Search code examples
doctrinesubquerydql

Doctrine : subquery in DQL

I'm trying to calculate a value using DQL on one single table. Say this table is named "TABLE". It has 5 colums :

  • id
  • people_count
  • animal_count
  • region_id
  • type_id

The result I'm looking for is the sum of people (when grouped by region), divided by the sum of animals (when grouped by type);

The SQL would be something like that :

SELECT SUM(people_count) / 
(
    SELECT SUM(animal_count) 
    FROM TABLE t2 
    GROUPED BY type_id
) 
FROM TABLE t1 
GROUPED BY region_id

How would you do that in Doctrine using DQL?

Solution

  • I resolved my problem by creating another query, executing it and including the result in the first one.

    This is probably not the best way to do if you are dealing with a simple example, but it was the only solution for me regarding to my code architecture.