I want to join two collections and sort the date field in the foreign table and then have group the fields to get only one record from recent date for each device as below
$collection = $this->db->dv_device;
$result_data = $collection->aggregate(
array('$lookup'=>array('from'=>'dv_device_readings', 'localField'=>'device_id', 'foreignField'=>'device_id','as'=>'result_data')),
array('$match'=>array('user_id'=>'5809c169622d10a419000029')),
array('$sort'=>array('result_data.date_time'=>-1)),
array('$group' => array(
"_id" => array("device_id" => '$result_data.device_id'),
"device_id" => array('$first' => '$result_data.device_id'),
"date_time" => array('$first' => '$result_data.date_time'),
"dc_voltage" => array('$first' => '$result_data.dc_voltage'),
"dc_current" => array('$first' => '$result_data.dc_current'),
"output_voltage" => array('$first' => '$result_data.output_voltage'),
"output_current" => array('$first' => '$result_data.output_current'),
))
);
By using the above code in php I got the below result, joining two collections is working fine but sorting and grouping is not working.
Array
(
[waitedMS] => 0
[result] => Array
(
[0] => Array
(
[_id] => Array
(
[device_id] => Array
(
[0] => dv1002
[1] => dv1002
)
)
[device_id] => Array
(
[0] => dv1002
[1] => dv1002
)
[date_time] => Array
(
[0] => MongoDate Object
(
[sec] => 1477476049
[usec] => 0
)
[1] => MongoDate Object
(
[sec] => 1477476313
[usec] => 0
)
)
[dc_voltage] => Array
(
[0] => dv1
[1] => dvv2
)
[dc_current] => Array
(
[0] => dc1
[1] => dc2
)
[output_voltage] => Array
(
[0] => output voltage1
[1] => output voltage2
)
[output_current] => Array
(
[0] => output current1
[1] => output current2
)
)
)
[ok] => 1
)
I need result as below
Array
(
[waitedMS] => 0
[result] => Array
(
[0] => Array
(
[device_id] => dv1002
[date_time] => MongoDate Object
(
[sec] => 1477476313
[usec] => 0
)
[dc_voltage] => dvv2
[dc_current] => dc2
[output_voltage] => output voltage2
[output_current] => output current2
)
)
[ok] => 1
)
Advertisement
Answer
You may need a $unwind, so you can perform grouping on looked up collection. Add this line after $match.
array('$unwind'=>'$result_data')