Doctrine Query Builder 嵌套 orX 和 andX 条件与连接 [英] Doctrine Query Builder nested orX and andX conditions with join
问题描述
我有两个实体 User 和 CalendarEvent.我的用户隶属于一个工厂，而 calendarEvent 可用于了解用户是否被借"到他所属的另一个工厂...
I have two entities User and CalendarEvent. My users are attached to a factory, and calendarEvent can be used to know if a user is "loaned" to a different factory of the one he belongs to...
我的两个实体是这样的:
My two entities are like this :
用户:
class User extends BaseUser
{
/**
* @ORMId
* @ORMColumn(type="integer")
* @ORMGeneratedValue(strategy="AUTO")
*/
protected $id;
/**
* @var string
* @ORMColumn(name="firstname", type="string", length=255, nullable=true)
*/
private $firstname;
/**
* @var string
* @ORMColumn(name="lastname", type="string", length=255, nullable=true)
*/
private $lastname;
/**
* @ORMOneToMany(targetEntity="AppBundleEntityCalendarEvent", mappedBy="user")
*/
private $events;
/**
* @ORMManyToOne(targetEntity="AppBundleEntityFactory", inversedBy="users")
* @ORMJoinColumn(name="factory_id", referencedColumnName="id")
*/
private $factory;
}
日历事件:
class CalendarEvent
{
/**
* @var int
*
* @ORMColumn(name="id", type="integer")
* @ORMId
* @ORMGeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @var DateTime
* @ORMColumn(type="datetime", name="event_start")
*/
private $startDate;
/**
* @var DateTime
* @ORMColumn(type="datetime", name="event_end")
*/
private $endDate;
/**
* @var bool
* @ORMColumn(name="isLoan", type="boolean")
*/
private $isLoan = TRUE;
/**
* @ORMManyToOne(targetEntity="Factory")
* @ORMJoinColumn(name="factory_id", referencedColumnName="id", nullable = true)
*/
private $factory;
/**
* @ORMManyToOne(targetEntity="UserBundleEntityUser", inversedBy="events")
* @ORMJoinColumn(name="user_id", referencedColumnName="id")
*/
private $user;
}
我想要做的是在 UserRepository 中创建一个存储库函数，当我在入口参数中使用工厂触发请求时，它让我知道实际上是工厂的人......
What i'm trying to do is create a repository function in the UserRepository which gets me the people which actualy are is the factory when I trigger the request with the factory in entry argument...
为了实现这一点，我尝试指定一个条件.这种情况应该是这样的:
In order to achieve that, I try to specify a condition. That condition should looks like that :
select * from user where (
(user.factory == $idfactory AND not loaned_to_other_factory) OR
(user.factory != $idfactory AND loaned_to_my_factory)
)
要知道用户是否被借给我的工厂，必须验证以下条件:
To know if a user has been loaned to my factory, the following condition must be verified :
Does a record exist in CalendarEvent where startDate < NOW and endDate > NOW and loaned == true and factory == $factory
我正在尝试使用我的存储库来使用和/或条件，但我一直坚持使用该查询很长时间，我什至不习惯使用存储库，因为 findBy 方法在大多数情况下都可以正常工作...
I'm trying to user and / or conditions with my repository but I've been stuck with that query for so long, i'm not even used to work with repository as the findBy method works fine in most of the cases...
我试过这个，但我有一个错误 Expression of type 'DoctrineORMQueryBuilder' not allowed in this context. 对于这个变量 $notLoanedToOther :
I tried this but I have an error Expression of type 'DoctrineORMQueryBuilder' not allowed in this context. for this variable $notLoanedToOther :
public function findByFactory($idFactory)
{
$qb = $this->_em->createQueryBuilder();
$qb->select('u')
->from($this->_entityName, 'u')
->leftJoin('u.events', 'e');
$sub = $this->createQueryBuilder("e");
$sub->select("e");
$sub->from("CalendarEvent","e");
$sub->where('e.user = u.id');
$sub->andWhere('e.startDate < :now');
$sub->andWhere('e.endDate > :now');
$notLoanedToOther = $qb->where($qb->expr()->not($qb->expr()->exists($sub->getDQL())));
$sub = $this->createQueryBuilder("e");
$sub->select("e");
$sub->from("CalendarEvent","e");
$sub->where('e.user = u.id');
$sub->where('e.factory = :idf');
$sub->andWhere('e.startDate < :now');
$sub->andWhere('e.endDate > :now');
$loanedToMyFactory = $qb->where($qb->expr()->exists($sub->getDQL()));
$condition1 = $qb->expr()->andX(
$qb->expr()->eq('u.factory', ':idf'),
$notLoanedToOther
);
$condition2 = $qb->expr()->andX(
$qb->expr()->neq('u.factory', ':idf'),
$loanedToMyFactory
);
$qb ->where($qb->expr()->orX($condition1, $condition2));
$qb ->setParameter('idf', $idFactory);
return $qb->getQuery()->getResult();
}
我真的希望我说得通，有人可以给我一些提示来实现这一点.提前致谢
I realy hope I made sense and someone can give me tips to achieve that. Thanks in advance
推荐答案
我猜你可以将你的 exists 查询翻译成 not in
I guess you could translate your exists query to not in
$notLoanedToOther = $this->createQueryBuilder("e")
->select("e.user")
->from("CalendarEvent","e")
->andWhere('e.startDate < :now')
->andWhere('e.endDate > :now')
->getDQL();
$loanedToMyFactory = $this->createQueryBuilder("e")
->select("e.user")
->from("CalendarEvent","e")
->where('e.factory = :idf')
->andWhere('e.startDate < :now')
->andWhere('e.endDate > :now')
->getDQL();
$qb = $this->_em->createQueryBuilder();
$qb->select('u')
->from($this->_entityName, 'u')
->where(
$qb->expr()->not(
$qb->expr()->in(
'u.id',
$notLoanedToOther
)
)
)
->andWhere(
$qb->expr()->in(
'u.id',
$loanedToMyFactory
)
)
->where(
$qb->expr()->orX(
$qb->expr()->andX(
$qb->expr()->eq('u.factory', ':idf'),
$qb->expr()->not(
$qb->expr()->in(
'u.id',
$notLoanedToOther
)
)
),
$qb->expr()->andX(
$qb->expr()->neq('u.factory', ':idf'),
$qb->expr()->in(
'u.id',
$loanedToMyFactory
)
)
)
)
->setParameter('idf', $idFactory)
->setParameter('now', $someDate)
;
这篇关于Doctrine Query Builder 嵌套 orX 和 andX 条件与连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
