CODEWITHSUNDEEP
symfonydoctrine-orm
Alexandr Sharamko
Alexandr Sharamko

Reputation: 107

How I can create multiple join with DQL

How I can create multiple join with DQL , something like this in sql:

SELECT t,c,u FROM `user` AS t
LEFT JOIN (`contract` AS c, `car` AS u) 
  ON (t.id = c.`user_id` AND u.id = c.`unit_id` AND u.name = 'audi')
WHERE t.email = '[email protected]';

Solve problem by another way, create sub-query:

    $query = $this->createQueryBuilder('user')
        ->addSelect(
            array('contract', 'car')
        )
    ;
    $query->leftJoin(
        'user.contracts',
        'contract',
        Expr\Join::WITH,
        'contract.id IN (
               SELECT contract2.id
               FROM DataBundle:User user2
               INNER JOIN user2.contracts contract2
               INNER JOIN contract2.car car2
               WHERE user2.email = :email AND (
                    contract2.status = :status1 OR contract2.status = :status2
               ) AND car2.name = :name
        )'
    );
    $query->leftJoin(
        'contract.car',
        'car'
    );

    $query->where('user.email = :email');

Maybe will be useful for someone.

Upvotes: 0

Views: 434

Answers (2)

Cerad
Cerad

Reputation: 48893

You will end up with something like this:

SELECT user,contract,car 
FROM user AS user
LEFT JOIN user.contract AS contract
LEFT JOIN contract.car AS car
WHERE user.email = '[email protected]' AND car.name = 'audi';

Obviously you need to define your entities and set up the relations properly. I'd suggest following the example in the documentation and then working your way through a test case.

Please note that the use of abbreviations (t,c,u) for aliases has been declared a war crime by the United Nations and will be punished severally.

Upvotes: 0

Up_One
Up_One

Reputation: 5271

See article for multiple join example :

DQL JOIN Syntax:

[[LEFT | INNER] JOIN <component_reference1>] [ON | WITH] <join_condition1> [INDEXBY] <map_condition1>,
[[LEFT | INNER] JOIN <component_reference2>] [ON | WITH] <join_condition2> [INDEXBY] <map_condition2>,
...
[[LEFT | INNER] JOIN <component_referenceN>] [ON | WITH] <join_conditionN> [INDEXBY] <map_conditionN>

Upvotes: 1

Related Questions