你可以加入与学说2 DQL子查询？(Can you join on a subquery with

有中访问加入了实体的关系的方式WITH的加入条款？ 我试着去避免使用的IN子句中使用子查询。

编辑：或者是有没有办法加入一个子查询，而不是使用IN ？

即确保了加盟对象的t.final值是1。

试图避免这种查询

SELECT o
FROM Entity\Order o
WHERE o.status = :orderStatus
AND o.id NOT IN (
  SELECT o2.id
  FROM Entity\ServiceRequest s
  JOIN s.order o2
  JOIN s.serviceType t
  WHERE s.status = :serviceStatus
  AND t.final = 1
)

如果不重写尝试：无法访问s.serviceType.final

SELECT o
FROM Entity\Order o
LEFT JOIN o.serviceRequests s
    WITH s.status = :serviceStatus
    AND s.serviceType.final = 1
LEFT JOIN s.serviceType t
WHERE o.status = :orderStatus
AND COUNT(s) = 0

为了实体：

<?php
namespace Entity;

/**
 * @Entity(repositoryClass="Repository\Order")
 * @Table(name="orders")
 */
class Order
{
  const STATUS_REVIEW    = 0;
  const STATUS_PENDING   = 1;
  const STATUS_SCHEDULED = 2;
  const STATUS_COMPLETE  = 3;

  /**
   * @Id
   * @Column(type="integer")
   * @GeneratedValue
   *
   * @var int
   */
  protected $id;

  /**
   * @ManyToOne(targetEntity="Invoice")
   *
   * @var Invoice
   */
  protected $invoice;

  /**
   * @Column(type="integer")
   *
   * @var int
   */
  protected $status;

  /**
   * @Column(type="smallint", name="is_canceled")
   *
   * @var int
   */
  protected $isCanceled;

  /**
   * @OneToMany(targetEntity="ServiceRequest", mappedBy="order")
   *
   * @var ServiceRequest[]
   */
  protected $serviceRequests;

  /**
   * @return int
   */
  public function getId()
  {
    return $this->id;
  }

  /**
   * @return \Entity\Invoice
   */
  public function getInvoice()
  {
    return $this->invoice;
  }

  /**
   * @return int
   *
   * @uses \Entity\Order::STATUS_REVIEW
   * @uses \Entity\Order::STATUS_PENDING
   * @uses \Entity\Order::STATUS_SCHEDULED
   * @uses \Entity\Order::STATUS_COMPLETE
   */
  public function getStatus()
  {
    return $this->status;
  }

  /**
   * @param int $status
   *
   * @uses \Entity\Order::STATUS_REVIEW
   * @uses \Entity\Order::STATUS_PENDING
   * @uses \Entity\Order::STATUS_SCHEDULED
   * @uses \Entity\Order::STATUS_COMPLETE
   */
  public function setStatus($status)
  {
    $this->status = $status;
  }

  /**
   * @return int
   */
  public function getIsCanceled()
  {
    return $this->isCanceled;
  }

  public function cancel()
  {
    $this->isCanceled = 1;
  }

  /**
   * @return ServiceRequest[]
   */
  public function getServices()
  {
    return $this->services;
  }
}

ServiceRequest实体：

 /** * @Entity * @Table(name="order_service_requests") */ class ServiceRequest { const STATUS_REVIEW = 0; const STATUS_PENDING = 1; const STATUS_SCHEDULED = 2; const STATUS_COMPLETE = 3; /** * @Id * @Column(type="integer") * @GeneratedValue * * @var int */ protected $id; /** * @ManyToOne(targetEntity="Invoice") * * @var Invoice */ protected $invoice; /** * @ManyToOne(targetEntity="ServiceType") * @JoinColumn(name="service_types_id") * * @var ServiceType */ protected $serviceType; /** * @ManyToOne(targetEntity="Order") * @JoinColumn(name="orders_id") * * @var Order */ protected $order; /** * @Column(type="integer") * * @var int */ protected $status; /** * @Column(type="smallint", name="is_canceled") * * @var int */ protected $isCanceled; /** * @return int */ public function getId() { return $this->id; } /** * @return \Entity\Invoice */ public function getInvoice() { return $this->invoice; } /** * @return int * * @uses \Entity\ServiceRequest::STATUS_REVIEW * @uses \Entity\ServiceRequest::STATUS_PENDING * @uses \Entity\ServiceRequest::STATUS_SCHEDULED * @uses \Entity\ServiceRequest::STATUS_COMPLETE */ public function getStatus() { return $this->status; } /** * @param int $status * * @uses \Entity\ServiceRequest::STATUS_REVIEW * @uses \Entity\ServiceRequest::STATUS_PENDING * @uses \Entity\ServiceRequest::STATUS_SCHEDULED * @uses \Entity\ServiceRequest::STATUS_COMPLETE */ public function setStatus($status) { $this->status = $status; } /** * @return \Entity\ServiceType */ public function getServiceType() { return $this->serviceType; } /** * @return int */ public function isCanceled() { return $this->isCanceled; } public function getOrder() { return $this->order; } } 

服务类型实体：

<?php
namespace Entity;

/**
 * @Entity
 * @Table(name="service_types")
 */
class ServiceType
{
  /**
   * @Id
   * @Column(type="integer")
   * @GeneratedValue
   *
   * @var int
   */
  protected $id;

  /**
   * @Column(type="smallint")
   *
   * @var int
   */
  protected $final;

  /**
   * @return int
   */
  public function getId()
  {
    return $this->id;
  }

  /**
   * @return int
   */
  public function getFinal()
  {
    return $this->final;
  }
}

Answer 1:

它加入之前，您要查询的服务类型。 尝试：

SELECT o
FROM Entity\Order o
LEFT JOIN o.serviceRequests s
  WITH s.status = :serviceStatus
LEFT JOIN s.serviceType t
WHERE o.status = :orderStatus
AND COUNT(s) = 0
AND t.final = 1

文章来源: Can you join on a subquery with Doctrine 2 DQL?