I’ve been attempting to add some filters to my AJAX resource list. I’ve made an attempt an added them, but they don’t work the way I would like. So lets say your on the library list, a filter that would make a lot of sense, would be to say show me every library thats written in PHP and Provides AJAX. Now if you goto that link you’ll get every library thats written in PHP or Provides AJAX. Now I think the UI worked out pretty well but the or thing stinks, and that all comes down to database queries.
So the database is 3 tables:
Bookmarks bookmark_id user_id url ... bookmark_tag bookmark_id tag_id tag tag_id user_id tag
So I end up with a that is basically:
select b.* from bookmark b inner join bookmark_tag using(bookmark_id) inner join tag using(tag_id) where tag.tag = 'library' and b.bookmark_id in( select bookmark_id from bookmark_tag inner join tag using(tag_id) where tag.tag in ('language:php','provides:ajax') )
This works great but I export for multiple filter tags means or, since they are in an in list, or subquery for filter which seems like it would be horrible for performance.
Am I just missing a simple solution to the problem or is a subquery for each filter the only option?
Btw: I’m using mysql 4.1.something