Symfony combinate 2 queries

Question

I would like to get max date from other query insert to current query. Look at my codes pls.

Data:

inv_id   inv_date    inv_export   inv_code
1        2016-03-14  0            a2
2        2016-03-13  0            a1
3        2016-04-13  1            a1
4        2016-03-14  0            a1

Result:

for inv_export = 0 return a2 & a1
for inv_export = 1 return a1

mysql (working fine):

SELECT ..., i.inv_date, i.inv_export
FROM Sp.inventory AS i
...

WHERE i.inv_date IN (
    SELECT max(i.inv_date) from Sp.inventory WHERE i.inv_export = 1
);

I have tried convert it to symfony:

$qb = $this
  ->createQueryBuilder('i')
  ->select('..., i.invDate')
  ...

  ->where('i.invExport = :export AND i.invDate = MAX(i.invDate)')
  ->setParameter('export', $export);

You can take a look at this [Q/A](http://stackoverflow.com/questions/6637506/doing-a-where-in-subquery-in-doctrine-2), you will find another method. — Houssem ZITOUN, Apr 13 '16 at 15:20

score 4 · Accepted Answer · answered Apr 13 '16 at 14:53

You can use a subquery as follow:

$sub = $this->createQueryBuilder('i2');
$sub->select("max(i2.inv_date)");
$sub->where("i.invExport= i2.invExport");


$qb = $this
  ->createQueryBuilder('i')
  ->select('..., i.invDate')
  ...
   ->where('i.invDate = ( '.$sub->getDQL().' )');
  ->andWhere('i.invExport = :export')
  ->setParameter('export', $export);

hope this help

Symfony combinate 2 queries

1 Answers1