How to COALCASE relation? #6060

New Issue

admin · 2026-01-22T15:25:49+01:00

admin commented

2026-01-22 15:25:49 +01:00

Originally created by @BonBonSlick on GitHub (Sep 11, 2018).

Originally assigned to: @Ocramius on GitHub.

Q	A
Version	"doctrine/orm": "^2.5.4",

Support Question

Example, User entity has 3 fields, A,B,C;All are relations, 2 always null, 1 is always id, let it be type of user details object.

How to select all users and get it is details from 1 of the relations? Because it is 3 different tables to join and it depends on which field is not null. Eg

 return $this->repository->createQueryBuilder('u')
            ->select('u.id, 
              u.email, 
             COALESCE(u.a, u.b, u.c) as relationNotNull, ')
            ->join('u.relationNotNull', 'userTypeTableAlias')
            ->getQuery()
            ->getArrayResult();

it said
User has no association named relationNotNull
Ok, lets try this

        return $this->repository->createQueryBuilder('u')
            ->select('u.id, u.a, u.b, u.c')
            ->join('u.userType', 'userType')
            ->join('COALESCE(u.a, u.b, u.c) as userTypeRelation', 'userTypeTableAlias')
            ->getQuery()
            ->getArrayResult();

we have
[Syntax Error] line 0, col 403: Error: Expected Doctrine\ORM\Query\Lexer::T_ALIASED_NAME, got 'COALESCE'

How to do it right way?

Originally created by @BonBonSlick on GitHub (Sep 11, 2018). Originally assigned to: @Ocramius on GitHub.  | Q | A |------------ | ----- | Version | "doctrine/orm": "^2.5.4", ### Support Question Example, User entity has 3 fields, A,B,C;All are relations, 2 always null, 1 is always id, let it be type of user details object. How to select all users and get it is details from 1 of the relations? Because it is 3 different tables to join and it depends on which field is not null. Eg ``` return $this->repository->createQueryBuilder('u') ->select('u.id, u.email, COALESCE(u.a, u.b, u.c) as relationNotNull, ') ->join('u.relationNotNull', 'userTypeTableAlias') ->getQuery() ->getArrayResult(); ``` it said `User has no association named relationNotNull` Ok, lets try this ``` return $this->repository->createQueryBuilder('u') ->select('u.id, u.a, u.b, u.c') ->join('u.userType', 'userType') ->join('COALESCE(u.a, u.b, u.c) as userTypeRelation', 'userTypeTableAlias') ->getQuery() ->getArrayResult(); ``` we have `[Syntax Error] line 0, col 403: Error: Expected Doctrine\ORM\Query\Lexer::T_ALIASED_NAME, got 'COALESCE'` How to do it right way?

admin added the Question DQL labels 2026-01-22 15:25:49 +01:00

admin closed this issue

2026-01-22 15:25:49 +01:00

admin commented

2026-01-22 15:25:50 +01:00

@Ocramius commented on GitHub (Sep 11, 2018):

You'd simply LEFT JOIN all 3 associations, and then have all 3 fields populated with either the associated entoty or null: then you'll deal with the coalescing in an accessor.

SELECT
    u, a, b, c
FROM Potato u
LEFT JOIN u.a
LEFT JOIN u.b
LEFT JOIN u.c

class Potato
{
    private $a;
    private $b;
    private $c;
    public function gimme() : Something
    {
        return $this->a ?? $this->b ?? $this->c;
    }
}

Note that doctrine associations are statically defined, and cannot be hydrated dynamically.

@Ocramius commented on GitHub (Sep 11, 2018): You'd simply `LEFT JOIN` all 3 associations, and then have all 3 fields populated with either the associated entoty or `null`: then you'll deal with the coalescing in an accessor. ```sql SELECT u, a, b, c FROM Potato u LEFT JOIN u.a LEFT JOIN u.b LEFT JOIN u.c ``` ```php class Potato { private $a; private $b; private $c; public function gimme() : Something { return $this->a ?? $this->b ?? $this->c; } } ``` Note that doctrine associations are statically defined, and cannot be hydrated dynamically.

admin commented

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

@BonBonSlick commented on GitHub (Sep 13, 2018):

@Ocramius thanks)
Loading 3 tables with thousands records not good option)

@BonBonSlick commented on GitHub (Sep 13, 2018): @Ocramius thanks) Loading 3 tables with thousands records not good option)

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