Self JOIN SQL converting to Doctrine

Question

This is my table:

Supposing I am passing $cameras containing cameras ids 30 and 31 my result should be 70, it is sum of 30 (for id 43, it is max Id for camera_id 31) and 40 (id 45, it is max id for camera_id 32)

I can achieve this result with this SQL statement:

SELECT SUM(t.number) 
FROM `count` t 
INNER JOIN 
  (SELECT MAX(id) id, camera_id 
   FROM`count` 
   WHERE camera_id 
   IN $cameras 
   GROUP BY camera_id)t1 
ON t1.id = t.id

But I would like to convert this SQL statement to Doctrine's query. This is what I try:

$this->createQueryBuilder("c")
            ->select('SUM(c.number)')
            ->innerJoin(Count::class, 'c2', 'WITH' , 'c.id = c2.id')
            ->select('MAX(c2.id)')
            ->where('c2.camera IN (:value)')
            ->setParameter('value', $cameras)
            ->groupBy('c2.camera')
            ->getQuery()

But this query returns to me Max ids and I want to these Max ids were returned to the

->andWhere('SUM(c.number)')

line where they should be summed.

How can I adjust my query code?

Thank you very much.

I think that happens because you have a second `select` which overrides the first one. Try removing it completely. But can you also share a sample of your db table? I don't think you actually need a self reference query to get what you need. — Martin M., Aug 28 '20 at 09:34
I added table sample. If I remove second select, I will not get max Ids. — Vladimír, Aug 28 '20 at 09:40
Am I right in guessing that your end result should be `[55, 70, 94, 40]` from the rows you provided? — Martin M., Aug 28 '20 at 09:48
No, my result should be 70, it is sum of 30 (for id 43, it is max Id for camera_id 31) and 40 (id 45, it is max id for camera_id 32) — Vladimír, Aug 28 '20 at 09:55

Martin M. · Accepted Answer · 2020-08-28T13:30:35.707

1

Simplest solution I found is using a second builder:

$qb = $this->createQueryBuilder('c')
    ->select('MAX(c.id) AS max_id')
    ->where('c.camera IN (:cameras)')
    ->groupBy('c.camera');

$result = $this->createQueryBuilder('c2')
    ->select('SUM(c2.number)')
    ->where($qb->expr()->in('c2.id', $qb->getDQL()))
    ->setParameter('cameras', $cameras)
    ->getQuery()->getResult();

Got the idea from here.

edited Aug 28 '20 at 13:30

answered Aug 28 '20 at 10:43

Martin M.

431
5
14

I get error: `Too few parameters: the query defines 1 parameters but you only bound 0` – Vladimír Aug 28 '20 at 10:56
I had to make just little modifications of your code and it works great. `$qb = $this->createQueryBuilder('c') ->select('MAX(c.id) AS max_id') ->where('c.camera IN (:cameras)') ->groupBy('c.camera'); $result = $this->createQueryBuilder('c2') ->select('SUM(c2.number)') ->where($qb->expr()->in('c2.id', $qb->getDQL())) ->setParameter('cameras', $cameras) ->getQuery()->getResult();` Thank You very much. – Vladimír Aug 28 '20 at 11:37

Self JOIN SQL converting to Doctrine

1 Answers1