Doctrine 2 查询构建器和 leftJoin 子查询 [英] Doctrine 2 query builder and leftJoin subquery

问题描述

我有以下实体.

实体产品:

namespace AppModel;

use NetteObject;
use DoctrineORMMapping as ORM;
use DoctrineCommonCollectionsArrayCollection;

/**
 * @ORMEntity
 */
class Product extends Object
{
    /**
     * @ORMId
     * @ORMColumn(type="integer")
     * @ORMGeneratedValue
     * @var int
     */
    private $id;

    /**
     * @ORMManyToOne(targetEntity="Category")
     * @ORMJoinColumn(name="category_id", referencedColumnName="id", onDelete="SET NULL")
     * @var int
     */
    private $category;

    /**
     * @ORMOneToOne(targetEntity="Image", cascade={"persist", "remove"})
     * @var Image
     */
    private $image;

    /**
     * @ORMOneToOne(targetEntity="Image", cascade={"persist", "remove"})
     * @ORMJoinColumn(nullable=true, onDelete="SET NULL")
     * @var Image
     */
    private $backgroundImage;

    /**
     * @ORMManyToOne(targetEntity="Layer")
     * @ORMJoinColumn(name="methodOfPreparing_id", referencedColumnName="id", onDelete="SET NULL")
     * @var Layer|NULL
     */
    private $methodOfPreparing;
}

实体层:

/**
 * @ORMEntity
 */
class Layer extends Object
{    
    /**
     * @ORMId
     * @ORMColumn(type="integer")
     * @ORMGeneratedValue
     * @var int
     */
    private $id;

    /**
     * @ORMOneToMany(targetEntity="State", mappedBy="layer", indexBy="position", cascade={"persist", "remove"})
     * @var ArrayCollection
     */
    private $states;

    /**
     * @ORMColumn(type="string")
     * @var string
     */
    private $name;

    /**
     * @ORMColumn(type="integer")
     * @var int
     */
    private $position;
}

实体状态:

/**
 * @ORMEntity
 */
class State extends Object
{
    /**
     * @ORMId
     * @ORMColumn(type="integer")
     * @ORMGeneratedValue
     * @var int
     */
    private $id;

    /**
     * @ORMColumn(type="string")
     * @var string
     */
    private $name;

    /**
     * @ORMManyToOne(targetEntity="Layer", inversedBy="states")
     * @ORMJoinColumn(name="layer_id", referencedColumnName="id", onDelete="CASCADE")
     * @var Layer
     */
    private $layer;

    /**
     * @ORMColumn(type="integer")
     * @var int
     */
    private $position;

    /**
     * @ORMColumn(type="integer", columnDefinition="int(1) NOT NULL")
     * @var bool
     */
    private $active;
}

骨架查询:

SELECT Product
    FROM product
        LEFT JOIN 
            (SELECT Layer, State
                FROM layer
                INNER JOIN state ON layer.id = state.layer_id
                WHERE state.active = 1
            ) sub
        ON sub.id = product.methodOfPreparing_id

我需要使用查询构建器创建 DQL 查询.可以使用左连接子查询吗?

I need to create a DQL query using the query builder. It is possible left join subquery?

推荐答案

您可以使用 WITH 子句作为 JOIN 条件在 DQL 中执行 LEFT JOIN 子查询.

You can do LEFT JOIN subqueries in DQL using a WITH clause as the JOIN criteria.

例如，

SELECT st
FROM SomeThings st
LEFT JOIN st.other ot WITH ot.id = (
  SELECT ot2
  FROM OtherThings ot2
  WHERE ot2.id = 'some ID'
)
WHERE blah blah blah

...或...

SELECT st
FROM SomeThings st
LEFT JOIN st.others ot WITH ot.id IN (
  SELECT ot2
  FROM OtherThings ot2
  WHERE ot2.status = true
)
WHERE blah blah blah

...等等.

以下是我处理的代码库中的工作示例:

Here are working samples from a codebase I work on:

SELECT f, df, d
FROM EntitiesMain_frames f
LEFT JOIN f.dashboards df WITH df.dashboard IN (
  SELECT dd
  FROM EntitiesMain_dashboards dd
  WHERE dd.dashboard_addedby = :createdBy
    AND dd.status = true
)
LEFT JOIN df.dashboard d
INDEX BY f.id
WHERE f.createdBy = :createdBy
  AND f.frame_type IN (2, 6)
  AND f.status = true
ORDER BY f.createdDate

...和...

SELECT m, cm, s
FROM EntitiesMain_modules m
LEFT JOIN m.companyModules cm WITH cm.companyid = :companyId
LEFT JOIN m.subscriptions s WITH s.id = (
  SELECT ss
  FROM EntitiesSubscriptions ss
  WHERE ss.company = :companyId
    AND ss.module = m.id
    AND ss.createdDate = (
      SELECT MAX(sss.createdDate)
      FROM EntitiesSubscriptions sss
      WHERE sss.company = :companyId
        AND sss.module = m.id
  )
)
WHERE m.id <= 4
ORDER BY m.id

这篇关于Doctrine 2 查询构建器和 leftJoin 子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！