CODEWITHSUNDEEP
doctrinesymfony
bluppfisk
bluppfisk

Reputation: 2652

Doctrine select, having sum

I have a MySQL query that works fine if I run it in phpMyAdmin. Now I need to transpose it to Doctrine, so it will give me the days on which the minutes of repairs exceed 300.

SELECT date FROM `job`
INNER JOIN `jobs_repairs` jr on job.id = jr.job_id
INNER JOIN `repair` r on jr.repair_id = r.id
GROUP BY date
HAVING SUM(r.minutes) < 300

how do I do this? Doctrine usually just returns objects, but this isn't an object as I'm looking for dates on which job objects have repairs that in total exceed 300 minutes.

Upvotes: 0

Views: 929

Answers (1)

Alex L
Alex L

Reputation: 651

Have you setup a repository for your Job entity?

The following is untested but should hopefully get you started. I'm using the QueryBuilder syntax but should be able to convert it into plain DQL pretty easily.

$this->getQueryBuilder('j')
    ->innerJoin('j.repair', 'r')
    ->groupBy('j.date')
    ->having('SUM(r.minutes) < 300')
    ->getQuery()
    ->getResult();

What this is doing is getting the Job objects that match your query. If you have a getDate() method on your Job entity, you could get it that way or could have a look at the different result types. If you just want an array instead, you could use getArrayResult() instead of getResult().

Updated. Thanks qooplmao for spotting I missed the SUM

Upvotes: 4

Related Questions