Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » Missing calculated field in relationship
Missing calculated field in relationship [message #2247] Mon, 10 August 2009 12:19 Go to next message
gpatti is currently offline  gpatti
Messages: 283
Registered: August 2008
Senior Member
I seem to have introduced a bug into my application with some recent code changes, but can't figure out where.

I have a relationship using a calculated field that returns several values. However, since I have made some changes, only the first of these values is being returned (I can see that in the generated SQL). I haven't actually made any changes to this particular relationship itself.

Are there any circumstances you could advise me of where the generated SQL might not return all the calculated fields? Alternatively, can you point me to the place to focus my debugging?

Thanks,

Graham
Re: Missing calculated field in relationship [message #2248 is a reply to message #2247] Mon, 10 August 2009 13:18 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
Can you tell me what your '<table>.dict.inc' file contains for that relationship?

Can you give me a sample of the SQL query which is produced? To log the generated SQL please refer to FAQ51 item #2.


Re: Missing calculated field in relationship [message #2249 is a reply to message #2247] Mon, 10 August 2009 17:07 Go to previous messageGo to next message
gpatti is currently offline  gpatti
Messages: 283
Registered: August 2008
Senior Member
The table.dict.inc relationship is:
    $this->parent_relations[]               = array('parent' => 'candidate',
                                                    'parent_field' => 'concat(first_name, \' \', last_name) as person_name, age_group_id, dob_date, first_name, last_name, gender, candidate_pin, student_ind, addr_line_1, addr_line_2, addr_line_3, town, county, postcode, email_addr, home_telephone, mobile_telephone',
                                                    'fields' => array('candidate_id' => 'candidate_id',
                                                                      'rdcaccount_id' => 'rdcaccount_id'));


The generated SQL is:
SELECT SQL_CALC_FOUND_ROWS exam.*, branch.branch_id, branch.branch_desc, branch.branch_abbrev, branch.syllabus_type_id, branch.student_branch_abbrev, branch.crf_type_id, branch.session_type_id as branch_session_type_id, 
  concat(candidate.first_name,' ',candidate.last_name) AS person_name,
  d1.dance_code AS dance1_code, d2.dance_code AS dance2_code, d3.dance_code AS dance3_code, d4.dance_code AS dance4_code,
  grade.grade_code, grade.grade_desc, school.school_name, school.principal_name, school.principal_mem_num, school.school_addr_line_1, school.school_addr_line_2, school.school_addr_line_3, school.school_town, school.school_county, school.school_postcode, school.school_email_addr, school.school_web_addr, school.school_telephone,
  session.session_desc, session.session_date, session.examiner, session.session_type_id, amount  
FROM exam  
LEFT JOIN branch ON (branch.branch_id=exam.branch_id)  
LEFT JOIN candidate ON (candidate.candidate_id=exam.candidate_id AND candidate.rdcaccount_id=exam.rdcaccount_id)  
LEFT JOIN dance AS d1 ON (d1.dance_id=exam.dance1_id)  
LEFT JOIN dance AS d2 ON (d2.dance_id=exam.dance2_id)  
LEFT JOIN dance AS d3 ON (d3.dance_id=exam.dance3_id)  
LEFT JOIN dance AS d4 ON (d4.dance_id=exam.dance4_id)  
LEFT JOIN grade ON (grade.grade_id=exam.grade_id)  
LEFT JOIN school ON (school.school_id=exam.rdcaccount_id)  
LEFT JOIN session ON (session.rdcaccount_id=exam.rdcaccount_id AND session.session_id=exam.session_id)  
LEFT JOIN cost_group_charge ON (grade.cost_group_id=cost_group_charge.cost_group_id AND session.session_date >= cost_group_charge.start_date AND session.session_date <= cost_group_charge.end_date AND exam.rdcaccount_id = cost_group_charge.rdcaccount_id AND exam.banner_req = cost_group_charge.banner_req AND cost_group_charge.age_group_id IN  (SELECT candidate.age_group_id from candidate where exam.candidate_id = candidate.candidate_id UNION SELECT age_group.age_group_id from age_group where age_group_id = 'X'))   
WHERE exam.rdcaccount_id='7' AND exam.session_id='1'    
ORDER BY exam.exam_seq_nbr asc  
LIMIT 10 OFFSET 0 =>Count=8


You can see that the items following person_name are not included in the SQL.

Graham
Re: Missing calculated field in relationship [message #2250 is a reply to message #2249] Mon, 10 August 2009 18:10 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
I have run a test using the XAMPLE subsystem which is contained in the Radicore download. I changed the relationship between X_PERSON and X_PERSON_ADDR so that it reads as follows:
$this->parent_relations[]     = array('parent' => 'x_person',
                                      'parent_field' => 'CONCAT(first_name, \' \', last_name) AS person_name, value1, value2',
                                      'fields' => array('person_id' => 'person_id'));

When I ran the task it gebnerate the following SQL query:
SELECT SQL_CALC_FOUND_ROWS x_person_addr.*, CONCAT(x_person.first_name,' ',x_person.last_name) AS person_name, x_person.value1, x_person.value2 
FROM x_person_addr 
LEFT JOIN x_person ON (x_person.person_id=x_person_addr.person_id) 
WHERE x_person_addr.person_id='PA'   
ORDER BY x_person_addr.person_id, x_person_addr.address_no  
LIMIT 10 OFFSET 0

As you can see it successfully retrieves multiple fields from the parent table. Can you step through with your debugger to see how the problem relationship is prcessed in the _sqlForeignJoin() method within 'std.table.class.inc'?


Re: Missing calculated field in relationship [message #2251 is a reply to message #2247] Mon, 10 August 2009 18:58 Go to previous messageGo to next message
gpatti is currently offline  gpatti
Messages: 283
Registered: August 2008
Senior Member
Thanks for the pointer where to look... I've found it, and it is a bug after all.

As part of my code changes I had added an alias to the relationship immediately prior to the one that is failing. I haven't located the exact point in your code (it's a bit complicated for this time of night!), but while processing the second relationship, the fact that an alias already exists is causing it to skip the full processing of the current relationship.

Hope this is enough for you to take over. Let me know if you need further information.

Graham
Re: Missing calculated field in relationship [message #2252 is a reply to message #2251] Mon, 10 August 2009 19:29 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
Can you give me the full contents of your '<table>.dic.inc' file so I can see all the relationships that are being processed?

Re: Missing calculated field in relationship [message #2253 is a reply to message #2247] Tue, 11 August 2009 05:34 Go to previous messageGo to next message
gpatti is currently offline  gpatti
Messages: 283
Registered: August 2008
Senior Member
I did a quick test by removing the alias on the previous relationship and it did indeed fix the issue with the problem relationship. The full set of relationships is below. The alias causing the problem is 'session_type_id as branch_session_type_id' in the relationship with the branch table, immediately before the relationship with the candidate table.

    $this->child_relations                  = array();
    
    // parent relationship details 
    $this->parent_relations[]               = array('parent' => 'branch',
                                                    'parent_field' => 'branch_id, branch_desc, branch_abbrev, syllabus_type_id, student_branch_abbrev, crf_type_id, session_type_id as branch_session_type_id',
                                                    'fields' => array('branch_id' => 'branch_id'));
    
    $this->parent_relations[]               = array('parent' => 'candidate',
                                                    'parent_field' => 'concat(first_name, \' \', last_name) as person_name, age_group_id, dob_date, first_name, last_name, gender, candidate_pin, student_ind, addr_line_1, addr_line_2, addr_line_3, town, county, postcode, email_addr, home_telephone, mobile_telephone',
                                                    'fields' => array('candidate_id' => 'candidate_id',
                                                                      'rdcaccount_id' => 'rdcaccount_id'));
    
    $this->parent_relations[]               = array('parent' => 'dance',
                                                    'alias' => 'd1',
                                                    'parent_field' => 'dance_code as dance1_code',
                                                    'fields' => array('dance1_id' => 'dance_id'));
    
    $this->parent_relations[]               = array('parent' => 'dance',
                                                    'alias' => 'd2',
                                                    'parent_field' => 'dance_code as dance2_code',
                                                    'fields' => array('dance2_id' => 'dance_id'));
    
    $this->parent_relations[]               = array('parent' => 'dance',
                                                    'alias' => 'd3',
                                                    'parent_field' => 'dance_code as dance3_code',
                                                    'fields' => array('dance3_id' => 'dance_id'));
    
    $this->parent_relations[]               = array('parent' => 'dance',
                                                    'alias' => 'd4',
                                                    'parent_field' => 'dance_code as dance4_code',
                                                    'fields' => array('dance4_id' => 'dance_id'));
    
    $this->parent_relations[]               = array('parent' => 'grade',
                                                    'parent_field' => 'grade_code, grade_desc',
                                                    'fields' => array('grade_id' => 'grade_id'));
    
    $this->parent_relations[]               = array('parent' => 'school',
                                                    'parent_field' => 'school_name, principal_name, principal_mem_num, school_addr_line_1, school_addr_line_2, school_addr_line_3, school_town, school_county, school_postcode, school_email_addr, school_web_addr, school_telephone',
                                                    'fields' => array('rdcaccount_id' => 'rdcaccount_id',
                                                                      'rdcaccount_id' => 'school_id'));
    
    $this->parent_relations[]               = array('parent' => 'session',
                                                    'parent_field' => 'session_desc, session_date, examiner, session_type_id',
                                                    'fields' => array('rdcaccount_id' => 'rdcaccount_id',
                                                                      'session_id' => 'session_id'));
Re: Missing calculated field in relationship [message #2254 is a reply to message #2253] Tue, 11 August 2009 06:23 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
So the generated SQL query is correct when you remove that alias. Just as an experiment what happens if you put that alias back?

Re: Missing calculated field in relationship [message #2255 is a reply to message #2247] Tue, 11 August 2009 06:32 Go to previous messageGo to next message
gpatti is currently offline  gpatti
Messages: 283
Registered: August 2008
Senior Member
Tried that already and it fails again.
Re: Missing calculated field in relationship [message #2256 is a reply to message #2255] Tue, 11 August 2009 09:10 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
Problem fixed (I hope! see attached file).

It was indeed the previous alias name which caused the problem - it caused my code to treat $parent_fields as having only a single field name instead of several. I now convert it into an array so that I can iterate over each entry without losing anything.


Re: Missing calculated field in relationship [message #2257 is a reply to message #2247] Tue, 11 August 2009 10:30 Go to previous message
gpatti is currently offline  gpatti
Messages: 283
Registered: August 2008
Senior Member
Thanks. That does indeed appear to solve the problem.
I'll let you know if I uncover any side effects as I continue my current testing.
Previous Topic: Use of $this->skip_validation
Next Topic: how can i change a textfield row into dropdown control?
Goto Forum:
  


Current Time: Thu Mar 28 19:21:39 EDT 2024

Total time taken to generate the page: 0.01397 seconds