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
");

Answer 1

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.