Implementation of greateast N per group with Laravel's query builder

Question

The problem is to implement the nested query on the second INNER JOIN :

SELECT a, b, c
FROM t1
INNER JOIN t2 ON t2.b = t1.b
INNER JOIN (
    SELECT a, max(c) AS d FROM t3 GROUP BY a
) AS t3 ON ( t3.a = t1.a AND t3.d = t1.c )

I've try a lot of test with closure on join() function, but it seems to be only for nested where.

I've find on SO that my SQL command can be write with an other form easier to write with query builder, but less trivial. So I wonder if the above SQL query can still be write with Laravel4's Query Builder.

Answer 1

That was a fun one.

$products = DB::table('t1')
    ->select('a', 'b', 'c')
    ->join('t2', 't2.b', '=', 't1.b')
    ->join(DB::raw('(SELECT a, max(c) AS d FROM "articles" GROUP BY a) AS t3'), function($join)
    {
        $join->on('t3.a', '=', 't1.a');
        $join->on('t3.d', '=', 't1.c');
    })
    ->toSql();

echo $products;

It's outputting correctly for me but with the inclusion of some parenthesis around what you are joining t3 on. I don't see it making any difference for you though. You may have to change "articles" to just articles because I don't think that will work correctly.