您可以使用 Doctrine 2 DQL 加入子查询吗? [英] Can you join on a subquery with Doctrine 2 DQL?

问题描述

有没有办法在连接的 WITH 子句中访问连接实体的关系?我试图避免在子查询中使用 IN 子句.

Is there a way to access a joined entity's relation in the WITH clause of a join? Im trying to avoid using an IN clause with a subquery.

或者有没有办法加入子查询而不是使用IN?

Or is there a way to join on a subquery rather than using IN?

即确保连接对象的 t.final 值为 1.

i.e. Making sure the joined object's t.final value is 1.

尽量避免这个查询

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

重写尝试失败:无法访问 s.serviceType.final

SELECT o
FROM EntityOrder 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="RepositoryOrder")
 * @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 EntityInvoice
   */
  public function getInvoice()
  {
    return $this->invoice;
  }

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

  /**
   * @param int $status
   *
   * @uses EntityOrder::STATUS_REVIEW
   * @uses EntityOrder::STATUS_PENDING
   * @uses EntityOrder::STATUS_SCHEDULED
   * @uses EntityOrder::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;
  }
}

服务请求实体:

/**
 * @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 EntityInvoice
   */
  public function getInvoice()
  {
    return $this->invoice;
  }

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

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

  /**
   * @return EntityServiceType
   */
  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;
  }
}

推荐答案

您在加入 servicetype 之前查询它.试试:

You're querying servicetype before it is joined. Try:

SELECT o
FROM EntityOrder 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

这篇关于您可以使用 Doctrine 2 DQL 加入子查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！