Challenging SQL for you

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.

select distinct
taggable_id,
taggable_type
from
taggings tg,
tags t
where
t.id = tg.tag_id and
tg.taggable_type = ‘My Type’ and
t.name in (‘hawaii’, '‘islands’, ‘girls’, ‘beach’, ‘beer’);

On 7 May 2006 14:17:45 -0000, BA Baracus
[email protected]

On Sunday, May 07, 2006, at 10:49 AM, Paul B. wrote:

select distinct
taggable_id,
taggable_type
from
taggings tg,
tags t
where
t.id = tg.tag_id and
tg.taggable_type = ‘My Type’ and
t.name in (‘hawaii’, '‘islands’, ‘girls’, ‘beach’, ‘beer’);

Ah, but that only gives you OR functionality, in

t.name = ‘hawaii’ OR t.name = ‘islands’ or…(etc,etc)

I want AND functionality, so that only records with ALL tags are
returned.

Thanks!

B.A.

On Sun, May 07, 2006 at 02:17:45PM -0000, BA Baracus wrote:

                  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’;

Didn’t test it, but what about this:

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)
intersect
select distinct taggable_id, taggable_type from taggings
where taggable_id in (select taggable_id from tags,taggings where
tags.name=‘islands’ and taggings.tag_id =
tags.id)
intersect
select distinct taggable_id, taggable_type from taggings
where taggable_id in (select taggable_id from tags,taggings where
tags.name=‘girls’ and taggings.tag_id = tags.id)
intersect
select distinct taggable_id, taggable_type from taggings
where taggable_id in (select taggable_id from tags,taggings where
tags.name=‘beach’ and taggings.tag_id = tags.id)
intersect
select distinct taggable_id, taggable_type from taggings
where taggable_id in (select taggable_id from tags,taggings where
tags.name=‘beer’ and taggings.tag_id = tags.id)

BA Baracus schrieb:

t.name in (‘hawaii’, '‘islands’, ‘girls’, ‘beach’, ‘beer’);

Ah, but that only gives you OR functionality, in

t.name = ‘hawaii’ OR t.name = ‘islands’ or…(etc,etc)

I want AND functionality, so that only records with ALL tags are returned.

select distinct
taggable_id,
taggable_type
from
taggings tg,
tags t
where
t.id = tg.tag_id and
tg.taggable_type = ‘My Type’ and
t.name in (‘hawaii’, ‘islands’, ‘girls’, ‘beach’, ‘beer’);
GROUP BY taggable_id
HAVING count(taggable_id) = 5

I think that should do the trick.


Sven

On Sunday, May 07, 2006, at 7:05 PM, Sven F. wrote:

GROUP BY taggable_id
HAVING count(taggable_id) = 5

Sven, this worked, thanks! I had played with a similar idea, but wasn’t
sure if the HAVING clause would operate properly. It does, and nicely
(albeit somewhat expensive according to the query optimizer).

Thank you! I appreciate your help!

B.A.

ooh, that is tricky, I’m stumped, I’ll let you know if I think of
anything

On 7 May 2006 16:24:49 -0000, BA Baracus
[email protected]