Doctrine's DBAL query builder omitts some joins

PHP CODE:

    $xCodesQueryBuilder = $conn->createQueryBuilder();

    $xCodesQueryBuilder->select("l.id","mdsh.xcode","mdso.xcode")
            ->from("location_tree","l")
            ->join("l","location_tree_pos","p","l.id = p.tree_id")
            ->rightJoin("l","hotel","h","h.location_id = l.id")
            ->leftJoin("l","offer_location","ol","l.id=ol.location_id")
            ->leftJoin("ol","mds_offer","mdso","ol.offer_id = mdso.offer_id")
            ->leftJoin("h","mds_ho开发者_如何学Ctel","mdsh","h.id = mdsh.hotel_id")
            ->where("p.parent_id IN (:ids)")
            ->andWhere("(mdso.xcode IS NOT NULL OR mdsh.xcode IS NOT NULL)");

    var_dump($xCodesQueryBuilder->getSQL());exit;

RESULT:

SELECT l.id, mdsh.xcode, mdso.xcode
FROM location_tree l 
INNER JOIN location_tree_pos p ON l.id = p.tree_id 
RIGHT JOIN hotel h ON h.location_id = l.id 
LEFT JOIN offer_location ol ON l.id=ol.location_id 
WHERE (p.parent_id IN (:ids)) 
AND ((mdso.xcode IS NOT NULL OR mdsh.xcode IS NOT NULL))

Any ideas why 2 last joins are omitted?

I just got this working for me. Had to modify the function getSQLForSelect() in QueryBuilder.php.

I've opened up a ticket and submitted a pull request to DBAL, but in the meanwhile, feel free to use my patched copy.

UPDATE:

Just realized that an alternate way to fix this issue is to always use the FROM table alias as the first argument ($fromAlias) in any of the join() methods.

In your case, you'd change your code to look like this:

$xCodesQueryBuilder = $conn->createQueryBuilder();

$xCodesQueryBuilder->select("l.id","mdsh.xcode","mdso.xcode")
        ->from("location_tree","l")
        ->join("l","location_tree_pos","p","l.id = p.tree_id")
        ->rightJoin("l","hotel","h","h.location_id = l.id")
        ->leftJoin("l","offer_location","ol","l.id=ol.location_id")
        ->leftJoin("l","mds_offer","mdso","ol.offer_id = mdso.offer_id")
        ->leftJoin("l","mds_hotel","mdsh","h.id = mdsh.hotel_id")
        ->where("p.parent_id IN (:ids)")
        ->andWhere("(mdso.xcode IS NOT NULL OR mdsh.xcode IS NOT NULL)");

var_dump($xCodesQueryBuilder->getSQL());exit;

I think Doctrine 2.3.1 is supporting this.

A way around could be converting joins to from and where, use group by if required. Got stuck for 2-3 hours, finally used this workaround because even after upgrading doesn't helped me (trying to figure out why)