Advertisement
Guest User

CakePHP Join with Fields CONCAT

a guest
Mar 15th, 2014
87
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 2.51 KB | None | 0 0
  1. //AssignmentsStudentController.php
  2.  
  3.                 $assistantOptions = array(
  4.                         'joins' => array(
  5.                                 array(
  6.                                         'table' => 'schools_students',
  7.                                         'alias' => 'SchoolsStudent',
  8.                                         'type' => 'INNER',
  9.                                         'conditions' => array('Assistant.id = SchoolsStudent.student_id')
  10.                                         )
  11.                                 ),
  12.                         'fields' => array(
  13.                                 'Assistant.id',
  14.                                 'CONCAT(Assistant.given_name, " ", Assistant.family_name) AS Assistant__assistant_fullname'    
  15.                                 ),
  16.                         'conditions' => array(
  17.                                 'Assistant.active' => 1,
  18.                                 'Assistant.role_id' => array(4, 5, 6),
  19.                                 'SchoolsStudent.assistant' => 1,
  20.                                 'SchoolsStudent.school_id' => 1
  21.                                 ),
  22.                         'order' => array(
  23.                                 'Assistant__assistant_fullname' => 'ASC'
  24.                                 )
  25.                         );
  26.                 $assistants = $this->AssignmentsStudent->Assistant->find('list', $assistantOptions);
  27.  
  28. // Produces the following SQL (This works correctly in SequelPro)
  29. 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
  30.  
  31. // Array Output is missing Assistant fullname
  32. Array
  33. (
  34.     [100] =>
  35.     [41] =>
  36.     [39] =>
  37.     [44] =>
  38.     [67] =>
  39.     [90] =>
  40.     [76] =>
  41.     [49] =>
  42.     [30] =>
  43.     [98] =>
  44.     [102] =>
  45.     [71] =>
  46.     [112] =>
  47.     [57] =>
  48.     [116] =>
  49.     [50] =>
  50.     [17] =>
  51.     [107] =>
  52.     [52] =>
  53.     [92] =>
  54.     [31] =>
  55.     [24] =>
  56.     [18] =>
  57.     [42] =>
  58.     [2] =>
  59.     [56] =>
  60.     [38] =>
  61.     [109] =>
  62.     [83] =>
  63.     [87] =>
  64.     [75] =>
  65.     [68] =>
  66.     [7] =>
  67.     [105] =>
  68.     [85] =>
  69.     [5] =>
  70.     [59] =>
  71.     [69] =>
  72.     [22] =>
  73.     [114] =>
  74. )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement