Doctrine 中的左连接 ON 条件和其他条件语法

问题描述

我在 Symfony2 中使用 Doctrine 的查询构建器来创建查询来获取实体.

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

现在我可以使用它来获取所有页面，无论它们是否安装.但我只想加入他们 $found 页面可用(这样如果应用程序有安装，但它在另一个页面上，安装不会被加入).如果我取消引用 where 子句，它将仅显示已安装该页面的应用程序.我想要所有已安装或未安装该页面的应用.

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.

在 SQL 中，我可以通过在连接中添加 AND 来获得这个

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.

有没有办法在 Doctrine 中做到这一点，还是我最好先获取每个应用程序的所有安装，然后使用 php 检查找到的页面?

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?

推荐答案

你可以试试这个:

use DoctrineORMQueryExpr;

->leftJoin('a.installations', 'i', ExprJoin::WITH, 'i.page = :page')
->setParameter('page', $page)

参见文档中的 leftJoin 函数:http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods

See function leftJoin in doc: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods

这篇关于Doctrine 中的左连接 ON 条件和其他条件语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！