On Jan 15, 4:25 pm, parkurm <[email protected]> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
