Help write and execute this query in finder cakephp 3.5.10
After, I need to group the result by user_id.
SELECT user_id, GRP, MIN(time) AS Start, MAX(time) AS End
FROM (
SELECT user_id, time,
@grp:=@grp+if(time>@prev+interval 30 minute,1,0) AS GRP,
@prev:=time
FROM user_log, (SELECT @prev:=NULL, @grp:=1) X
ORDER BY time) A
GROUP BY GRP
Below is the table structure, for the convenience of the from Adminer.
Thank you
Solved
$date = (!empty($this->request->query['date'])) ? $this->request->query['date'] : Date::now()->i18nFormat('yyyy-MM-dd');
$connection = ConnectionManager::get('default');
$statistics = $connection
->newQuery()
->select('users.name, user_id, grp, MIN(datetime) as start, MAX(datetime) as end, TIMEDIFF(MAX(datetime), MIN(datetime)) as sum')
->from('(select user_id, datetime, @grp:=@grp+if(datetime>@prev+interval 15 minute,1,0) as grp,@prev:=datetime from user_log, (select @prev:=NULL, @grp:=1) X order by datetime) A INNER JOIN users ON users.id = user_id')
->where(['datetime LIKE' => $date."%"])
->group(['grp'])
->execute()
->fetchAll('assoc');
$collection = (new Collection($statistics))->groupBy('user_id');