Doctrine join WITH vs WHERE

Question

I'm joining multiple tables and I found two solution. I can't ready figure out what is the diference. Can anyone tell me?

Solution 1:

$query = $entityManager->createQuery("
    SELECT m FROM Model m
        JOIN m.battery b
        JOIN m.camera c
        JOIN m.connectivity co
        JOIN m.hardware h
        JOIN m.screen s
        JOIN m.sensor se                                                        
            WHERE b = m.battery
            AND c = m.camera
            AND co = m.connectivity
            AND h = m.hardware
            AND s = m.screen
            AND se = m.sensor"
);

Solution 2:

$query = $entityManager->createQuery("
    SELECT m FROM Model m
        JOIN m.battery b
        WITH m.battery = b
        JOIN m.camera c
        WITH m.camera = c
        JOIN m.connectivity co
        WITH m.connectivity = co
        JOIN m.hardware h
        WITH m.hardware = h
        JOIN m.screen s
        WITH m.screen = s
        JOIN m.sensor se
        WITH m.sensor = se
");

Wilt · Accepted Answer · 2016-01-13T10:10:54.187

You are writing DQL and you should not confuse it with SQL. In a DQL query join you don't need to add id fields explicitly to the join Doctrine takes care of it for you.

"SELECT m FROM Model m JOIN m.battery b" is sufficient.

to fetch-join add b to your select clause:

"SELECT m, b FROM Model m JOIN m.battery b"

If you want to join only batteries with a certain property, that is when you need to use the WITH clause in your DQL. For example join only batteries who's battery status is empty:

"SELECT m, b FROM Model m JOIN m.battery b WITH b.status = 'empty'"

Check the documentation on DQL joins for more information.

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

From what i have read i can say:

When you use Where instead of On in join, there is one difference, On will apply before selection and Where will apply after the selection process by join.
WITH is similar to On clause which lets you specify another condition in addition to On. So I think when you use With, It will work like ON but Where will eliminate the result after selection by Join.
It may helps: Stack Overflow:

Selected Answer of this question also used two of them together with explanation.

edited May 23 '17 at 12:15

Community

1
1

answered Jan 12 '16 at 18:08

Mohamad Eghlima

970
10
23

@RobinDijkhof In inner join, yes but in outer join does not matter. – Mohamad Eghlima Jan 12 '16 at 18:25
@RobinDijkhof I think I was wrong. query optimizer will pick the best way for the join and they can use interchangeably. read this http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause – Mohamad Eghlima Jan 12 '16 at 18:31
@RobinDijkhof The WHERE clause filters both the left and the right side of the JOIN, while the ON clause will always filter the right side only. - If you always want to fetch the left side rows and only JOIN if some condition matches then you should the ON clause. - If you want to filter both sides after the JOIN is made then you should use the WHERE clause. – Mohamad Eghlima Jan 12 '16 at 18:33

Doctrine join WITH vs WHERE

2 Answers2