CONCAT in Join

Hi All,

I think I have found a bug in CakePHP... but I could be wrong as I'm a CakePHP newbie... The code can be seen here if you prefer it colour coded: http://pastebin.com/eXSpbAmw

Heres some code from my AssignmentsStudentSController.php. It should pull a list of names from the students DB who are registered as an "Assistant" in a joining table two models away.

$assistantOptions = array(

'joins' => array(

array(

'table' => 'schools_students',

'alias' => 'SchoolsStudent',

'type' => 'INNER',

'conditions' => array('Assistant.id = SchoolsStudent.student_id')

)

),

'fields' => array(

'Assistant.id',

'CONCAT(Assistant.given_name, " ", Assistant.family_name) AS Assistant__assistant_fullname'

),

'conditions' => array(

'Assistant.active' => 1,

'Assistant.role_id' => array(4, 5, 6),

'SchoolsStudent.assistant' => 1,

'SchoolsStudent.school_id' => 1

),

'order' => array(

'Assistant__assistant_fullname' => 'ASC'

)

);

$assistants = $this->AssignmentsStudent->Assistant->find('list', $assistantOptions);

The above code actually produces SQL that works correctly when I paste it into SequelPro and run it... this is the bit that's very strange to me... why does it work perfectly in SequelPro but not in CakePHP? Even stranger is that CakePHP actually produced the correct SQL query.

SELECT `Assistant`.`id`, CONCAT(Assistant.given_name, " ", Assistant.family_name) AS Assistant__assistant_fullname FROM `jwtalks_tms`.`students` AS `Assistant` INNER JOIN `jwtalks_tms`.`schools_students` AS `SchoolsStudent` ON (`Assistant`.`id` = `SchoolsStudent`.`student_id`) WHERE `Assistant`.`active` = '1' AND `Assistant`.`role_id` IN (4, 5, 6) AND `SchoolsStudent`.`assistant` = '1' AND `SchoolsStudent`.`school_id` = 1 ORDER BY `Assistant__assistant_fullname` ASC

However when I look at my AssistantsStudent:add view I get the following Array

Array  (      [100] =>       [41] =>       [39] =>       [44] =>       [67] =>       [90] =>       [76] =>       [49] =>       [30] =>       [98] =>       [102] =>       [71] =>       [112] =>       [57] =>       [116] =>       [50] =>       [17] =>       [107] =>       [52] =>       [92] =>       [31] =>       [24] =>       [18] =>       [42] =>       [2] =>       [56] =>       [38] =>       [109] =>       [83] =>       [87] =>       [75] =>       [68] =>       [7] =>       [105] =>       [85] =>       [5] =>       [59] =>       [69] =>       [22] =>       [114] =>   )

This produces a list with no names because as you can see they are missing from the array however the ID's are present so the list is still filled but with blanks.

Now before you say just use a "VirtualField"... I know about these and how to use them. However what I need to do is produce lists with data from various models, so I need to join and CONCAT this information. If I do this using a "VirtualField" I get SQL errors because the field doesn't exist... that's because it's in another model.

I hope I have explained the issue well enough.

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.