Comments (6)
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.
Related Issues (20)
- Non field names in CONCAT HOT 1
- criteria for NOT EQUAL HOT 1
- Support INSERT IGNORE HOT 2
- correcting Query.php for working with MySQL HOT 3
- Documentation link is broken HOT 3
- Using LIKE for Fields HOT 1
- Question: "WHERE IN" with Array HOT 1
- Computed Columns / Subselects HOT 1
- Is there a way to name the binding parameters? HOT 2
- How to first create Criteria and then add it to the query HOT 4
- How to make scalar subquery (query in select)? HOT 2
- JOIN added multiple times with same criteria HOT 3
- No grouping support when creating criteria from string expressions (hoa/ruler) HOT 2
- Interest in schema modification queries? HOT 2
- Increment column in update query HOT 2
- ORDER BY case HOT 2
- AND ?? HOT 2
- Select with tableA.* and tableB.field issue HOT 1
- Broken doumentation links
- How to select MAX? HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from latitude.