Orm: Subqueries in arithmetic expressions

[iluuu1994]

Bug Report

| Q | A
|------------ | ------
| BC Break | no
| Version | 2.6.3

Summary

I have a query that looks something like this:

SELECT
    user, 
    (
        (
            SELECT COUNT(discussion.id)
            FROM App\Entity\Discussion discussion
            WHERE IDENTITY(discussion.author) = user.id
        )
        + (
            SELECT COUNT(comment.id)
            FROM App\Entity\Comment comment
            WHERE IDENTITY(comment.author) = user.id
        )
    ) AS activityCount
FROM App\Entity\User user

Basically, activityCount should be the sum of the number of comments and discussions the user has authored.

Current behavior

Currently, this results in a parser error.

[Syntax Error] ... Error: Expected Literal, got 'SELECT'

https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html#arithmetic-expressions

Assuming the documentation is accurate, ArithmeticPrimary will not parse subselects. Thus this is an invalid query.

Is this expected behavior?

[SenseException]

As far as I know this is an expected behaviour. This DQL query isn't something where you need an ORM. Cases like this should be handled with basic SQL.

[iluuu1994]

This DQL query isn't something where you need an ORM. Cases like this should be handled with basic SQL.

🤔

Everything can be handled with SQL. That's not why we use Doctrine. As mentioned in another thread.

If your whole application is built on Doctrine filters/query builders/gedmo this (using native queries) can be very difficult.

Is there a technical reason why this isn't allowed? If not I'll try to make it work and create a PR.

[Ocramius]

Is there a technical reason why this isn't allowed?

Not really, mostly complexity, plus the fact that it wasn't built so far. I'd say this is a good chance to include it in 3.x with the new parser, but the inner query must be guaranteed to be an aggregation query with a single scalar result in order to compile (no GROUP BY)

[Ocramius]

Also, I agree with @SenseException that this is perfectly OK if built with SQL: the ORM isn't really needed for most read-intensive operations.

[iluuu1994]

Thanks for the response guys. Despite my fear of being annoying I'd like to touch on that again:

this is perfectly OK if built with SQL: the ORM isn't really needed for most read-intensive operations.

I genuinely don't understand this statement. An ORM isn't ever "needed". We use Doctrine because it's convenient and it helps us write better software. Some reasons are:

1. Database independence
2. Composable queries (Query Builders)
3. Automatic object mapping
4. Filters
5. Gedmo

The second you write a native queries all of these go out the window. I can speak from experience that it's extremely easy to forget or improperly handle one of these things.

Let me give you a practicle example. This is how we usually structure our repositories:

<?php

namespace App\Repository;

class UserRepository
{
    public function find(array $options): array
    {
        return $this->createQueryBuilder($options)->getResult();
    }

    public function findOne(array $options): ?User
    {
        return $this->createQueryBuilder($options)->getOneOrNullResult();
    }

    private function createQueryBuilder(array $options): QueryBuilder
    {
        $options = (new OptionsResolver())
            ->setDefaults([
                'option' => null,
            ])
            ->resolve($options);

        $qb = $this->em->createQueryBuilder()
            ->select('user')
            ->from(User::class, 'user');

        if (null !== $option = $options['option']) {
            $qb
                ->andWhere('...')
                ->addSelect('...');
        }

        return $qb;
    }
}

Instead of writing individual queries by hand we use query builders to compose them. This allows us to keep the code as DRY as possible. The options often include things like searching by name, tags, etc. This is then used all over the place.

One request was to allow sorting by activities. I planned to add a new option (the original code from this issue).

if ($options['load-activity-count']) {
    $qb->addSelect('
        (
            (
                SELECT COUNT(discussion.id)
                FROM App\Entity\Discussion discussion
                WHERE IDENTITY(discussion.author) = user.id
            )
            + (
                SELECT COUNT(comment.id)
                FROM App\Entity\Comment comment
                WHERE IDENTITY(comment.author) = user.id
            )
        ) AS activityCount
    ');
}

if (null !== $orderBy = $options['order-by']) {
    // Exact logic is irrelevant, results in
    $qb->orderBy('activityCount', 'DESC');
}

Writing this as a native query I will have to:

1. Rewrite all needed the options
2. Configure the ResultSetMapping by hand
3. Remember to implement filters by hand (e.g. multi tenancy)
4. Remember to handle Gedmo translations by hand
5. Do all of this for each supported database

I hope you can understand that this is simply not a practical solution for us.