Created
November 8, 2018 11:01
-
-
Save naz/05aea3902981516c5ed6074b5664197f to your computer and use it in GitHub Desktop.
Describes types of filtering that could be applied to related resources in M:M relations
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--tag:-two | |
select p.title, html | |
from posts as p | |
where p.id | |
NOT in ( | |
select post_id | |
from posts_tags as pt | |
inner join tags as t | |
on t.id = pt.tag_id | |
where t.slug IN ('two') | |
); | |
| |
--tag:one,two | |
select p.title, html | |
from posts as p | |
where p.id | |
in ( | |
select post_id | |
from posts_tags as pt | |
inner join tags as t | |
on t.id = pt.tag_id | |
where t.slug IN ('one', 'two') | |
); | |
| |
--tag:-two | |
select p.title, html | |
from posts as p | |
where p.id | |
NOT in ( | |
select post_id | |
from posts_tags as pt | |
inner join tags as t | |
on t.id = pt.tag_id | |
where t.slug IN ('two') | |
); | |
| |
--tag:-one,-two | |
select p.title, html | |
from posts as p | |
where p.id | |
NOT in ( | |
select post_id | |
from posts_tags as pt | |
inner join tags as t | |
on t.id = pt.tag_id | |
where t.slug IN ('one', 'two') | |
); | |
| |
--tag: two+thee | |
select p.title, html | |
from posts as p | |
where p.id | |
in ( | |
select pt.post_id | |
from posts_tags as pt | |
inner join tags as t | |
on t.id = pt.tag_id | |
where t.slug IN ('two', 'three') | |
group by post_id | |
having count('post_id') = 2 --this is imitating AND for IN, have to have group by | |
); | |
|
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment