Doctrine QueryBuilder changes JOIN order - Is this expected behavior? #7469

New Issue

admin · 2026-01-22T15:52:06+01:00

admin commented

2026-01-22 15:52:06 +01:00

Originally created by @berkut1 on GitHub (Jan 30, 2025).

ORM: 2.20

I’m not sure how to phrase my question and even is this a bug or not, so I’ll just describe the situation.

I have a complex query where I initially used subqueries in JOINs. While refactoring, I decided to split the subquery into multiple JOINs. The final query looks like this:

$this->connection->createQueryBuilder()->select()
            ->from('a_table', 'a')
            ->leftJoin('a', 'b_table', 'b', 'b.id = a.id')
            ->leftJoin('a', 'c_table', 'c', 'c.id = a.id')
            ->leftJoin('c', 'd_table', 'd', 'd.id = c.id')
            ->leftJoin('a', 'zz_table', 'zz', 'zz.id = a.id_zz AND NOT d.is_true')
            ->leftJoin('zz', 'xx_table', 'xx', 'zz.id = xx.id')
            ->leftJoin('d', 'yy_table', 'yy', 'yy.id = CASE
                            WHEN d.is_true THEN b.id_bb
                            ELSE xx.id_xx
                            END');

Since I initially wrote the query as RAW SQL, I expected Doctrine to strictly follow the order in which the JOINs are written. However, that turned out not to be the case.

Doctrine actually executes the query in a different order (simplified example):

$this->connection->createQueryBuilder()->select()
            ->from('a_table', 'a')
            ->leftJoin('a', 'b_table', 'b', 'b.id = a.id')
            ->leftJoin('d', 'yy_table', 'yy', 'yy.id = CASE
                            WHEN d.is_true THEN b.id_bb
                            ELSE xx.id_xx
                            END');
            ->leftJoin('a', 'zz_table', 'zz', 'zz.id = a.id_zz AND NOT d.is_true')
            ->leftJoin('a', 'c_table', 'c', 'c.id = a.id')
            ->leftJoin('c', 'd_table', 'd', 'd.id = c.id')
            ->leftJoin('zz', 'xx_table', 'xx', 'zz.id = xx.id')

As a result, I got an error stating that Doctrine couldn’t resolve alias b because it rearranged the JOIN order.

After hours of trial and error, I finally found a solution:
In Doctrine, an alias is not just an alias - it also determines the execution order of JOINs.

So, I had to change my aliases like this:

$this->connection->createQueryBuilder()->select()
    ->from('a_table', 'a')
    ->leftJoin('a', 'b_table', 'b', 'b.id = a.id')
    ->leftJoin('a', 'c_table', 'c', 'c.id = a.id')
    ->leftJoin('c', 'd_table', 'd', 'd.id = c.id')
    ->leftJoin('d', 'zz_table', 'zz', 'zz.id = a.id_zz AND NOT d.is_true') // Changed fromAlias from "a" to "d"
    ->leftJoin('zz', 'xx_table', 'xx', 'zz.id = xx.id')
    // Changed fromAlias from "d" to "xx"
    ->leftJoin('xx', 'yy_table', 'yy', 'yy.id = CASE 
        WHEN d.is_true THEN b.id_bb
        ELSE xx.id_xx
    END');

This effectively creates a dependency chain between aliases and JOINs: fromAlias → Alias.

My question is:
Is this the expected behavior? I couldn't find anything in the documentation (or maybe I missed it) stating that aliases determine the JOIN execution order, overriding the written order of the query.

Thanks!

Originally created by @berkut1 on GitHub (Jan 30, 2025). **ORM:** 2.20 I’m not sure how to phrase my question and even is this a bug or not, so I’ll just describe the situation. I have a complex query where I initially used subqueries in JOINs. While refactoring, I decided to split the subquery into multiple JOINs. The final query looks like this: ```php $this->connection->createQueryBuilder()->select() ->from('a_table', 'a') ->leftJoin('a', 'b_table', 'b', 'b.id = a.id') ->leftJoin('a', 'c_table', 'c', 'c.id = a.id') ->leftJoin('c', 'd_table', 'd', 'd.id = c.id') ->leftJoin('a', 'zz_table', 'zz', 'zz.id = a.id_zz AND NOT d.is_true') ->leftJoin('zz', 'xx_table', 'xx', 'zz.id = xx.id') ->leftJoin('d', 'yy_table', 'yy', 'yy.id = CASE WHEN d.is_true THEN b.id_bb ELSE xx.id_xx END'); ``` Since I initially wrote the query as RAW SQL, I expected Doctrine to strictly follow the order in which the JOINs are written. However, that turned out not to be the case. Doctrine actually executes the query in a different order (simplified example): ```php $this->connection->createQueryBuilder()->select() ->from('a_table', 'a') ->leftJoin('a', 'b_table', 'b', 'b.id = a.id') ->leftJoin('d', 'yy_table', 'yy', 'yy.id = CASE WHEN d.is_true THEN b.id_bb ELSE xx.id_xx END'); ->leftJoin('a', 'zz_table', 'zz', 'zz.id = a.id_zz AND NOT d.is_true') ->leftJoin('a', 'c_table', 'c', 'c.id = a.id') ->leftJoin('c', 'd_table', 'd', 'd.id = c.id') ->leftJoin('zz', 'xx_table', 'xx', 'zz.id = xx.id') ``` As a result, I got an error stating that Doctrine couldn’t resolve alias `b` because it rearranged the `JOIN` order. After hours of trial and error, I finally found a solution: In Doctrine, an alias is not just an alias - it also determines the execution order of JOINs. So, I had to change my aliases like this: ```php $this->connection->createQueryBuilder()->select() ->from('a_table', 'a') ->leftJoin('a', 'b_table', 'b', 'b.id = a.id') ->leftJoin('a', 'c_table', 'c', 'c.id = a.id') ->leftJoin('c', 'd_table', 'd', 'd.id = c.id') ->leftJoin('d', 'zz_table', 'zz', 'zz.id = a.id_zz AND NOT d.is_true') // Changed fromAlias from "a" to "d" ->leftJoin('zz', 'xx_table', 'xx', 'zz.id = xx.id') // Changed fromAlias from "d" to "xx" ->leftJoin('xx', 'yy_table', 'yy', 'yy.id = CASE WHEN d.is_true THEN b.id_bb ELSE xx.id_xx END'); ``` This effectively creates a dependency chain between aliases and JOINs: fromAlias → Alias. ***My question is:*** Is this the expected behavior? I couldn't find anything in the documentation (or maybe I missed it) stating that aliases determine the JOIN execution order, overriding the written order of the query. Thanks!

admin closed this issue

2026-01-22 15:52:07 +01:00

admin referenced this issue

2026-01-22 16:07:46 +01:00

[PR #7469] Sync master with 2.x #10502

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#7469