Symfony2 QueryBuilder加入ON和WITH区别

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

我是Symfony2的新手，我通过QueryBuilder和Doctrine 2成功构建了我的第一个连接。可能这是一个愚蠢的问题但是在线和Symfony2的方法中我都无法找到任何理解join子句之间的区别的东西“用“和”开“。

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:

它工作正常，但如果我把它打开而不是WITH我得到以下错误：

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

[语法错误]第0行，第200行：错误：预期的Doctrine \ ORM \ Query \ Lexer :: T_WITH，得到'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?

为什么？我在对象中看到T_ON和T_WITH都有join子句，但是它们的使用区别是什么？他们的用途是什么样的？

2 个解决方案

#1

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

@florian给了你正确的答案，但让我试着在例子中解释它：

In sql, joins are done like this:

在sql中，连接是这样完成的：

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:

现在在Doctrine中，您不需要使用ON子句，因为doctrine知道您实体中的关系注释。所以上面的例子是：

// 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:

现在出现了WITH子句。如果您只想加入价格大于50的产品，您可以在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.

所以，实际上如果你使用的是Doctrine，你永远不应该使用ON。如果你需要这样的东西，你几乎可以肯定你搞砸了别的东西。

#2

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

理论上，ON允许您提供完整的连接标准，而WITH允许在默认值（IMHO）中添加其他标准。

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

但是，DQL允许的是避免给出JOIN标准：

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

你只需要说：$ qb-> leftJoin（'prod.pdata'，'pdata'）;

And doctrine2 will handle the join correctly.

doctrine2将正确处理连接。

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

这是一个相关的问题：我可以在DQL中使用“ON”关键字，还是需要使用Native Query？

#1