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

Incorrect column alias when using multiple "addSelect" #5489

New Issue
Open
opened 2026-01-22 15:09:00 +01:00 by admin · 10 comments
admin commented 2026-01-22 15:09:00 +01:00
Owner
Copy Link

Originally created by @sboesch on GitHub (Mar 30, 2017).

I'm trying to build the following SQL query with Doctrine QueryBuilder:

SELECT 
  sp.*,
  ((
    SELECT COUNT(spa.id) FROM project_attribute AS spa WHERE spa.project = sp.id AND spa.attribute = pa.attribute AND spa.attribute_value = pa.attribute_value
  ) * 1)
  AS similarity
FROM project AS p
LEFT JOIN project_attribute AS pa ON (p.id = pa.project)
LEFT JOIN project AS sp ON (p.id != sp.id)
WHERE 
  p.id = <PROJECT-ID>
GROUP BY sp.id
ORDER BY 
  similarity DESC

The querys purpose is to find all "projects", ordered by "similarity" to a given project. Similarity is calculated by counting matching relationships between projects and foreign tables (eg. project_attribute).
The example above works as expected when executed via MySQL.

Now I tried to build this query via Doctrine:

$qb->select('sp')
    ->addSelect(
        '(SELECT COUNT(spa.id) FROM AppBundle:ProjectAttribute spa WHERE spa.project = sp.id AND spa.attribute = pa.attribute AND spa.attributeValue = pa.attributeValue) AS attributeSimilarity'
    )
    ->addSelect(
        '(attributeSimilarity * 1) AS similarity'
    )
    ->from('AppBundle:Project', 'p')
    ->leftJoin('AppBundle:ProjectAttribute', 'pa', 'WITH', 'p = pa.project')
    ->leftJoin('AppBundle:Project', 'sp', 'WITH', 'p != sp')
    ->where('p = :project')
    ->setParameter(':project', $project)
    ->groupBy('sp.id')
    ->orderBy('similarity', 'DESC')
    ->getQuery()
    ->getResult();

Unfortunately, "attributeSimilarity" from the first "addSelect" gets aliased to "sclr_31", while it doesn't use the alias in the second "addSelect":

AS sclr_31, (attributeSimilarity * 1) AS sclr_32
Column not found: 1054 Unknown column 'attributeSimilarity' in 'field list'

Expected result is:
AS sclr_31, (sclr_31 * 1) AS sclr_32

Now, I'm not sure - is this a bug or did I something wrong?

Ah, I forgot to mention that the final query contains multiple subqueries which results will be summed up and multiplied to get a final "similarity" value. That's the reason I separated "similarity" into it's own "addSelect".

Originally created by @sboesch on GitHub (Mar 30, 2017). I'm trying to build the following SQL query with Doctrine QueryBuilder: ``` SELECT sp.*, (( SELECT COUNT(spa.id) FROM project_attribute AS spa WHERE spa.project = sp.id AND spa.attribute = pa.attribute AND spa.attribute_value = pa.attribute_value ) * 1) AS similarity FROM project AS p LEFT JOIN project_attribute AS pa ON (p.id = pa.project) LEFT JOIN project AS sp ON (p.id != sp.id) WHERE p.id = <PROJECT-ID> GROUP BY sp.id ORDER BY similarity DESC ``` The querys purpose is to find all "projects", ordered by "similarity" to a given project. Similarity is calculated by counting matching relationships between projects and foreign tables (eg. project_attribute). The example above works as expected when executed via MySQL. Now I tried to build this query via Doctrine: ``` $qb->select('sp') ->addSelect( '(SELECT COUNT(spa.id) FROM AppBundle:ProjectAttribute spa WHERE spa.project = sp.id AND spa.attribute = pa.attribute AND spa.attributeValue = pa.attributeValue) AS attributeSimilarity' ) ->addSelect( '(attributeSimilarity * 1) AS similarity' ) ->from('AppBundle:Project', 'p') ->leftJoin('AppBundle:ProjectAttribute', 'pa', 'WITH', 'p = pa.project') ->leftJoin('AppBundle:Project', 'sp', 'WITH', 'p != sp') ->where('p = :project') ->setParameter(':project', $project) ->groupBy('sp.id') ->orderBy('similarity', 'DESC') ->getQuery() ->getResult(); ``` Unfortunately, "attributeSimilarity" from the first "addSelect" gets aliased to "sclr_31", while it doesn't use the alias in the second "addSelect": `AS sclr_31, (attributeSimilarity * 1) AS sclr_32` `Column not found: 1054 Unknown column 'attributeSimilarity' in 'field list'` Expected result is: `AS sclr_31, (sclr_31 * 1) AS sclr_32` Now, I'm not sure - is this a bug or did I something wrong? Ah, I forgot to mention that the final query contains multiple subqueries which results will be summed up and multiplied to get a final "similarity" value. That's the reason I separated "similarity" into it's own "addSelect".
admin added the BugMissing Tests labels 2026-01-22 15:09:00 +01:00
admin commented 2026-01-22 15:09:01 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Mar 30, 2017):

This seems wrong - we don't really support sub-SELECT clauses in the SELECT clause. What's the DQL here?

@Ocramius commented on GitHub (Mar 30, 2017): This seems wrong - we don't really support sub-`SELECT` clauses in the `SELECT` clause. What's the DQL here?
admin commented 2026-01-22 15:09:01 +01:00
Author
Owner
Copy Link

@sboesch commented on GitHub (Mar 30, 2017):

$qb->getDQL() returns this:

SELECT
  
  sp,
  
  (SELECT COUNT(spa.id)
   FROM AppBundle:ProjectAttribute spa 
   WHERE spa.project = sp.id AND spa.attribute = pa.attribute AND spa.attributeValue = pa.attributeValue) 
   AS attributeSimilarity,

  (SELECT COUNT(spt.id) 
   FROM AppBundle:ProjectTask spt 
   WHERE spt.project = sp.id AND spt.address = pt.address) 
   AS taskSimilarity,

  (SELECT COUNT(spc.id) 
   FROM AppBundle:ProjectCampaign spc 
   WHERE spc.project = sp.id AND spc.campaign = pc.campaign) 
   AS campaignSimilarity,

  ( attributeSimilarity * 1 + taskSimilarity * 0.25 + campaignSimilarity * 2 ) 
   AS similarity 
   
FROM AppBundle:Project p 
   
LEFT JOIN AppBundle:ProjectAttribute pa WITH p = pa.project 
LEFT JOIN AppBundle:ProjectTask pt WITH p = pt.project 
LEFT JOIN AppBundle:ProjectCampaign pc WITH p = pc.project 
LEFT JOIN AppBundle:Project sp WITH p != sp WHERE p = :project AND sp.is_live = TRUE 

GROUP BY sp.id 

ORDER BY similarity DESC, sp.created DESC

Basically I only need this sub selects to order the result by relevance. Any other way to achieve this besides raw SQL?

@sboesch commented on GitHub (Mar 30, 2017): $qb->getDQL() returns this: ``` SELECT sp, (SELECT COUNT(spa.id) FROM AppBundle:ProjectAttribute spa WHERE spa.project = sp.id AND spa.attribute = pa.attribute AND spa.attributeValue = pa.attributeValue) AS attributeSimilarity, (SELECT COUNT(spt.id) FROM AppBundle:ProjectTask spt WHERE spt.project = sp.id AND spt.address = pt.address) AS taskSimilarity, (SELECT COUNT(spc.id) FROM AppBundle:ProjectCampaign spc WHERE spc.project = sp.id AND spc.campaign = pc.campaign) AS campaignSimilarity, ( attributeSimilarity * 1 + taskSimilarity * 0.25 + campaignSimilarity * 2 ) AS similarity FROM AppBundle:Project p LEFT JOIN AppBundle:ProjectAttribute pa WITH p = pa.project LEFT JOIN AppBundle:ProjectTask pt WITH p = pt.project LEFT JOIN AppBundle:ProjectCampaign pc WITH p = pc.project LEFT JOIN AppBundle:Project sp WITH p != sp WHERE p = :project AND sp.is_live = TRUE GROUP BY sp.id ORDER BY similarity DESC, sp.created DESC ``` Basically I only need this sub selects to order the result by relevance. Any other way to achieve this besides raw SQL?
admin commented 2026-01-22 15:09:02 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Mar 30, 2017):

My mistake - it seems like the ebnf declares sub-SELECT statements. The bug is valid, we just need a minimal reproduction scenario.

@Ocramius commented on GitHub (Mar 30, 2017): My mistake - it seems like the [ebnf](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#ebnf) declares sub-`SELECT` statements. The bug is valid, we just need a minimal reproduction scenario.
admin commented 2026-01-22 15:09:02 +01:00
Author
Owner
Copy Link

@sboesch commented on GitHub (Apr 10, 2017):

I'm sorry, I just found out that using Aliases within another Alias isn't even allowed in MySQL.
When I wrote and tested the query above in raw MySQL, I haven't used Aliases but directly added the subqueries COUNT() results together; which works.
Unfortunately this style of writing doesn't work either with DQL.
So I guess this issue isn't actually a bug.

Anyway, is possible to get the desired results with DQL?
I really don't want to use raw MySQL.

Basically I want to write the following:

SELECT
  a.*,
  (SELECT COUNT(b.id) FROM table_b AS b) + (SELECT COUNT(c.id) FROM table_c AS c) AS priority
FROM
  table_a AS a
ORDER BY 
  priority DESC
@sboesch commented on GitHub (Apr 10, 2017): I'm sorry, I just found out that using Aliases within another Alias isn't even allowed in MySQL. When I wrote and tested the query above in raw MySQL, I haven't used Aliases but directly added the subqueries COUNT() results together; which works. Unfortunately this style of writing doesn't work either with DQL. So I guess this issue isn't actually a bug. Anyway, is possible to get the desired results with DQL? I really don't want to use raw MySQL. Basically I want to write the following: ``` SELECT a.*, (SELECT COUNT(b.id) FROM table_b AS b) + (SELECT COUNT(c.id) FROM table_c AS c) AS priority FROM table_a AS a ORDER BY priority DESC ```
admin commented 2026-01-22 15:09:03 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Apr 10, 2017):

Uhm... I would just say to keep it in raw SQL unless there is a strong need
for it to be DQL...

On 10 Apr 2017 4:13 p.m., "sboesch" notifications@github.com wrote:

I'm sorry, I just found out that using Aliases within another Alias isn't
even allowed in MySQL.
When I wrote and tested the query above in raw MySQL, I haven't used
Aliases but directly added the subqueries COUNT() results together; which
works.
Unfortunately this style of writing doesn't work either with DQL.
So I guess this issue isn't actually a bug.

Anyway, is possible to get the desired results with DQL?
I really don't want to use raw MySQL.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/6372#issuecomment-292961804,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakJzXTtFvx6h3cNmSRgbaYOZmCvDxks5rujkbgaJpZM4MuDjc
.

@Ocramius commented on GitHub (Apr 10, 2017): Uhm... I would just say to keep it in raw SQL unless there is a strong need for it to be DQL... On 10 Apr 2017 4:13 p.m., "sboesch" <notifications@github.com> wrote: > I'm sorry, I just found out that using Aliases within another Alias isn't > even allowed in MySQL. > When I wrote and tested the query above in raw MySQL, I haven't used > Aliases but directly added the subqueries COUNT() results together; which > works. > Unfortunately this style of writing doesn't work either with DQL. > So I guess this issue isn't actually a bug. > > Anyway, is possible to get the desired results with DQL? > I really don't want to use raw MySQL. > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > <https://github.com/doctrine/doctrine2/issues/6372#issuecomment-292961804>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAJakJzXTtFvx6h3cNmSRgbaYOZmCvDxks5rujkbgaJpZM4MuDjc> > . >
admin commented 2026-01-22 15:09:03 +01:00
Author
Owner
Copy Link

@sboesch commented on GitHub (Apr 10, 2017):

Ok, thank you, i'll stick to raw SQL...
So the statement above is not supposed to be supported by doctrine?

@sboesch commented on GitHub (Apr 10, 2017): Ok, thank you, i'll stick to raw SQL... So the statement above is not supposed to be supported by doctrine?
admin commented 2026-01-22 15:09:03 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Apr 10, 2017):

@sboesch unless there's a strong reason to do that, it's not worth doing so. The fact that the generated SQL is broken is still a bug though.

@Ocramius commented on GitHub (Apr 10, 2017): @sboesch unless there's a strong reason to do that, it's not worth doing so. The fact that the generated SQL is broken is still a bug though.
admin commented 2026-01-22 15:09:04 +01:00
Author
Owner
Copy Link

@wlalele commented on GitHub (Jul 7, 2017):

@Ocramius if there is an actual strong need for it to be in DQL, how would you do it ?

@wlalele commented on GitHub (Jul 7, 2017): @Ocramius if there is an actual strong need for it to be in DQL, how would you do it ?
admin commented 2026-01-22 15:09:04 +01:00
Author
Owner
Copy Link

@stof commented on GitHub (Mar 7, 2018):

What is not supported AFAIK is to refer to a ResultAliasVariable as part of another expression in the select (and so you cannot reference the attributeSimilarity variable for the next select expression)

@stof commented on GitHub (Mar 7, 2018): What is not supported AFAIK is to refer to a ResultAliasVariable as part of another expression in the select (and so you cannot reference the `attributeSimilarity` variable for the next select expression)
admin commented 2026-01-22 15:09:05 +01:00
Author
Owner
Copy Link

@tugrul commented on GitHub (Sep 15, 2020):

This bug also occurs when use @OrderBy annotation.

@tugrul commented on GitHub (Sep 15, 2020): This bug also occurs when use `@OrderBy` annotation.
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 Bug Missing Tests
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#5489
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?