Databases
StackOverflow2013
Postsdbo
POMysql many to many relationship query. How to get all tags of filtered posts?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql many to many relationship query. How to get all tags of filtered posts?
    text
    copied!<p>I have gone through tons of questions about this issue here in Stackoverflow but I think this is different.</p> <p>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.</p> <p>I have tables <strong>posts</strong>, <strong>posts_tags</strong> and <strong>tags</strong>. Posts_tags have post_id and tag_id. I have managed to get post_ids available with specific tagset:</p> <pre><code>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; </code></pre> <p>Let's say this query gives post_ids 1, 2, 3:</p> <pre><code>post 1 has tag1, tag2, tag3 and tag4 post 2 has tag1, tag2, tag3 and tag5 post 3 has tag1, tag2, tag3 and tag6 </code></pre> <p>Now my problem is <strong>how to expand the query to return only tag4, tag5 and tag6</strong> to user, because these tags are still available to filter posts further. How to achieve this?</p> <p>Paying attention to performance would be also nice. I have 130000 posts, 6500 tags and bridge-table has 240000 rows.</p> <p>edit: use scenario:</p> <ol> <li>User seaches tags with autocomplete and selects multiple tags.</li> <li>User retrieves posts based on submitted tags.</li> <li><p>User searches more tags and at that point:</p> <p>I don't want to give full list but only the ones</p> <p>a. That haven't been selected yet.</p> <p>b. Are used in posts that were retrieved at step 2.</p></li> </ol> <p><img src="https://i.stack.imgur.com/ov44V.jpg" alt="Sample data"></p> <hr> <p><strong>EDIT: FINAL QUERY BASED ON Mosty Mostacho's ANSWER:</strong></p> <pre><code>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'); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload