Get model with N child items grouped by field-CodePudding

Table looks like this:

  id  ...  item_id  likes
  1          1       200
             1       300
             1       400
             1       4
  ...        2       54
             2       31
             3       22

How to get 3 or less items for each item_id with most likes?
So for my example output should looks like this:

[
  item_id: 1,
  likes: 200,
],
[
  item_id: 1,
  likes: 300,
],
[
  item_id: 1,
  likes: 400,
],
[
  item_id: 2,
  likes: 54,
],
[
  item_id: 2,
  likes: 31,
],
[
  item_id: 3,
  likes: 22,
],

CodePudding user response：

Since MySQL 8.0 you can use window function row_number

with n as (
    select 
        id, item_id, likes, row_number() over (partition by item_id) rn
    from tbl
) select 
    item_id, likes 
from n 
where rn < 4;

Test Mysql window functions

<?php

$query = "with n as (
    select 
        id, item_id, likes, row_number() over (partition by item_id) rn
    from tbl
) select item_id, likes from n where rn < 4;";

// Select using Laravel
$rows = $db::select($db::raw($query));

var_export($rows);

Laravel DB query