Use a Doctrine association table in a DQL query or equivalent

(Probably the solution for my problem is simple, but I've been searching for over an hour and I still haven't found anything.)

I have two entities, X and Y, with a bidirectional relationship between them. For an X object, I would do something like anXObject->getY() to get all the Y objects associated with anXObject.

How can I only get a (possibly sorted) subset of the objects returned by the previous statement (probably using a DQL statement or some other Doctrine-specific mechanism)?

I could do it with SQL, using the X_Y association table created by Doctrine, but I'm curious if it can be done only with DQL. (From what I understand, that association table can't be accessed from DQL.)

Update:

Here are my entities (I have stripped unessential stuff for brevity): http://paste.pocoo.org/show/512177/

To clarify, what I want to do is get all the Serial objects that have a certain Tag.

Solution

You are using yml - I'm familiar to annotations. I imagine it's correct though.

First of all, this is worth reading:

http://www.doctrine-project.org/docs/orm/2.1/en/reference/association-mapping.html#picking-owning-and-inverse-side

You need to explicitly set the relationships between the two entities, this is something I have forgotten to do a couple of times.

Assuming that the relationships are all made correctly and the fks are written to the join table, you should be able to perform some additional stuff with your DQL:

$query = $em->createQuery('
    SELECT s, t 
    FROM FooBarBundle:Serial s 
    JOIN s.tags t 
    WHERE t.name = "baz" 
    ORDER BY t.id ASC
');

Note that the alias for tags is in the field list; I believe this is called a fetch join?

Anyway, in my experience the important thing is to ensure that the relationships are being persisted to the join table.

Hope this helps :)