CODEWITHSUNDEEP
mysqlsqllaravel
dlyatupyhvoprosov
dlyatupyhvoprosov

Reputation: 13

How to correctly use Query Builder Laravel in my situation?

I have a part of the SQL-code that needs to be converted correctly for Query Builder (Laravel).

select
p.*
from posts p, comments c
where c.post_id = p.post_id
group by p.post_id
order by avg(c.mark_first) desc

Can anyone halp me? :/

Upvotes: 1

Views: 32

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Using query builder you could write it as 

$posts = DB::table('posts as p')
            ->join('comments as c', 'p.post_id', '=', 'c.post_id')
            ->groupBy('p.post_id')
            ->orderByRaw('avg(c.mark_first) desc')
            ->select('p.*')
            ->get();

Its better if you could add all the columns in groupby which you want in select part because above query will become invalid if mysql's full_group_by mode is enabled which is enabled by default in 5.7+

Upvotes: 1

Related Questions