Gents,
My heads been out of the sql game for too long (or it’s simply too
early).
I’m trying to extend the acts_as_taggable plugin (note: not gem) so that
you can specify a list of tags you’re interested in, and the plugin will
return only those items that are tagged with all these tags.
The plugin uses primarily two tables: tags, to keep all the various tag
names and ids, and taggings, which actually associates the tags table
with other items in your database.
So, tags looks like:
[id][name]
and taggings looks like:
[id][tag_id][taggable_id][taggable_type]
id and tag_id above serve to join the two tables…taggable_id is the id
of a tuple in another relation in the database, and type is the name of
said relation.
What I want to do is select * taggable_id’s from taggings that have
tag_ids matching a set of ids from the tags table.
So, my user might specify “hawaii, islands, girls, beach, beer” as a tag
query string, and I only want to pull taggable_id’s for those that are
tagged with each of these items…not, with either, as the current
plugin works.
The only way I’ve figured out to do this so far is the following query.
I know there has to be a better way (and more efficent for that
matter…this one will eat CPU cycles)…I’m just rusty and tired. Can
anyone offer any suggestions?
Here’s the query:
select distinct taggable_id, taggable_type from taggings
where taggable_id in (select taggable_id from tags,taggings where
tags.name=‘hawaii’ and taggings.tag_id = tags.id)
and taggable_id in (select taggable_id from tags,taggings where
tags.name=‘islands’ and taggings.tag_id = tags.id)
and taggable_id in (select taggable_id from tags,taggings where
tags.name=‘girls’ and taggings.tag_id = tags.id)
and taggable_id in (select taggable_id from tags,taggings where
tags.name=‘beach’ and taggings.tag_id = tags.id)
and taggable_id in (select taggable_id from tags,taggings where
tags.name=‘beer’ and taggings.tag_id = tags.id)
and taggable_type=‘MyType’;
Thanks!
B.A.
B.A. Baracus: I thought you weren’t crazy no more?
Murdock: Only on paper.