Counting rows with a complex Subquery #6293

New Issue

admin · 2026-01-22T15:30:16+01:00

admin commented

2026-01-22 15:30:16 +01:00

Originally created by @kalkin on GitHub (Sep 11, 2019).

Background

I have a complex query which feeds a grid powered by datatables. Datatable needs a page of records(✓), the total amount records(✓) and the total amount of filtered records(✗). The query uses multiple JOIN, WHERE and HAVING clauses. For counting the total amount of filtered records I a subquery:

$count = BaseFactory::getEm()->createQueryBuilder();
$count->select('COUNT(x)')->from($MODEL_NAME, 'x')
	->where($subQuery->expr()->in('x.id', $subQuery->getDQL()))
	->setParameters($subQuery->getParameters());

The query fails with one of the following errors, depending whether I use alias or not:

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

or

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

Feature Request

I read the source code. It looks like the definition of Subselect() is different and not as powerful like the main select. Is there a reason for that?

Summary

It would be great to have a way to count the amount of records in a complex query which contains JOIN, WHERE and HAVING clauses, without fetching all the records and doing a PHP count() on it.

Thank you for your great work
kalkin

PS: Any workarounds or hints for a hack would be great‼

EDIT: I forgot to mention that the actual issue is, that I need more then one column in the SELECT part of the subselect because of the HAVING clause, which is not supported.

Originally created by @kalkin on GitHub (Sep 11, 2019). ### Background I have a complex query which feeds a grid powered by datatables. Datatable needs a page of records(✓), the total amount records(✓) and the total amount of filtered records(✗). The query uses multiple `JOIN`, `WHERE` and `HAVING` clauses. For counting the total amount of filtered records I a subquery: ```php $count = BaseFactory::getEm()->createQueryBuilder(); $count->select('COUNT(x)')->from($MODEL_NAME, 'x') ->where($subQuery->expr()->in('x.id', $subQuery->getDQL())) ->setParameters($subQuery->getParameters()); ``` The query fails with one of the following errors, depending whether I use alias or not: ``` [Syntax Error] line 0, col 81: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ',' ``` or ``` [Syntax Error] line 0, col 81: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got 'AS' ``` ### Feature Request I read the source code. It looks like the definition of `Subselect()` is different and not as powerful like the main select. Is there a reason for that? ### Summary It would be great to have a way to count the amount of records in a complex query which contains `JOIN`, `WHERE` and `HAVING` clauses, without fetching all the records and doing a PHP `count()` on it. Thank you for your great work kalkin PS: Any workarounds or hints for a hack would be great‼ EDIT: I forgot to mention that the actual issue is, that I need more then one column in the `SELECT` part of the subselect because of the `HAVING` clause, which is not supported.

admin commented

2026-01-22 15:30:17 +01:00

@SenseException commented on GitHub (Sep 16, 2019):

As far as I understand you need to fetch many counted values instead of entity objects. What was the reason why you didn't choose SQL for those numbers?

@SenseException commented on GitHub (Sep 16, 2019): As far as I understand you need to fetch many counted values instead of entity objects. What was the reason why you didn't choose SQL for those numbers?

admin commented

2026-01-22 15:30:17 +01:00

@natebrunette commented on GitHub (Sep 19, 2019):

I've run into this as well. My use case is needing to run a haversine function, but I want to exclude the actual distance from the result as it's only used for filtering. My strategy is to do the query in a subquery and then select the normal entity in the main query.

@natebrunette commented on GitHub (Sep 19, 2019): I've run into this as well. My use case is needing to run a haversine function, but I want to exclude the actual distance from the result as it's only used for filtering. My strategy is to do the query in a subquery and then select the normal entity in the main query.

admin commented

2026-01-22 15:30:18 +01:00

@kalkin commented on GitHub (Sep 19, 2019):

As far as I understand you need to fetch many counted values instead of entity objects. What was the reason why you didn't choose SQL for those numbers?

I don't understand your question. Can you elaborate?

@kalkin commented on GitHub (Sep 19, 2019): > As far as I understand you need to fetch many counted values instead of entity objects. What was the reason why you didn't choose SQL for those numbers? I don't understand your question. Can you elaborate?

admin commented

2026-01-22 15:30:18 +01:00

@SenseException commented on GitHub (Sep 23, 2019):

@kalkin You don't seem to fetch objects with your query. Why didn't you use simple SQL instead of DQL for count?

@SenseException commented on GitHub (Sep 23, 2019): @kalkin You don't seem to fetch objects with your query. Why didn't you use simple SQL instead of DQL for count?

admin commented

2026-01-22 15:30:18 +01:00

@natebrunette commented on GitHub (Sep 23, 2019):

For what it's worth, I found the HIDDEN marker which helped me with my particular problem.

@natebrunette commented on GitHub (Sep 23, 2019): For what it's worth, I found the `HIDDEN` marker which helped me with my particular problem.

admin commented

2026-01-22 15:30:19 +01:00

@kalkin commented on GitHub (Sep 24, 2019):

@kalkin You don't seem to fetch objects with your query. Why didn't you use simple SQL instead of DQL for count?

Because in my code I use the subquery in two places:
⒈ To fetch a page of objects (i.e. only first 10)
⒉ To count all available objects.

I do not want to maintain two separate implementations of the same complex query.

@kalkin commented on GitHub (Sep 24, 2019): > @kalkin You don't seem to fetch objects with your query. Why didn't you use simple SQL instead of DQL for count? Because in my code I use the subquery in two places: ⒈ To fetch a page of objects (i.e. only first 10) ⒉ To count all available objects. I do not want to maintain two separate implementations of the same complex query.

admin commented

2026-01-22 15:30:19 +01:00

@SenseException commented on GitHub (Sep 25, 2019):

So no. 1 is another use case in your application and no. 2 the one you showed us above? It is possible to get the SQL from $subquery to use it as an SQL subquery.

What does the subquery look like?

@SenseException commented on GitHub (Sep 25, 2019): So no. 1 is another use case in your application and no. 2 the one you showed us above? It is possible to get the SQL from `$subquery` to use it as an SQL subquery. What does the subquery look like?

admin commented

2026-01-22 15:30:19 +01:00

@kalkin commented on GitHub (Oct 6, 2019):

What does the subquery look like?

SELECT foo, bar FROM buz HAVING foo = 3 AND bar LIKE '%asd%'

I'm actually getting annoyed by this kind of questions, it doesn't matter how the subquery looks like. All the information you need (if you want to fix this issue at all, may be it's not a bug, but a feature?) is in my first posting. May be we misunderstand each others intentions. I don't seek help for finding a way to achieve what I want. I already achieved it by not using the HAVING clause, after optimizing the data layout in the database.

To sum it up:

For no obvious reason (at least to me) the Doctrine DQL Parser expects a subselect to contain at most one column in the SELECT part and does not support aliasing. After skimming through Doctrine code (look at the Subselect()) I'm not sure if there is a reason to limit the parser in such way.

What can Doctrine improve?

If you decide that this behaviour is wrong and you want to allow the subselect to be as powerful as SQL allows it, you need to fix your parser. IMHO it shouldn't be that hard because you already have all the code there.
If you decide that this behaviour is intentional, you need to fix the error messages. I wasted half a day debugging to figure out what the actual issue is. Also document that subselects are actually limited to have only one column and do not support aliases.

@kalkin commented on GitHub (Oct 6, 2019): > What does the subquery look like? `SELECT foo, bar FROM buz HAVING foo = 3 AND bar LIKE '%asd%'` I'm actually getting annoyed by this kind of questions, it doesn't matter how the subquery looks like. All the information you need (if you want to fix this issue at all, may be it's not a bug, but a feature?) is in my first posting. May be we misunderstand each others intentions. I don't seek help for finding a way to achieve what I want. I already achieved it by not using the `HAVING` clause, after optimizing the data layout in the database. #### To sum it up: For no obvious reason (at least to me) the Doctrine DQL Parser expects a subselect to contain *at most one column* in the `SELECT` part and *does not support aliasing*. After skimming through Doctrine code (look at the Subselect()) I'm not sure if there is a reason to limit the parser in such way. #### What can Doctrine improve? * If you decide that this behaviour is wrong and you want to allow the subselect to be as powerful as SQL allows it, you need to fix your parser. IMHO it shouldn't be that hard because you already have all the code there. * If you decide that this behaviour is intentional, you need to fix the error messages. I wasted half a day debugging to figure out what the actual issue is. Also document that subselects are actually limited to have only one column and do not support aliases.

admin commented

2026-01-22 15:30:20 +01:00

@SenseException commented on GitHub (Oct 10, 2019):

DQL is not supposed to be as powerful as SQL. DQL isn't supposed to replace SQL because SQL is far more powerful than Doctrine ORM ever will be. The ORM should be used to return objects by the use of DQL or one of the find-methods. Your initial query is about returning a count, which is why my first question was about using SQL because no objects are expected in the result.

My questions are to understand more about the use case and if there is need to improve the parser in that way. If an improvement is desirable or not doesn't depend on my decision only though. I keep this issue open for further discussion. If you like to make DQL subselects as powerful as the main DQL selects, you (and others) are welcome to create pull requests. I suggest smaller ones to make step by step improvements.

@SenseException commented on GitHub (Oct 10, 2019): DQL is not supposed to be as powerful as SQL. DQL isn't supposed to replace SQL because SQL is far more powerful than Doctrine ORM ever will be. The ORM should be used to return objects by the use of DQL or one of the find-methods. Your initial query is about returning a count, which is why my first question was about using SQL because no objects are expected in the result. My questions are to understand more about the use case and if there is need to improve the parser in that way. If an improvement is desirable or not doesn't depend on my decision only though. I keep this issue open for further discussion. If you like to make DQL subselects as powerful as the main DQL selects, you (and others) are welcome to create pull requests. I suggest smaller ones to make step by step improvements.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6293