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.
--
Posted with http://DevLists.com. Sign up and save your mailbox.
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
<devlists-rubyonrails@devlists.com>
wrote:>
> 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.
> --
> Posted with http://DevLists.com. Sign up and save your mailbox.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060507/4fe1e1d9/attachment.html
On Sunday, May 07, 2006, at 10:49 AM, Paul Barry 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. -- Posted with http://DevLists.com. Sign up and save your mailbox.
On Sun, May 07, 2006 at 02:17:45PM -0000, BA Baracus wrote:> 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'';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) -- Loose bits sink chips.
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 <devlists-rubyonrails@devlists.com> wrote:> > On Sunday, May 07, 2006, at 10:49 AM, Paul Barry 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. > -- > Posted with http://DevLists.com. Sign up and save your mailbox. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060507/beaf219d/attachment.html
BA Baracus schrieb:> On Sunday, May 07, 2006, at 10:49 AM, Paul Barry 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.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 Fuchs 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''); >GROUP BY taggable_id >HAVING count(taggable_id) = 5Sven, 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. -- Posted with http://DevLists.com. Sign up and save your mailbox.