Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql many to many relationship query. How to get all tags of filtered posts?

I have gone through tons of questions about this issue here in Stackoverflow but I think this is different.

What I'm trying to do is give user ability to filter posts by tags so that one sees only the tags that are left for filtering. In other words if user selects tag "tag1", it show posts with that tag and also show other tags that post shares but hide tags that no visible post have after filtering.

I have tables posts, posts_tags and tags. Posts_tags have post_id and tag_id. I have managed to get post_ids available with specific tagset:

SELECT pt.post_id
FROM posts_tags pt
    INNER JOIN tags t
        ON pt.tag_id = t.id
WHERE t.name IN ('tag1', 'tag2', 'tag3')
GROUP BY pt.post_id
HAVING COUNT(DISTINCT t.id) = 3;

Let's say this query gives post_ids 1, 2, 3:

post 1 has tag1, tag2, tag3 and tag4
post 2 has tag1, tag2, tag3 and tag5
post 3 has tag1, tag2, tag3 and tag6

Now my problem is how to expand the query to return only tag4, tag5 and tag6 to user, because these tags are still available to filter posts further. How to achieve this?

Paying attention to performance would be also nice. I have 130000 posts, 6500 tags and bridge-table has 240000 rows.

edit: use scenario:

  1. User seaches tags with autocomplete and selects multiple tags.
  2. User retrieves posts based on submitted tags.
  3. User searches more tags and at that point:

    I don't want to give full list but only the ones

    a. That haven't been selected yet.

    b. Are used in posts that were retrieved at step 2.

Sample data


EDIT: FINAL QUERY BASED ON Mosty Mostacho's ANSWER:

SELECT DISTINCT pt2.tag_id, t2.name FROM    
(SELECT pt1.post_id
    FROM posts_tags pt1
    INNER JOIN tags t1
        ON pt1.tag_id = t1.id
    WHERE t1.name in ('tag1','tag2','tag3')
    GROUP BY pt1.post_id
    HAVING COUNT(DISTINCT t1.id) = 3) MatchingPosts
INNER JOIN posts_tags pt2 ON (MatchingPosts.post_id = pt2.post_id)
INNER JOIN tags t2 ON (pt2.tag_id = t2.id)
WHERE t2.name NOT IN ('tag1','tag2','tag3');
like image 398
Henri Avatar asked Nov 05 '22 06:11

Henri


1 Answers

Well, this is the best I can think of at 4:30 AM:

SELECT distinct tag_id FROM
    (SELECT pt1.post_id FROM pt1
    INNER JOIN tags t1 ON (pt1.tag_id = t1.id)
    WHERE t1.id IN (1, 2)
    GROUP BY pt1.post_id
    HAVING COUNT(DISTINCT t1.id) = 2) MatchingPosts
INNER JOIN pt2 ON (MatchingPosts.post_id = pt2.post_id)
WHERE (pt2.tag_id NOT IN (1, 2))

The (1, 2) are the tags you're looking for and the count, of course, will have to match the amount of tags you're using to filter.

Here is an example (Notice I slightly changed the data)

like image 53
Mosty Mostacho Avatar answered Nov 07 '22 21:11

Mosty Mostacho