Doctrine QueryBuilder changes JOIN order - Is this expected behavior?

[berkut1]

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!

[derrabus]

My question is: Is this the expected behavior?

Yes. 🙂

[berkut1]

Thanks :)