Symfony2 QueryBuilder join ON and WITH difference

Question

@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.

Leave a Comment Cancel reply