How to group only consecutive records from a database?

Question

MySQL

There is a revision table (based on venturecraft revisionable):

+----+-----------------+---------+---------------------+
| id | revisionable_id | user_id |     created_at      |
+----+-----------------+---------+---------------------+
|  6 |               1 |       1 | 2017-03-15 23:59:40 |
|  5 |               1 |       1 | 2017-03-15 23:56:40 |
|  4 |               3 |       8 | 2017-03-15 23:55:40 |
|  3 |               4 |       3 | 2017-03-15 23:55:40 |
|  2 |               1 |       1 | 2017-03-15 23:15:40 |
|  1 |               1 |       1 | 2017-03-15 22:59:40 |
+----+-----------------+---------+---------------------+

Is it possible, without dancing with a tambourine, to group the data in such a way that, while maintaining the possibility of pagination, display them as follows:

• user 1 made 2 changes to entity 1
  
• user 8 made 1 change to entity 3
  
• user 3 made 1 change in entity 4
  
• user 1 made 2 changes to entity 1
  

and at the same time, it is necessary to group and count only sequentially made edits for one revisionable_id and ignore those made earlier. That is, records with id 1,2 and 5,6 are not merged, because there are other records between them. That is, groupBy (revisionable_id), as I understand it, is not suitable)?..

Answer 1

UPD: not 100% satisfying, but working approach

//шаманим постраничную навигацию вручную
        $paginate = 10;
       $page = Input::get('page', 1);
       $offSet = ($page * $paginate) - $paginate;
       //забираем все записи и сортируем их
       $records = Revision::select(
          'revisionable_id',
          'revisionable_type',
          'user_id',
          DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d') as created"),
          DB::raw('count(*) as total')
        )
        ->groupBy('revisionable_id','revisionable_type','user_id','created')
        ->orderBy('created','desc')
        ->get();
       //выбираем записи для нужной страницы
       if($page==1){
         $sliced = collect($records)->slice(0,$paginate)->all();
       }else{
         $sliced = collect($records)->slice($offSet, $paginate)->all();
       }
       //отправляем все в view
       $revisions = new Paginator($sliced, count($records), $paginate, $page);
       //не забываем поправить путь ссылок в постраничной навигации
       $revisions = $revisions->setPath('/revisions');

Answer 2

SELECT
    user_id, revisionable_id, COUNT(*) 
FROM 
    table 
GROUP BY 
    CONCAT(user_id, "-", revisionable_id)

Answer 3

I can only advise a stored procedure, which will create the table you need and add an additional column, which will be incremented when the user_id changes. Then you can make a group by on a new column and sort by it. Those. you get something like
SELECT `t`.*, COUNT(*) AS `count` FROM my_func() AS `t` GROUP BY `newcol` ORDER BY `newcol`;
And my_func - should return the table by full consecutive search of data search.