Re: CONCAT in Join

On Saturday, 15 March 2014 06:46:37 UTC+1, Justin Atack wrote:

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);

That is a lot of code to have in your controller - put it in the model =)

 

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.

Find list uses Hash::combine - it's going to be using a value path that doesn't exist with the aliased field, and hence returning null. If you debug the end of that method you may figure out a way to modify your current usage to do that.

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.

virtual fields are defined with a public property - you can define them on the fly.

Alternatively use find all, and a foreach loop to format the list _in your model_.

AD

--
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.