Hi all,
Stuck at this problem for several days. Tried to use named_scope,
scope_out, and plugin such as searchlogic, but no result.
The problem is as follows: I have a pic table and tag table, and a
join table called pic_tag (with only pic_id and tag_id) so that I can
associate N pics to M tags. This is the tutorial way to set up a many-
to-many association. I''m trying to implement a simple search function,
so that I can search for a picture with several given tags.
Now say I want to search for pics that are tagged with "dog" and
"cat". The simplest way to do this is to use named_scopes, for
example:
[code]
class Pic < ActiveRecord::Base
has_many :pic_tags
has_many :tags, :through => :pic_tags
named_scope :dog, options_for_tag(“dog") # options_for_tag(tag) is a
method to generate joined table search conditions, not important
named_scope :cat, options_for_tag("cat")
end
[/code]
But when I chain the query like this:
[code]Pic.dog.cat[/code]
The SQL query that I''m actually getting is the merged conditions:
[code]
SELECT "pics".* FROM "pics" INNER JOIN "pic_tags"
ON ("pics"."id" "pic_tags"."pic_id")
INNER JOIN "tags" ON ("tags"."id"
"pic_tags"."tag_id") WHERE ((tags.name LIKE
''dog'') AND (tags.name LIKE
''cat''))
[/code]
And the above query won''t give me any result, since no joined table
entry will have both ''dog'' and ''cat'' at the
same time. The reason for
this is that using named_scope, query is done only when all the query
conditions are merged together. I tried scope_out, searchlogic plugin,
but they all share the same basic concept of using named_scope, so the
result is the same.
So here''s my question: is there any way to query step by step? If so,
then Pic.dog should be able to return all pics tagged with "dog", and
then when I continue to query on the result, say, Pic.dog.cat, I can
have all pics tagged with "cat" based on the previous result.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jan 15, 4:25 pm, parkurm <park...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi all, > > Stuck at this problem for several days. Tried to use named_scope, > scope_out, and plugin such as searchlogic, but no result. > > The problem is as follows: I have a pic table and tag table, and a > join table called pic_tag (with only pic_id and tag_id) so that I can > associate N pics to M tags. This is the tutorial way to set up a many- > to-many association. I''m trying to implement a simple search function, > so that I can search for a picture with several given tags. > > Now say I want to search for pics that are tagged with "dog" and > "cat". The simplest way to do this is to use named_scopes, for > example: > > [code] > class Pic < ActiveRecord::Base > has_many :pic_tags > has_many :tags, :through => :pic_tags > > named_scope :dog, options_for_tag(“dog") # options_for_tag(tag) is a > method to generate joined table search conditions, not important > named_scope :cat, options_for_tag("cat") > end > [/code] > > But when I chain the query like this: > [code]Pic.dog.cat[/code] > The SQL query that I''m actually getting is the merged conditions: > [code] > SELECT "pics".* FROM "pics" INNER JOIN "pic_tags" ON ("pics"."id" > "pic_tags"."pic_id") INNER JOIN "tags" ON ("tags"."id" > "pic_tags"."tag_id") WHERE ((tags.name LIKE ''dog'') AND (tags.name LIKE > ''cat'')) > [/code]The classical way to handle this is to alias the tables involved so they don''t get fused together by the SQL generation code. For instance: named_scope :dog, :joins => ''INNER JOIN pic_tags AS dog_pic_tags ON (pics.id dog_pic_tags.pic_id) INNER JOIN tags AS dog_tags ON (dog_tags.id dog_pic_tags.tag_id)'', :conditions => [''dog_tags.name LIKE ?'', ''dog''] This gives each scope its own set of distinct joins, so it should avoid the previous problem. It''s straightforward to extend this to a named_scope named tagged_with: named_scope :tagged_with, lambda { |n| { :joins => "INNER JOIN pic_tags AS #{n}_pic_tags ON (pics.id = #{n}_pic_tags.pic_id) INNER JOIN tags AS #{n}_tags ON (#{n}_tags.id = #{n} _pic_tags.tag_id)", :conditions => ["#{n}_tags.name LIKE ?'',n] } } Then you can do things like: Pic.tagged_with(''dog'').tagged_with(''cat'') etc. Note that you''ll want to sanitize any user inputs you''re passing to tagged_with. --Matt Jones -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.