Cakephp make a request in the finder

release

#1

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


#2

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