Postgres: Alias cannot be used in select/where/order-by #6174

New Issue

admin · 2026-01-22T15:28:13+01:00

admin commented

2026-01-22 15:28:13 +01:00

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

Bug Report

Q	A
BC Break	no
Version	2.6.3

Summary

I apologize if there's already an issue for this, I couldn't find anything related.

Doctrine allows you to use aliases in various components of the query:

Other SELECT components
WHERE clause
ORDER BY clause

Consider the following rather useless query:

SELECT
    user.id as id,
    (id * 2) as id2
FROM App\Entity\User user

Doctrine happily compiles this to the following SQL:

SELECT
    u0_.id AS id_0,
    (id_0 * 2) AS sclr_1
FROM user_ u0_

This works on MySql but it's actually non-standard syntax. Postgres, Sql Server and others do not support this. The way to do this on those platforms is to use a CTE:

https://stackoverflow.com/a/8095413/1320374

Since CTEs are not supported in Doctrine there's currently no way to execute such a query.

Expected behavior

It would of course be awesome if Doctrine would automatically create a CTE when necessary. But I understand that that would be difficult to implement.

This seems like a difficult problem to fix. Is this a known issue? Is there a workaround?

Originally created by @iluuu1994 on GitHub (Feb 10, 2019). ### Bug Report | Q | A |------------ | ------ | BC Break | no | Version | 2.6.3 #### Summary I apologize if there's already an issue for this, I couldn't find anything related. Doctrine allows you to use aliases in various components of the query: * Other `SELECT` components * `WHERE` clause * `ORDER BY` clause Consider the following rather useless query: ```sql SELECT user.id as id, (id * 2) as id2 FROM App\Entity\User user ``` Doctrine happily compiles this to the following SQL: ```sql SELECT u0_.id AS id_0, (id_0 * 2) AS sclr_1 FROM user_ u0_ ``` This works on MySql but it's actually non-standard syntax. Postgres, Sql Server and others do not support this. The way to do this on those platforms is to use a CTE: https://stackoverflow.com/a/8095413/1320374 Since CTEs are not supported in Doctrine there's currently no way to execute such a query. #### Expected behavior It would of course be awesome if Doctrine would automatically create a CTE when necessary. But I understand that that would be difficult to implement. This seems like a difficult problem to fix. Is this a known issue? Is there a workaround?

admin closed this issue

2026-01-22 15:28:13 +01:00

admin commented

2026-01-22 15:28:14 +01:00

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

I think the DQL component is resolving aliases globally within the context of the entire query, without checking where an alias came from. Does it work with subquery aliases, for example?

@Ocramius commented on GitHub (Feb 11, 2019): I think the DQL component is resolving aliases globally within the context of the entire query, without checking where an alias came from. Does it work with subquery aliases, for example?

admin commented

2026-01-22 15:28:14 +01:00

@Tony-Esales commented on GitHub (Feb 11, 2019):

@iluuu1994
1 - Postgres allows you to use alias in the order by portion. I think it's a standard because it's allowed in Oracle and MySQL too;
2 - Sorry for my ignorance but, what CTE stands for? Can you explain, please?

@Tony-Esales commented on GitHub (Feb 11, 2019): @iluuu1994 1 - Postgres allows you to use alias in the order by portion. I think it's a standard because it's allowed in Oracle and MySQL too; 2 - Sorry for my ignorance but, what CTE stands for? Can you explain, please?

admin commented

2026-01-22 15:28:15 +01:00

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

@Tony-Esales

Right, the ORDER BY could use the index of the alias. Unfortunately that will not work for SELECT and WHERE clauses.

@iluuu1994 commented on GitHub (Feb 11, 2019): @Tony-Esales Right, the `ORDER BY` could use the index of the alias. Unfortunately that will not work for `SELECT` and `WHERE` clauses.

admin commented

2026-01-22 15:28:15 +01:00

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

@Tony-Esales

what CTE stands for?

It stands for common table expression. It's just a different name for the WITH statement.

@iluuu1994 commented on GitHub (Feb 11, 2019): @Tony-Esales > what CTE stands for? It stands for common table expression. It's just a different name for the `WITH` statement.

admin commented

2026-01-22 15:28:15 +01:00

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

@Ocramius

Does it work with subquery aliases, for example?

Yeah, looks like it. The following DQL:

SELECT
    user.id id,
    (
        SELECT user2.id
        FROM App\Entity\User user2
        WHERE user2.id = id
    )
FROM App\Entity\User user

generates this SQL:

SELECT
    u0_.id AS id_0,
    (
        SELECT u1_.id
        FROM user_ u1_
        WHERE u1_.id = id_0
    ) AS sclr_1
FROM user_ u0_

But again, results in a SQL that id_0 doesn't exist.

@iluuu1994 commented on GitHub (Feb 11, 2019): @Ocramius > Does it work with subquery aliases, for example? Yeah, looks like it. The following DQL: ```sql SELECT user.id id, ( SELECT user2.id FROM App\Entity\User user2 WHERE user2.id = id ) FROM App\Entity\User user ``` generates this SQL: ```sql SELECT u0_.id AS id_0, ( SELECT u1_.id FROM user_ u1_ WHERE u1_.id = id_0 ) AS sclr_1 FROM user_ u0_ ``` But again, results in a SQL that `id_0` doesn't exist.

admin referenced this issue

2026-01-22 16:05:42 +01:00

[PR #6174] Merging a new entity with PrePersist event make changes in callback not be considered #9879

admin referenced this issue

2026-01-22 16:05:43 +01:00

[PR #6178] Backport #6177 - fix #6174 #5570: merging new entities should also trigger prepersist lifecycle callbacks with the merged data #9883

admin referenced this issue

2026-01-22 16:05:43 +01:00

[PR #6177] Fix #6174 #5570: merging new entities should also trigger prepersist lifecycle callbacks with merged entity data #9885

Sign in to join this conversation.