Does someone have a good idea / way on how to work with the following situation? :
Situation is: I have the tables 'releases' and 'ntags', related via 'releases_ntags' (containing 'release_id' and 'ntag_id')
Starting with a number of ntag id's I have a query that returns only releases that have all corresponding tags assigned. Thanks to stackoverflow this is working fine now! :) See this post
Problem
I need to limit the tags displayed in the tagcloud to those also appearing in the record-set. Eg without selecting any tags, the tagcloud displays the 30 most used tags in general. After applying say two tags, the cloud now should display the 30 most appearing tags as well, but only those appearing in the new result. (And there is a paging as well, so eg 20 of 532 results are displayed, the tagcloud has to show the tags from all the 523 releases)
Is there an elegant way to achieve this? I went that way:
- query 1: getting only the id's for the matching releases (eg 532 the results)
- query 2: getting the tag id's + names out of the id's from 'query 1'
- query 3: get releases with the id's from 'query 1', joining other needed tables, and applying sorting/limiting/paging (20) here
But somehow i have the feeling that this solution is rather 'lumberjack-ish'...
As it is a quite common requirement/situation I assume that some smarter people already made some toughts about it :)
Thx in advance for any tips!
Here the query to get the tag-filtered releases:
SELECT r.id, r.name
FROM releases r
JOIN ntags_releases rt ON rt.release_id = r.id
JOIN ntags nt ON nt.id = rt.ntag_id
WHERE nt.id in (198, 199)
AND nt.created > '2010-09-18 14:39:13'
GROUP BY
r.id, r.name
HAVING
COUNT(distinct nt.id) = 2