0

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.

Community
  • 1
  • 1
Fractaliste
  • 5,777
  • 11
  • 42
  • 86

1 Answers1

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.

user1669496
  • 32,176
  • 9
  • 73
  • 65
  • Oh I forgot to replace "articles" by "t3" in my example when I've try to generalize my query. As far as I understand it's not possible to avoid DB::raw for nested queries? – Fractaliste Nov 27 '13 at 22:15
  • Does the two $join->on() into the callbackfunction work like an `and`? – Fractaliste Nov 27 '13 at 22:16
  • Yeah, that's exactly what they are doing. It won't be possible without a raw statement because if you try doing `->join('(select a, max(c) as d FROM articles GROUP BY a) as t3', 't3.a', '=', 't1.a')`, it will put ticks before and after like it's one long table name. – user1669496 Dec 02 '13 at 15:50
  • 1
    Notice I'm using `toSql()` rather than `get()`. This will convert it to SQL first so you can echo it to make sure it's doing what you need. – user1669496 Dec 02 '13 at 15:55
  • Thanks for the tips, I didn't know them ! – Fractaliste Dec 02 '13 at 16:12