php – Fetching posts by multiple tags

Question:

Given: there are 3 tables in a mySQL type database (I will list only the fields relevant to the question):

  1. post (id)
  2. tag (id, text)
  3. tagpost (id, tagid, postid)

The tag table contains the description of the tag (word and its identifier). When adding a tag to a post, a record is placed in the tagpost table with the tag and post id. As many entries are added to the tagpost table as there have been tags added to that post.

Needed: The user in the interface specified a selection for two tags. You need to extract posts that are tagged with both tag1 and tag2 . It is not difficult to extract posts marked with only one specific tag ( inner join ), but how to do it nicely with several tags? Let not for 1 request, but in the end it will work quickly. And then only shit code comes to mind. ps. I put the label "php" in the question, because is coded in this language, all of a sudden you need specific functions from it.

Answer:

The simplest thing is to add tags two times, first one, then the second:

select p.* from post p 
    join tagpost tp1 on tp1.post=p.id join tag t1 on tp1.tag=t1.id 
    join tagpost tp2 on tp2.post=p.id join tag t2 on tp2.tag=t2.id 
    where t1.name='tag1' and t2.name='tag2';

But for an unknown number of tags, such a request without a quarter builder will be inconvenient and ugly to build. A more accurate option:

select p.* from tagpost tp join tag t on tag=t.id 
    join post p on post = p.id where name in ('tag1','tag2') 
    group by post having count(1) = 2;

but remember that group by queries are more difficult to optimize.

Scroll to Top
AllEscort