Use a Doctrine association table in a DQL query or equivalent

Question

(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.

I think I know what you mean... but can you post your code so we can give you a more explicit example based on your entities? — Darragh Enright

Darragh Enright Darragh Enright · Accepted Answer · 2011-11-24T02:18:30

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 :)

Use a Doctrine association table in a DQL query or equivalent

1 Answers