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
{
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
* @var string
* @ORM\Column(name="firstname", type="string", length=255, nullable=true)
*/
private $firstname;
/**
* @var string
* @ORM\Column(name="lastname", type="string", length=255, nullable=true)
*/
private $lastname;
/**
* @ORM\OneToMany(targetEntity="AppBundle\Entity\CalendarEvent", mappedBy="user")
*/
private $events;
/**
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Factory", inversedBy="users")
* @ORM\JoinColumn(name="factory_id", referencedColumnName="id")
*/
private $factory;
}
CalendarEvent:
CalendarEvent:
class CalendarEvent
{
/**
* @var int
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @var \DateTime
* @ORM\Column(type="datetime", name="event_start")
*/
private $startDate;
/**
* @var \DateTime
* @ORM\Column(type="datetime", name="event_end")
*/
private $endDate;
/**
* @var bool
* @ORM\Column(name="isLoan", type="boolean")
*/
private $isLoan = TRUE;
/**
* @ORM\ManyToOne(targetEntity="Factory")
* @ORM\JoinColumn(name="factory_id", referencedColumnName="id", nullable = true)
*/
private $factory;
/**
* @ORM\ManyToOne(targetEntity="UserBundle\Entity\User", inversedBy="events")
* @ORM\JoinColumn(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...
我尝试了此操作，但是此变量$notLoanedToOther出现错误Expression of type 'Doctrine\ORM\QueryBuilder' not allowed in this context.:
I tried this but I have an error Expression of type 'Doctrine\ORM\QueryBuilder' 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
推荐答案
我想您可以将现有查询翻译为不在
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屋！
