Select from ManyToMany only if all Ids apply

Question

Hi I'm trying to fiddle this into a doctrine2 statement, unfortunatly i have knot in my brain right now.

I have 3 tables

post

id | unrelated stuff

post_has_tag

post_id | tag_id
1         1
1         2
2         2

tag

 id | name
 1    smth
 2    smthelse

now i want to use some tag navigation, kinda like here on stackoverflow and this is where my problem applies. I want to select only the post_id from my m2n table if all ids apply. And I'm kinda dumb here since all my attemts either give me both entrys or none.

If you can give me the sql i'll be fine, if you can give me the dql it'll be amazing

E: clarification So to sum things up:

I use smth like $object->filterPostsByTag(array(1,2)); now i want only the posts where atleast the tags with id 1 AND 2 are assigned. My attempts with joins were resulting in either returning both posts or none.

Answer 1

If you want all ids (presumably "tag" ids) to apply, then use aggregation with a having clause:

select pht.post_id
from post_has_tag pht
group by pht.post_id
having count(distinct pht.tag_id) = (select count(distinct t.tag_id) from tag t)

EDIT: (After OP was revised)

If you only want two particular tags, you can use the same idea:

If you want all ids (presumably "tag" ids) to apply, then use aggregation with a having clause:

select pht.post_id
from post_has_tag pht
group by pht.post_id
having max(pht.tag_id = 1) > 0 and
       max(pht.tag_id = 2) > 0

This returns all posts that have both tags. If you want both tags and nothing else:

select pht.post_id
from post_has_tag pht
group by pht.post_id
having max(pht.tag_id = 1) > 0 and
       max((pht.tag_id <> 1) or (pht.tag_id <> 2)) = 0