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 Go to next message](/theme/default/images/down.png) |
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 #2249 is a reply to message #2247] |
Mon, 10 August 2009 17:07 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
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 message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 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'?
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: Missing calculated field in relationship [message #2253 is a reply to message #2247] |
Tue, 11 August 2009 05:34 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
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'));
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 16:40:39 EDT 2024
Total time taken to generate the page: 0.01222 seconds
|