gpt4 book ai didi

mysql - 您可以使用 Doctrine 2 DQL 加入子查询吗?

转载 作者:行者123 更新时间:2023-11-29 03:41:12 25 4
gpt4 key购买 nike

有没有办法在连接的 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;
}
}

服务请求实体:

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

最佳答案

您在加入之前查询服务类型。尝试:

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

关于mysql - 您可以使用 Doctrine 2 DQL 加入子查询吗?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13596541/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com