doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

Subqueries in arithmetic expressions #6169

New Issue
Closed
opened 2026-01-22 15:28:06 +01:00 by admin · 9 comments
admin commented 2026-01-22 15:28:06 +01:00
Owner
Copy Link

Originally created by @iluuu1994 on GitHub (Feb 6, 2019).

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?

Originally created by @iluuu1994 on GitHub (Feb 6, 2019). ### Bug Report | Q | A |------------ | ------ | BC Break | no | Version | 2.6.3 #### Summary I have a query that looks something like this: ```sql 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?
admin added the New FeatureQuestion labels 2026-01-22 15:28:06 +01:00
admin closed this issue 2026-01-22 15:28:06 +01:00
admin commented 2026-01-22 15:28:07 +01:00
Author
Owner
Copy Link

@SenseException commented on GitHub (Feb 7, 2019):

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.

@SenseException commented on GitHub (Feb 7, 2019): 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.
admin commented 2026-01-22 15:28:08 +01:00
Author
Owner
Copy Link

@iluuu1994 commented on GitHub (Feb 7, 2019):

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.

@iluuu1994 commented on GitHub (Feb 7, 2019): > 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.
admin commented 2026-01-22 15:28:08 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Feb 9, 2019):

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 commented on GitHub (Feb 9, 2019): > 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`)
admin commented 2026-01-22 15:28:08 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Feb 9, 2019):

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.

@Ocramius commented on GitHub (Feb 9, 2019): 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.
admin commented 2026-01-22 15:28:08 +01:00
Author
Owner
Copy Link

@iluuu1994 commented on GitHub (Feb 9, 2019):

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.

@iluuu1994 commented on GitHub (Feb 9, 2019): 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 <?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). ```sql 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.
admin commented 2026-01-22 15:28:09 +01:00
Author
Owner
Copy Link

@kconde2 commented on GitHub (Nov 10, 2022):

Any update with this ?

I got same issue when I tried to write this in DQL

SELECT 
  u.gender gender,
  count(u.id) nb,
  (count(u.id)/(select count(*) FROM student))*100 as percentage
FROM student s inner join `user` u on s.user_id = u.id
group by u.gender;

Did you find a workaround ?

@kconde2 commented on GitHub (Nov 10, 2022): Any update with this ? I got same issue when I tried to write this in DQL ```sql SELECT u.gender gender, count(u.id) nb, (count(u.id)/(select count(*) FROM student))*100 as percentage FROM student s inner join `user` u on s.user_id = u.id group by u.gender; ``` Did you find a workaround ?
admin commented 2026-01-22 15:28:09 +01:00
Author
Owner
Copy Link

@worthwhileindustries commented on GitHub (Jan 28, 2024):

Painful. Bumping... anyone have a workaround for this? Doing it this way was already a workaround for another reason DQL couldn't do something. Now, I find myself looking for a workaround for the workaround...

@worthwhileindustries commented on GitHub (Jan 28, 2024): Painful. Bumping... anyone have a workaround for this? Doing it this way was already a workaround for another reason DQL couldn't do something. Now, I find myself looking for a workaround for the workaround...
admin commented 2026-01-22 15:28:09 +01:00
Author
Owner
Copy Link

@GonZOO82 commented on GitHub (Jan 28, 2024):

@worthwhileindustries this my old but similar code, try out this logic


public function getByProduct(Product $product)
{

    $stockMovements = $this->_em->getRepository(StockMovement::class)->createQueryBuilder('sm')
        ->select('count(sm.id)')
        ->leftJoin('sm.serialNumbers', 'sn')
        ->leftJoin('sm.cart', 'c')
        ->andWhere('sm.movementType = :sale')
        ->andWhere('c.status = :completed')
        ->andWhere('sn = serialNumber');

    $scarp = $this->_em->getRepository(StockMovement::class)->createQueryBuilder('sm2')
        ->select('count(sm2.id)')
        ->leftJoin('sm2.serialNumbers', 'sn2')
        ->andWhere('sm2.movementType = :scrap')
        ->andWhere('sn2 = serialNumber');

    $qb = $this->createQueryBuilder('serialNumber');

    $qb
        ->leftJoin('serialNumber.stockMovements', 'stockMovements')
        ->leftJoin('stockMovements.cart', 'cart')
        ->where('serialNumber.product = :product')
        ->andWhere(
            $qb->expr()->orX(
                $qb->expr()->andX(
                    $qb->expr()->isNotNull('stockMovements.cart'),
                    $qb->expr()->neq('cart.status', $qb->expr()->literal(Cart::STATUS_COMPLETED))
                ),
                $qb->expr()->eq('(' . $stockMovements->getDQL() . ')', 0),
            ),
            $qb->expr()->eq('(' . $scarp->getDQL() . ')', 0)
        )
        ->setParameters([
            'product' => $product,
            'sale' => StockMovement::TYPE_SALE,
            'scrap' => StockMovement::TYPE_SCRAP,
            'completed' => Cart::STATUS_COMPLETED
        ]);

    return $qb;
}
@GonZOO82 commented on GitHub (Jan 28, 2024): @worthwhileindustries this my old but similar code, try out this logic ```php public function getByProduct(Product $product) { $stockMovements = $this->_em->getRepository(StockMovement::class)->createQueryBuilder('sm') ->select('count(sm.id)') ->leftJoin('sm.serialNumbers', 'sn') ->leftJoin('sm.cart', 'c') ->andWhere('sm.movementType = :sale') ->andWhere('c.status = :completed') ->andWhere('sn = serialNumber'); $scarp = $this->_em->getRepository(StockMovement::class)->createQueryBuilder('sm2') ->select('count(sm2.id)') ->leftJoin('sm2.serialNumbers', 'sn2') ->andWhere('sm2.movementType = :scrap') ->andWhere('sn2 = serialNumber'); $qb = $this->createQueryBuilder('serialNumber'); $qb ->leftJoin('serialNumber.stockMovements', 'stockMovements') ->leftJoin('stockMovements.cart', 'cart') ->where('serialNumber.product = :product') ->andWhere( $qb->expr()->orX( $qb->expr()->andX( $qb->expr()->isNotNull('stockMovements.cart'), $qb->expr()->neq('cart.status', $qb->expr()->literal(Cart::STATUS_COMPLETED)) ), $qb->expr()->eq('(' . $stockMovements->getDQL() . ')', 0), ), $qb->expr()->eq('(' . $scarp->getDQL() . ')', 0) ) ->setParameters([ 'product' => $product, 'sale' => StockMovement::TYPE_SALE, 'scrap' => StockMovement::TYPE_SCRAP, 'completed' => Cart::STATUS_COMPLETED ]); return $qb; } ```
admin commented 2026-01-22 15:28:10 +01:00
Author
Owner
Copy Link

@worthwhileindustries commented on GitHub (Jan 29, 2024):

@GonZOO82 Thanks for the reply but, I'm not sure if can pickup what you are putting down. This is my DQL and I wrote the lexer code and it spits out DQL fine when I getDQL but, the fails on the getSQL

**EDIT: I was missing a closing parens on my lexer which fixed the problem. Thanks for the rubber duck

The error from getSQL ... not sure if there is another way to extend the lexer or the sqlwaker from here or not? Any thoughts?

[Syntax Error] line 0, col 468: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ')'

SELECT
  o,
  SUM_SUB_SELECT((
    SELECT
      COALESCE(SUM(MATCH_AGAINST(o_a3.locationName) AGAINST(:locationName_p3 BOOLEAN) * 1), 0)
    FROM
      App\Entity\Location o_a3
    WHERE
      o_a3.id = o.id
    GROUP BY
      o_a3.id
  ) +
  (
    SELECT
      COALESCE(SUM(MATCH_AGAINST(o_a5.locationDescription) AGAINST(:locationDescription_p4 BOOLEAN) * 1), 0)
    FROM
      App\Entity\Location o_a5
    WHERE
      o_a5.id = o.id
    GROUP BY
      o_a5.id
  )) AS relevance
FROM
  App\Entity\Location o
WHERE
  o.id IN (
    SELECT
      DISTINCT o_a1.id
    FROM
      App\Entity\Location o_a1
      LEFT JOIN o_a1.catalogRecords catalogRecords_a2
    WHERE
      o_a1.locationStatusId = :locationStatusId_p1
      AND catalogRecords_a2.catalogStatusId = :catalogStatusId_p2
  )
HAVING
  relevance > 0

<?php

declare(strict_types=1);

namespace App\Doctrine\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Subselect;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

/**
 * SumSubSelectFunction ::=
 *     "SUM_SUB_SELECT" "("Subselect + Subselect")"
 */
class SumSubSelectFunction extends FunctionNode
{
    /**
     * @var Subselect
     */
    private $subselect1;

    /**
     * @var Subselect
     */
    private $subselect2;

    /**
     * {@inheritdoc}
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->subselect1 = $parser->Subselect();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
        $parser->match(Lexer::T_PLUS);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->subselect2 = $parser->Subselect();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
        $parser->match(Lexer::T_CLOSE_PARENTHESIS); // missing this, adding it fixed my problem
    }

    /**
     * {@inheritdoc}
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        return '(' . $this->subselect1->dispatch($sqlWalker) . ') + (' . $this->subselect2->dispatch($sqlWalker) . ')'; // removed this `AS relevance` as it will make an alias for you
    }
}
@worthwhileindustries commented on GitHub (Jan 29, 2024): @GonZOO82 Thanks for the reply but, I'm not sure if can pickup what you are putting down. This is my DQL and I wrote the lexer code and it spits out DQL fine when I `getDQL` but, the fails on the `getSQL` **EDIT: I was missing a closing parens on my lexer which fixed the problem. Thanks for the rubber duck The error from getSQL ... not sure if there is another way to extend the lexer or the sqlwaker from here or not? Any thoughts? `[Syntax Error] line 0, col 468: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ')'` ``` SELECT o, SUM_SUB_SELECT(( SELECT COALESCE(SUM(MATCH_AGAINST(o_a3.locationName) AGAINST(:locationName_p3 BOOLEAN) * 1), 0) FROM App\Entity\Location o_a3 WHERE o_a3.id = o.id GROUP BY o_a3.id ) + ( SELECT COALESCE(SUM(MATCH_AGAINST(o_a5.locationDescription) AGAINST(:locationDescription_p4 BOOLEAN) * 1), 0) FROM App\Entity\Location o_a5 WHERE o_a5.id = o.id GROUP BY o_a5.id )) AS relevance FROM App\Entity\Location o WHERE o.id IN ( SELECT DISTINCT o_a1.id FROM App\Entity\Location o_a1 LEFT JOIN o_a1.catalogRecords catalogRecords_a2 WHERE o_a1.locationStatusId = :locationStatusId_p1 AND catalogRecords_a2.catalogStatusId = :catalogStatusId_p2 ) HAVING relevance > 0 ``` ``` <?php declare(strict_types=1); namespace App\Doctrine\DQL; use Doctrine\ORM\Query\AST\Functions\FunctionNode; use Doctrine\ORM\Query\AST\Subselect; use Doctrine\ORM\Query\Lexer; use Doctrine\ORM\Query\Parser; use Doctrine\ORM\Query\SqlWalker; /** * SumSubSelectFunction ::= * "SUM_SUB_SELECT" "("Subselect + Subselect")" */ class SumSubSelectFunction extends FunctionNode { /** * @var Subselect */ private $subselect1; /** * @var Subselect */ private $subselect2; /** * {@inheritdoc} */ public function parse(Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->subselect1 = $parser->Subselect(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); $parser->match(Lexer::T_PLUS); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->subselect2 = $parser->Subselect(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); $parser->match(Lexer::T_CLOSE_PARENTHESIS); // missing this, adding it fixed my problem } /** * {@inheritdoc} */ public function getSql(SqlWalker $sqlWalker) { return '(' . $this->subselect1->dispatch($sqlWalker) . ') + (' . $this->subselect2->dispatch($sqlWalker) . ')'; // removed this `AS relevance` as it will make an alias for you } } ```
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label New Feature Question
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6169
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?