我遇到了一个关于dql的奇怪问题。
我有两个带有外键的表连接器:
== Table incident ==
id, cycle_nr, ...
1, 1
2, 3
== Table incident_active ==
id, incident_id, user_id, ...
1, 1, 1
...
我需要显示一些周期的活动事件,如果我执行mySQL查询,一切都很好
SELECT * FROM `incident_active` LEFT JOIN incident ON incident.id = `incident_active`.incident_id WHERE cycle_nr <= 2 and user_id = 1
DQL中的相同查询也适用,但仅适用于cycle_nr!=2
SELECT incidentActive, incident
FROM AccountingBundle:IncidentActive incidentActive
JOIN incidentActive.incident incident
WHERE incidentActive.company_id = 1 AND incident.cycle_nr <= 2
对于cycle_nr<=2我得到一个空结果。我想是因为这个周期的事件太多了,但我问<2而不是==2。有什么想法吗?
DQL中的默认联接是INNER join。这些查询并不等价。
SELECT * FROM `incident_active`
LEFT JOIN incident ON incident.id = `incident_active`.incident_id
WHERE cycle_nr <= 2 and user_id = 1
这个SQL查询是这样用DQL编写的(假设用户是一个实体)
SELECT incidentActive, incident
FROM AccountingBundle:IncidentActive incidentActive
LEFT JOIN incidentActive.incident incident
WHERE incident.cycle_nr <= :cycleNr
AND incidentActive.user = :userId
此外,当您只为联接设置了条件,并且不希望它影响"main"表中的结果时,您应该只将它们添加到该联接条件中,如下所示。
SELECT incidentActive, incident
FROM AccountingBundle:IncidentActive incidentActive
LEFT JOIN incidentActive.incident incident WITH incident.cycle_nr <= :cycleNr
WHERE incidentActive.user = :userId
如果上面的查询在$dql
中,那么使用绑定参数应该是这样的。
$result = $em->createQuery($dql)
->setParameter('cycleNr', 2)
->setParameter('userId', 1)
->getResult();