Symfony2 QueryBuilder join ON 和 WITH 的区别 [英] Symfony2 QueryBuilder join ON and WITH difference

问题描述

我是 Symfony2 的新手，我通过 QueryBuilder 和 Doctrine 2 成功构建了我的第一个连接.可能这是一个愚蠢的问题，但无论是在线还是在 Symfony2 的方法中，我都找不到任何东西来理解连接子句WITH"和ON"之间的区别.

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)')

效果很好，但如果我把 ON 而不是 WITH 我得到以下错误:

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

[语法错误] 第 0 行，第 200 列:错误:预期DoctrineORMQueryLexer::T_WITH, 'ON'

[Syntax Error] line 0, col 200: Error: Expected DoctrineORMQueryLexer::T_WITH, got 'ON'

为什么?我在对象中看到有 T_ON 和 T_WITH 之类的 join 子句，但它们的用法区别是什么?它们的用途是什么?

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?

推荐答案

@florian 给了你正确的答案，但让我试着用例子解释一下:

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

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

In sql, joins are done like this:

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

(或类似的东西)

现在在 Doctrine 中，您不需要使用 ON 子句，因为 Doctrine 从实体中的关系注释中知道这一点.所以上面的例子是:

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.

现在是 WITH 子句.如果您只想加入价格大于 50 的产品，您可以在 SQL 中执行此操作:

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

在教义中:

// 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() ;
}

因此，实际上，如果您使用 Doctrine，则永远不应该使用 ON.如果您需要类似的东西，您几乎可以肯定自己搞砸了其他东西.

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.

这篇关于Symfony2 QueryBuilder join ON 和 WITH 的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！