Many-to-Many Request

Question

I have a problem and I don’t know which solution is better. Ok, I have 2 tables: posts (id, title), posts_tags (post_id, tag_id). I have the following task: I need to select messages with tag identifiers, for example, 4, 10 and 11. Not really, a message can have any other tags at the same time. So how could I make this more optimized? Creating a temporary table in each query? Or maybe some kind of stored procedure? In the future, the user can request the script to select messages with any number of tags (it can be only one tag or 10 at a time), and I must be sure that the method I choose will be the best method for my problem. Sorry for my english, thanks for watching.

Answer 1

This solution assumes that (post_id, tag_id) in post_tags is applied as UNIQUE:

 SELECT id, title FROM posts
    INNER JOIN post_tag ON post_tag.post_id = posts.id
    WHERE tag_id IN (4, 6, 10)
    GROUP BY id, title
    HAVING COUNT(*) = 3

Although this is not a solution for all possible tag combinations, it is easy to create as dynamic SQL. To change for other tag sets, modify the IN () list to have all tags, and COUNT (*) = to check the number of tags specified. The advantage of this solution is that you should not add JOINs or even additional WHERE clauses when changing a query.

Answer 2

select id, title
from posts p, tags t
where p.id = t.post_id
and tag_id in ( 4,10,11 ) ;

?

Answer 3

?

select *
from posts
where post.post_id in
    (select post_id
    from post_tags
    where tag_id = 4
    and post_id in (select post_id
                    from post_tags
                    where tag_id = 10
                    and post_id in (select post_id
                                    from post_tags
                                    where tag_id = 11)))

Answer 4

, , .

, select t.name from tags t inner join post_tags pt where pt.post_id = [ID_of_tagged_post] order by t.name. , MD5 ( , , ).

, (, ) select from posts p where p.taghash = MD5([concatenated_tag_string]).

Answer 5

any (4, 10, 11):

select distinct id, title from posts  
where exists ( 
  select * from posts_tags  
  where  
    post_id = id and 
    tag_id in (4, 10, 11)) 

:

select distinct id, title from posts   
join posts_tags on post_id = id 
where tag_id in (4, 10, 11) 

( ).

all (4, 10, 11):

select distinct id, title from posts
where not exists ( 
  select * from posts_tags t1 
  where 
    t1.tag_id in (4, 10, 11) and
    not exists (
      select * from posts_tags as t2
      where 
        t1.tag_id = t2.tag_id and
        id = t2.post_id))

in - , ( ).

But this last request is not very fast, so instead you can use something like this:

 create temporary table target_tags (tag_id int);
 insert into target_tags values(4),(10),(11);
 select id, title from posts 
   join posts_tags on post_id = id 
   join target_tags on target_tags.tag_id = posts_tags.tag_id
   group by id, title 
   having count(*) = (select count(*) from target_tags);
 drop table target_tags;

The dynamic part, which changes dynamically, is now in the second expression (insert).