Search code examples
phpmysqldoctrine-ormdqlsymfony-2.3

doctrine dql not returning expected result where MySQL does

The last hour or so i am trying to get the MySQL query below working in DQL, however instead of returning the expected result it output nothing.

This is the MySQL query that output the correct result.

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', \Doctrine\ORM\Query\Expr\Join::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();

As far as we could find out there goes something wrong with vmh.source. When leaving out "andWhere('vmh.source != :haan')" and "andWhere('vmh.source != :kip')" the DQL query does output results. However these filters needs to be active as both needs to be filter out.

I hope you guys have an idea how to fix the result set.

Solution

  • IMHO your original SQL is different than the one generated by Doctrine. I would try something like this:

    $qb = $this->getDoctrine()->getEntityManager()->createQueryBuilder();
$qb->select('vp.id')
   ->from('VaartlandIntranetBundle:VehiclePhoto', 'vp')
   ->innerJoin('vp.vehicle', 'v')
   ->leftJoin(
       'v.vehicleMaintenanceHistory',
       'vmh',
       \Doctrine\ORM\Query\Expr\Join::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();

    If the haan and kip dynamic params will be always set to kip and haan I would skip the assignment and write them directly in the expression.

    More info: