# Challenging SQL for you

**URL:** <https://www.ruby-forum.com/t/challenging-sql-for-you/94568>\
**Category:** Rails\
**Created:** [May 7, 2006, 4:19pm UTC](https://www.ruby-forum.com/t/challenging-sql-for-you/94568 "2006-05-07T16:19:40Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![BA\_Baracus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BA\_Baracus](https://www.ruby-forum.com/u/BA_Baracus)\
**Post date:** [May 7, 2006, 4:19pm UTC](https://www.ruby-forum.com/t/challenging-sql-for-you/94568/1 "2006-05-07T16:19:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![BA\_Baracus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BA\_Baracus](https://www.ruby-forum.com/u/BA_Baracus)\
**Post date:** [May 7, 2006, 4:50pm UTC](https://www.ruby-forum.com/t/challenging-sql-for-you/94568/2 "2006-05-07T16:50:16Z")

</div>

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](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![BA\_Baracus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BA\_Baracus](https://www.ruby-forum.com/u/BA_Baracus)\
**Post date:** [May 7, 2006, 6:27pm UTC](https://www.ruby-forum.com/t/challenging-sql-for-you/94568/3 "2006-05-07T18:27:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![BA\_Baracus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BA\_Baracus](https://www.ruby-forum.com/u/BA_Baracus)\
**Post date:** [May 7, 2006, 6:46pm UTC](https://www.ruby-forum.com/t/challenging-sql-for-you/94568/4 "2006-05-07T18:46:03Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![BA\_Baracus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BA\_Baracus](https://www.ruby-forum.com/u/BA_Baracus)\
**Post date:** [May 7, 2006, 7:07pm UTC](https://www.ruby-forum.com/t/challenging-sql-for-you/94568/5 "2006-05-07T19:07:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![BA\_Baracus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BA\_Baracus](https://www.ruby-forum.com/u/BA_Baracus)\
**Post date:** [May 9, 2006, 12:26pm UTC](https://www.ruby-forum.com/t/challenging-sql-for-you/94568/6 "2006-05-09T12:26:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![BA\_Baracus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BA\_Baracus](https://www.ruby-forum.com/u/BA_Baracus)\
**Post date:** [May 7, 2006, 6:55pm UTC](https://www.ruby-forum.com/t/challenging-sql-for-you/94568/7 "2006-05-07T18:55:17Z")

</div>

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](mailto:removed_email_address@domain.invalid)