Subquery with eloquent model in laravel 5

Question

I have the following raw query:

select * 
from (select * from settings order by priority desc) x
group by name

I would like to use a laravel 5 query builder instead but cant get the subquery to work. Is there a way to do this without using raw query's?

The table:

id - name - priority
1    1      1
2    1      2
3    2      1

In the above case It will return row 2 and 3 like I want to

I tried this query:

$settings = \App\Setting::where('user_id', Auth::id())->orWhere('tool_id', $id)->groupBy('name')->orderBy('priority', 'desc')->get();

But this will return row 1 and 3.

that makes no sense, just use the subselect. there's no reason to group by name as you're not aggregating any other fields — Rich Benner, Jun 21 '16 at 14:34
try Query Builder instead of Eloquent like in this answer http://stackoverflow.com/a/31864088/3018183 — Ravisha Hesh, Jun 21 '16 at 15:22

score 1 · Answer 1 · edited Oct 06 '20 at 06:28

1

Can you use CTE expressions? RANK() would work:

WITH CTE AS
(
  SELECT *, RANK() OVER(PARTITION BY name ORDER BY priority desc) AS RowRank 
  FROM settings
)
SELECT id, name, priority
FROM CTE c
WHERE c.RowRank = 1

edited Oct 06 '20 at 06:28

Regolith

2,944
9
33
50

answered Jun 21 '16 at 14:56

Sergio

503
3
11

The query already works. I just want to convert it into a query builder but no luck on that – Merijndk Jun 21 '16 at 14:57

Subquery with eloquent model in laravel 5

1 Answers1