GROUP BY clause ignores rows and data
As we know, the expression GROUP BY
returns data, ignoring duplicate data by specifying a specific one GROUP BY 'user_id'
, but I want to make it GROUP BY
ignore the table row, but I want to concatenate the data of that row in an array, which means I want all the data, but in the filtered row, I want something like
id | name | user_id
-------------------
1 abc 20
2 trt 19
3 sdf 20
4 khg 22
5 fdf 20
6 lnm 22
id | name | user_id
-------------------
1 abc,sdf,fdf 20
2 trt 19
3 khg,lnm 22
+3
source to share
2 answers
Use for this GROUP_CONCAT
. Try this query -
SELECT id, GROUP_CONCAT(name) name, user_id FROM students GROUP BY user_id
The GROUP_CONCAT function concatenates lines from a group into one line with different parameters.
Update
To implement it in CakePhp -
$driverlocation_data = $this->DriverLocation->find(
'all',
array(
'conditions'=>array('DriverLocation.dispensary_id'=>$dispensary_id),
'fields' => array('id', 'GROUP_CONCAT(name) name', 'user_id')
'group'=>array('DriverLocation.driver_id')
)
);
+1
source to share