Nested Joins about latitude HOT 6 CLOSED

Absolutely! Every call to join() or leftJoin() creates a new JOIN part:

use Latitude\QueryBuilder\Conditions as c;
use Latitude\QueryBuilder\Expression as e;
use Latitude\QueryBuilder\SelectQuery;

$query = SelectQuery::make('t1.field1', 't2.field2', 't3.field')
    ->from('table1 t1')
    ->leftJoin('table2 t2', c::make('t2.id = t1.table2_id'))
    ->leftJoin('table3 t3', c::make('t3.id = t2.table3_id'))
   ->where(c::make('t1.id = ?', 1));

echo $query->sql();

from latitude.

Let me know if that doesn't solve your problem!

from latitude.

Thanks for the quick response!
This is exactly what I was doing, but it has a very big performance difference than the actually nested query, so this:

SELECT t1.field1, t2.field2, t3.field
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t2.id = t1.table2_id
LEFT JOIN table3 AS t3
ON t3.id = t2.table3_id
WHERE t1.id = ?

Is much slower than this:

SELECT t1.field1, t2.field2, t3.field
FROM table1 AS t1
LEFT JOIN table2 AS t2
    LEFT JOIN table3 AS t3
    ON t3.id = t2.table3_id
ON t2.id = t1.table2_id
WHERE t1.id = ?

from latitude.

AFAIK, there is no difference in Postgres or MySQL. I assume you are using something else?

from latitude.

I'm using MySQL, my query is a little different than that, however. This is what I am doing:

SELECT p.id, pt.id, GROUP_CONCAT(CONCAT(t.id, ':', t.name)) as testers
FROM Project AS p
LEFT JOIN ProjectTester AS pt
ON pt.project_id = p.id
LEFT JOIN Tester AS t
ON pt.tester_id = t.id
WHERE p.deleted = 0
GROUP BY p.id
LIMIT 3

Which takes about 4 seconds when I run it, when this one:

SELECT p.id, pt.id, GROUP_CONCAT(CONCAT(t.id, ':', t.name)) as testers
FROM Project AS p
LEFT JOIN ProjectTester AS pt
	LEFT JOIN Tester AS t
	ON pt.tester_id = t.id
ON pt.project_id = p.id
WHERE p.deleted = 0
GROUP BY p.id
LIMIT 3

Takes about 200ms.
Maybe I'm missing something but it looks like it has a different result.
Thanks!

from latitude.

After doing some testing I believe you're right, there's no performance difference between the two. The difference is that they return in a different order, that's why the time was different.
Thanks for the help!

from latitude.