1
votes

I'm trying to mimic this sql query:

$sql = "SELECT *, log.id as logid, date_format(shipdate,'%m/%d/%Y') as shipdate
        FROM  log LEFT JOIN sub ON sub.id = sub_id
        LEFT JOIN main ON id = main_id WHERE 
        shipdate >= '$datestart' AND shipdate <= '$dateend' ";

When I put SELECT * in the createQuery function of Doctrine, an error says it doesn't recognize that statement so I tried to grab all the entities by selecting individual ones:

$query = $this->getEntityManager()
        ->createQuery('
            SELECT
              m.dano, m.partno, m.batchno,
              s.rackno, s.heatcode, s.diecode,
              l.shipdate, l.qtyshipped, l.blno, l.id as logid
            FROM
             Bundle:Sub s
            JOIN
              s.main m
            JOIN
              s.log l
            WHERE
              l.shipdate >=  :fromdate and l.shipdate <= :todate
        ')->setParameter('fromdate', $fromdate)
        ->setParameter('todate', $todate);

However when I do it this way, it doesn't return all the data that the original queries does. It skips some rows. Is there another way to be selecting all the entity?

1
I don't know symfony2/doctrin2, but it looks liek you're doing LEFT JOINs in the first query and INNER JOINs in the second- that can definitely give different result sets.Dave Jemison
@DaveJemison Annnndd you are right! What a noob move.Sam Young

1 Answers

0
votes

You can mimic the following SQL using DQL. I believe that's the best way to do rather than using create query. Select all in DQL is 'select(alias)'. See my

SELECT *, log.id as logid, date_format(shipdate,'%m/%d/%Y') as shipdate
    FROM  log LEFT JOIN sub ON sub.id = sub_id
    LEFT JOIN main ON id = main_id WHERE 
    shipdate >= '$datestart' AND shipdate <= '$dateend' 

DQL

$dql = $qb2->select('m.dano, m.partno, m.batchno,
          s.rackno, s.heatcode, s.diecode,
          l.shipdate, l.qtyshipped, l.blno, l.id')
      ->from('YourBundle:Log','l')
      ->leftJoin('l.sub', 's', Expr\Join::ON, 's.id = l.sub_id')
      ->leftJoin('l.main', 'm', Expr\Join::ON, 'm.id = l.main_id')
      ->where('l.shipdate >= :shipdateStart')
      ->setParameter('shipdateStart', $datestart)
      ->where('l.shipdate <= :shipdateEnd')
      ->setParameter('shipdateEnd', $dateend) 

By the way your selecting table and joining tables are slightly different in the given sql example compared to your query in create query function. I mimiced what's in your raw SQL which is listed above. Hope this helps you to understand converting SQL in to DQL.

Regards,