Forum: Ruby on Rails Challenging SQL for you...

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
BA Baracus (Guest)
on 2006-05-07 18:19
(Received via mailing list)
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.
Paul B. (Guest)
on 2006-05-07 18:50
(Received via mailing list)
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
<removed_email_address@domain.invalid>
BA Baracus (Guest)
on 2006-05-07 20:27
(Received via mailing list)
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.
Andrea C. (Guest)
on 2006-05-07 20:46
(Received via mailing list)
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)
Paul B. (Guest)
on 2006-05-07 20:55
(Received via mailing list)
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
<removed_email_address@domain.invalid>
Sven F. (Guest)
on 2006-05-07 21:07
(Received via mailing list)
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
BA Baracus (Guest)
on 2006-05-09 14:26
(Received via mailing list)
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.
This topic is locked and can not be replied to.