Doctrine DQL JOIN

Question

I have the following entities mapped with Doctrine 2:

class Zone
{
    /**
     * @ManyToOne(targetEntity="Zone", inversedBy="children")
     * @var Zone
     */
    protected $parent;

    /**
     * @OneToMany(targetEntity="Zone", mappedBy="parent")
     * @var Zone[]
     */
    protected $children;

    /**
     * @ManyToMany(targetEntity="Zone")
     * @var Zone[]
     */
    protected $descendants;
}

class Restaurant
{
    /**
     * @ManyToOne(targetEntity="Zone")
     * @var Zone
     */
    protected $zone;
}

Basically, a Zone has a parent, and therefore children. Because children may have children themselves, each Zone keeps a list of all its descendants as well.

Each Restaurant is assigned a Zone.

What I want to do, is to perform a DQL JOIN to return all Restaurants in a specific Zone (including all of its descendants).

If I had to do this in plain SQL, I would write:

SELECT r.* from Zone z
JOIN ZoneDescendant d ON d.zoneId = z.id
JOIN Restaurant r ON r.zoneId = d.descendantId
WHERE z.id = ?;

Is it possible to do this with Doctrine DQL, without adding a $restaurants property on the Zone, and having to complexify the domain model uselessly?

Answer 1

Ok, I finally found a way to do it with JOINs only (significantly faster on MySQL):

SELECT r
FROM Restaurant r,
     Zone z
JOIN z.descendants d
WHERE r.zone = d
AND z = ?1;

Answer 2

The only way I can think of doing this in a single DQL query is to use a subquery:

SELECT r FROM Restaurant r WHERE r.zone IN (SELECT zc.id FROM r.zone z JOIN z.children zc WHERE z.id = :zoneId) OR r.zone = :zoneId