Doctrine query builder - nested queries

Question

How can I convert this query into symfony 2 doctrine query builder?

SELECT
    artist_id,
    DATE,
    balance,
    TYPE
FROM TRANSACTION AS
    t1
WHERE
    DATE =(
    SELECT
        MAX(DATE)
    FROM TRANSACTION
WHERE
    artist_id = t1.artist_id AND
STATUS
    IN(
        'partial',
        'pending',
        'deducted',
        'accepted'
    ) AND TYPE NOT LIKE 'payment'
)
GROUP BY
    artist_id
ORDER BY
    artist_id

I tried the following:

$qb = $this->getEntityManager()->createQueryBuilder()
->select('t.balance','a.id','t.date')
           ->from('TestMainBundle:Transaction','t')
           ->Join('t.artist','a')
           ->where("t.status in ('partial','pending','deducted','accepted')")
           ->andWhere("t.type NOT LIKE 'payment'")
           ->groupBy('a.id')
           ->orderBy('a.id');
        return $qb->getQuery()->getResult();

But I am stuck with including the condition of max (date) as well. Any help on this is very much appreciated.

I think you need something similar to this [question](https://stackoverflow.com/questions/11924164/subquery-in-doctrine2-notin-function) — squareCircle, Aug 03 '17 at 20:29

score 2 · Accepted Answer · answered Aug 04 '17 at 15:09

Your Doctrine query will look something like this,

$qb1 = $this->getDoctrine()->getManager()->createQueryBuilder();        
$select = $qb1->select('MAX(date) AS max_data')
        ->from('YourBundle:Transaction', 's')
        ->where('s.artist_id = :ti_artist_id')
        ->andWhere('s.status IN (:statuses)')
        ->andWhere('s.type NOT LIKE :type')
        ->getQuery();

$qb2 = $this->getDoctrine()->getManager()->createQueryBuilder();
$result = $qb2->select('t.artist_id', 't.date', 't.balance', 't.type')
        ->from('YourBundle:Transaction', 't');

$result->where($qb2->expr()->eq('t.date', $select->getDQL()))
        ->setParameter('ti_artist_id', 't.id')
        ->setParameter('statuses', array('partial','pending','deducted','accepted'))
        ->setParameter('type', 'payment') //possibly '%payment%'
        ->orderBy('t.artist_id')
        ->getQuery()
        ->getResult();

Cheers!!!

Doctrine query builder - nested queries

1 Answers1