CODEWITHSUNDEEP
mysqllaraveleloquent
Ruwan Liyanage
Ruwan Liyanage

Reputation: 375

Eloquent get top 5 records from each group

I'm trying to get top 5 records from each category using Eloquent query, similar to sample MySQL query as follows;

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY secid ORDER BY created_at DESC) AS n
    FROM trees
) AS x
WHERE n <= 5

This is what I tried

  $q= Tree::select(['*',DB::raw("ROW_NUMBER() OVER (PARTITION BY secid ORDER BY created_at DESC) AS n")])
         ->WhereRaw(['n','<=','5'])

->select();

I'm getting "Array to string conversion" error

I tried

->WhereRaw('n','<=','5')

and getting

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5 n order by `trees`.`created_at` desc' at line 1

Can anyone please point me what am I doing wrong here? Any help would be highly appreciated.

Upvotes: 0

Views: 152

Answers (1)

Vlad
Vlad

Reputation: 891

The first parameter of the whereRow method is an sql string and the second is an array of bindings. To avoid this error you need the following call:

->whereRaw('n <= ?', [5])

But still the query will not build correctly. I suggest you use the following construction:

$subQuery = DB::table('trees')
    ->selectRaw('*, ROW_NUMBER() OVER (PARTITION BY secid) AS n')
    ->orderByDesc('created_at');

$result = DB::fromSub($subQuery, 'x')
    ->where('n', '<=', 5)
    ->get();

Solution with Eloquent:

$subQuery = Tree::selectRaw('*, ROW_NUMBER() OVER (PARTITION BY secid) AS n')
    ->orderByDesc('created_at');

$result = Tree::fromSub($subQuery, 'trees')
    ->where('n', '<=', 5)
    ->get();

Upvotes: 1

Related Questions