Symfony2 QueryBuilder join ON and WITH difference

Question

I'm new with Symfony2 and I built successfully my first join through QueryBuilder and Doctrine 2. Probably this is a stupid question but both on-line and in the Symfony2's methods I was unable to find anything for understanding the difference between the join clauses "WITH" and "ON".

For example this is my join code:

->leftJoin('EcommerceProductBundle:ProductData', 'pdata', 'WITH', 'prod.id = IDENTITY(pdata.product)')

It works good but if I put ON instead of WITH I get the following error:

[Syntax Error] line 0, col 200: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'

Why? I've seen among the objects that there are both the T_ON and T_WITH like join clauses, but which is their usage difference? What is their uses like?

Not at all a stupid question. This is major problem with the Doctrine documentation. There is no reference, only a Reference Guide which does not cover this and many other unclarities. — agoldev, Mar 27 '18 at 16:56

score 69 · Accepted Answer · answered Jul 02 '13 at 14:44

@florian gave you the correct answer but let me try to explain it on example:

In sql, joins are done like this:

SELECT * FROM category
    LEFT JOIN product ON product.category_id = category.id

(or something like this)

Now in Doctrine, you don't need to use ON clause because doctrine knows that from relations annotations in your entities. So above example would be:

// CategoryRepository.php
public function getCategoriesAndJoinProducts() 
{
    return $this->createQueryBuilder("o")
        ->leftJoin("o.products", "p")->addSelect("p") 
        ->getQuery()->getResult() ;
}

Both would fetch all categories and join products associated with them.

Now comes the WITH clause. If you want to join only products with price bigger than 50, you would do this in SQL:

SELECT * FROM category
    LEFT JOIN product ON product.category_id = category.id AND product.price>50

In Doctrine:

// CategoryRepository.php
public function getCategoriesAndJoinProductsWithPriceBiggerThan($price) 
{
    return $this->createQueryBuilder("o")
        ->leftJoin("o.products", "p", "WITH", "p.price>:price")
            ->setParameter("price", price)->addSelect("p") 
        ->getQuery()->getResult() ;
}

So, in reality you should never, ever use ON if you are using Doctrine. If you have a need for something like that, you can be almost sure that you screwed something else.

and what about result? do it return associated entity\model? for example i cannot get proper one http://stackoverflow.com/questions/20134014/symfony2-doctrine2-left-join-dql-and-its-result — user1954544, Nov 21 '13 at 23:33
A bit late to the party, but where would the the join query method be placed appropriately ?? In the example above you created the query method in `CategoryRepository.php` and why not on `ProductRepository.php`?? — Sanjok Gurung, Apr 12 '18 at 09:44

score 8 · Answer 2 · edited May 23 '17 at 10:31

8

In theory, ON permits you to give the full join criterias, while WITH permits to add additional criterias to the default ones (IMHO).

But, what DQL permits is to avoid giving the JOIN criterias:

You just have to say: $qb->leftJoin('prod.pdata', 'pdata');

And doctrine2 will handle the join correctly.

Here is a related question about that: Can I use "ON" keyword in DQL or do I need to use Native Query?

edited May 23 '17 at 10:31

Community

1
1

answered Jul 02 '13 at 11:51

Florian Klein

8,692
1
32
42

Thanks a lot for the comment. I've already seen that post and my issue is that it doesn't satisfy my question because there, there is written just the solution but not the reason for the "WITH" instead of "OR" and their differences. – Roberto Rizzi Jul 02 '13 at 14:27

Symfony2 QueryBuilder join ON and WITH difference

2 Answers2

Linked

Related