CODEWITHSUNDEEP
doctrinesubquerydql
frinux
frinux

Reputation: 2092

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 :

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?

Upvotes: 1

Views: 1120

Answers (2)

Baptiste Donaux
Baptiste Donaux

Reputation: 1310

I have a solution but I think there is probably a best solution to resolve your problem.

In any case, make two queries and import results of first query in the second can be a solution. Unfortunately, it's a low-usage for our database. More, sometimes we must execute only one SQL to sort on column result per example.

<?php

namespace AppBundle\Repository;

use AppBundle\Entity\MyClass;
use Doctrine\ORM\EntityRepository;

class MyRepository extends EntityRepository
{
    public function findWithSubQuery()
    {
        return $this->createQueryBuilder('a')
                    ->addSelect(sprintf(
                        "(SELECT COUNT(b.id) FROM %s AS b WHERE b.a = a.id GROUP BY a.id) AS otherColumn",
                        MyClass::class
                    ))
                    ;
    }
}

I use this solution. Maybe the subquery could be write with DQL ojbect rather that DQL string.

Upvotes: 0

frinux
frinux

Reputation: 2092

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.

Upvotes: 1

Related Questions