Laravel 5, Derived table in join clause?

Question

I have this query:

SELECT * FROM blog
LEFT JOIN (
    SELECT blog_id, AVG(value) as blog_rating FROM blog_ratings
    GROUP BY (blog_id)
) T ON T.blog_id = blog.id;

I do not know how to write this with Eloquent.

For Example:

Blog::select("*")->leftJoin( /* Here goes derived table */ )->get()

How do I accomplish this?

score 13 · Accepted Answer · answered May 17 '15 at 12:14

13

I'd personally just use the fluent query builder, try this out and see how it works out:

DB::table('blog')
  ->select('*')
  ->leftJoin(DB::raw('(SELECT blog_id, AVG(value) as blog_rating FROM blog_ratings
    GROUP BY (blog_id)
    ) as T'), function ($join) {
        $join->on ( 'T.blog_id', '=', 'blog.id' );
    })
  ->get();

You can always swap ->get() for ->toSql() to dump out the query and adjust if you see any mistakes.

answered May 17 '15 at 12:14

haakym

12,050
12
70
98

1

Since I cannot use DB::table as it will not give me model (I have relations that are needed for representing data) I came up with this `Blog::leftJoin(DB::raw('(SELECT blog_id, AVG(value) as blog_rating FROM blog_ratings GROUP BY (blog_id)) as T'), function ($join) {$join->on ( 'T.blog_id', '=', 'blog.id' );})->get();` – clzola May 27 '15 at 19:34
1

learning that you can pass the raw derived table sql as the first argument to a *join function, rather than the table name, was clutch – chiliNUT Mar 26 '18 at 19:34

Laravel 5, Derived table in join clause?

1 Answers1

Linked