最后一个小时左右,我试图使下面的mySQL查询在dql中工作,但是而不是返回预期的结果,而是什么都没输出。
这是输出正确结果的MySQL查询。
mysql:
SELECT vp.id FROM vehicle_photo AS vp
INNER JOIN vehicle AS v ON vp.vehicle_id = v.id AND vp.manualMaintenanceCheckedOn IS NULL AND vp.type_id = 1
LEFT JOIN vehicle_maintenance_history AS vmh ON vmh.vehicle_id = v.id AND vmh.source != 'kip' AND vmh.source != 'haan'
WHERE vmh.vehicle_id IS NULL
LIMIT 1;
dql:
$qb = $this->getDoctrine()->getEntityManager()->createQueryBuilder();
$qb->select('vp.id')
->from('VaartlandIntranetBundle:VehiclePhoto', 'vp')
->innerJoin('vp.vehicle', 'v')
->leftJoin('v.vehicleMaintenanceHistory', 'vmh', DoctrineORMQueryExprJoin::WITH, 'vmh.vehicle = v.id')
->where('vp.type = 1')
->andWhere('vp.manualMaintenanceCheckedOn is null')
->andWhere('vmh.source != :kip')
->andWhere('vmh.source != :haan')
->andWhere('vmh.vehicle IS NULL')
->setParameter('kip','kip')
->setParameter('haan','haan')
->setMaxResults(1);
$ers = $qb->getQuery();
$res = $qb->getQuery()->getResult();
据我们发现,VMH.Source出了问题。当忽略" and where('vmh.source!=:haan'("one_answers" and Where('vmh.source!=:kip'("时,DQL查询确实会输出结果。但是,这些过滤器需要过滤出来,因此需要活跃。
我希望你们有一个想法如何修复结果集。
IMHO您的原始SQL与学说生成的SQL不同。我会尝试这样的事情:
$qb = $this->getDoctrine()->getEntityManager()->createQueryBuilder();
$qb->select('vp.id')
->from('VaartlandIntranetBundle:VehiclePhoto', 'vp')
->innerJoin('vp.vehicle', 'v')
->leftJoin(
'v.vehicleMaintenanceHistory',
'vmh',
DoctrineORMQueryExprJoin::WITH,
$qb->expr()->andx(
$qb->expr()->eq('vmh.vehicle', 'v.id'),
$qb->expr()->neq('vmh.source', ':kip'),
$qb->expr()->neq('vmh.source', ':haan')
)
)
->where('vp.type = 1')
->andWhere('vp.manualMaintenanceCheckedOn is null')
->andWhere('vmh.vehicle IS NULL')
->setParameter('kip', 'kip')
->setParameter('haan', 'haan')
->setMaxResults(1);
$ers = $qb->getQuery();
$res = $qb->getQuery()->getResult();
如果haan
和kip
动态参数将始终设置为kip
,而haan
我会跳过分配并将它们直接写在表达式中。
更多信息:
- 学说expr类