Databases
StackOverflow2013
Postsdbo
POHow to query on related entities but return root entities?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to query on related entities but return root entities?
    text
    copied!<p>This is a hypothetical question, because I'm trying to get my head around Doctrine ORM and having a hard time replicating what I'd do in plain SQL.</p> <p>Suppose I have a simple ManytoMany relationship between tags and posts. They would map so that <code>Post::tags</code> is the owning side and <code>Tag::posts</code> is the reverse mapping.</p> <p>I understand that with Doctrine's DQL I can select posts containing their tags, or tags referencing their posts with the following 2 queries</p> <p>(1) <code>SELECT p, t FROM MyBundle:Post p JOIN p.tags t WHERE p.id = :id</code></p> <p>(2) <code>SELECT t, p FROM MyBundle:Tag t JOIN t.posts p WHERE t.id = :id</code></p> <p>But when I want to fetch posts by a number of tags, I have to choose between these: </p> <p>(3) <code>SELECT p, t FROM MyBundle:Post p JOIN p.tags t WHERE t.value IN ('foo','bar')</code></p> <p>(4) <code>SELECT t, p FROM MyBundle:Tag t JOIN t.posts p WHERE t.value IN ('foo','bar')</code></p> <p>Both of these seem wrong.</p> <p>With (3) I imagine the DB would scan the whole posts table before reducing the set to those tagged</p> <p>With (4) I get a collection of tag objects back which is the inverse of what I'm after.</p> <p>I tried the following, because logically it seemed to mirror what I'd do in SQL:</p> <pre><code>SELECT p, pt FROM MyBundle:Tag t JOIN t.posts p JOIN p.tags pt WHERE t.value IN ('foo','bar') GROUP BY p.id </code></pre> <p>It doesn't work because Doctrine insists I select the root entity</p> <p><strong>What's the best way to select on tags but get back unique posts as full objects?</strong></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload