在“原则”中左连接ON条件AND其他条件语法 [英] Left join ON condition AND other condition syntax in Doctrine
问题描述
我现在的代码如下所示:
$ repository = $ this-> getDoctrine() - > getRepository('AaaBundle:Application');
$ queryBuilder = $ repository-> createQueryBuilder('a');
$ queryBuilder
- > addSelect('u')
- > addSelect('i')
- > orderBy('a.created_date','DESC' )
- > leftJoin('a.created_by','u')
- > leftJoin('a.installations','i')
// - > where 'i.page =:page')
// - > setParameter('page',$ found)
;
现在我可以使用它来获取所有的页面,无论它们是否安装。
但是,我只想加入它们,$ $ $ $ $ $ $找到页面(所以如果有一个应用程序的安装,但它在另一个页面上,安装不会加入)。如果我取消引用where子句,它将只显示具有该页面安装的应用程序。我想要所有的应用程序有或没有安装页面。
在SQL中,我可以通过将 AND
添加到加入
LEFT JOIN安装i ON a.id = i.app AND i.page =:page
这样我就可以获得一个在页面上安装的应用程序的安装信息,但是我在列上得到空值对于在其他页面上安装的应用程序,或者根本没有安装。
有没有办法在Doctrine中执行此操作,或者我最好只是为每个应用程序获取所有安装然后用php检查找到的页面?
你可以试试这个:
使用Doctrine\ORM\Query\Expr;
- > leftJoin('a.installations','i',Expr\Join :: WITH,'i.page =:page')
- > setParameter('页面',$ page)
请参阅文档中的leftJoin函数: http://docs.doctrine-project。 org / projects / doctrine-orm / en / latest / reference / query-builder.html#high-level-api-methods
I'm using Doctrine's querybuilder in Symfony2 to create a query to fetch entities.
My current code looks like this:
$repository = $this->getDoctrine()->getRepository('AaaBundle:Application');
$queryBuilder = $repository->createQueryBuilder('a');
$queryBuilder
->addSelect('u')
->addSelect('i')
->orderBy('a.created_date', 'DESC')
->leftJoin('a.created_by', 'u')
->leftJoin('a.installations', 'i')
//->where('i.page = :page')
//->setParameter('page', $found)
;
Now I can use this to get all the pages regardless of them having an installation or not.
But I only want to join them it the $found
page is available (so that if there is an installation for the app, but it's on another page, the installation wont be joined). If I unquote the where clause, it will show only apps that have an installation for the page. I want all apps with or without installations for the page.
In SQL I can get this by adding AND
to the join
LEFT JOIN installations i ON a.id = i.app AND i.page = :page
This way I get the installation info for an app that has an installation on the page, but I get null values on the columns for app's that have installations on other pages or not at all.
Is there a way to do this in Doctrine or am I better off just getting all the installations for each application and then checking against the found page with php?
You can try this :
use Doctrine\ORM\Query\Expr;
->leftJoin('a.installations', 'i', Expr\Join::WITH, 'i.page = :page')
->setParameter('page', $page)
See function leftJoin in doc: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods
这篇关于在“原则”中左连接ON条件AND其他条件语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!