Hi all,
This is my first question, and hopefully someone can help.
Overview:
models:.......................................................................
1. class Post < ActiveRecord::Base
belongs_to :user
has_and_belongs_to_many :categories
acts_as_mappable :distance_field_name => :distance
2. class Category < ActiveRecord::Base
has_and_belongs_to_many :posts
3. and, of course, the join table: categories_posts
the
search:.......................................................................
The following find works and is paginated via will_paginate:
results = Post.paginate(:page => page,
:origin => user,
:conditions => [''removed_at IS NULL AND zip = ?
AND intention = ? AND title like ?'',
zip, search_type,
"%#{search}%"],
:order =>
''date_format(updated_at,"%Y%m%d") DESC,
distance ASC''
) if search.blank?
details:
search --- the query enter by the user
search_type --- selected by the user from [''offered'',
''wanted'']
removed_at --- date/time post was made invisible
zip -- posts.zip
intention -- posts.intention is either offered or wanted matches
search_type
:origin => user --- references the current user''s latitude/longitude
for acts_as_mappable,
which calculates a distance column (i.e.
distance of post from current user)
problem:
........................................................................
The search needs to include only rows that also match one or more
categories ???
I have tried :join and :include(this can''t have conditions), but I
can''t seem to make it work properly.
note: the results can be read-only as it''s just a search
If I used find_by_sql I think I would lose acts_as_mappable(GeoKit)
and will_paginate functionality.
Any help or suggestions would be greatly appreciated.
thanks,
chris
--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
How confident are you that your plugin won''t work if you do a
find_by_sql? That sounds wrong to me... I would think that as long as
you''re pulling all the columns in your table, AR should be able to
create instances of your model regardless of how they were found.
But no matter--you can use :conditions to refer to a second table if you
want to. So something like this should work. Imagine you have an array
of ''sought categories''.
cat_list = "#{sought_categories.join(''",
"'')}"
[''removed_at IS NULL AND zip = ? AND intention = ? AND title like ? AND
category_id in (select id from categories where name in (?))'', zip,
search_type,"%#{search}%", cat_list]
HTH,
-Roy
-----Original Message-----
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org] On Behalf Of
smitty
Sent: Tuesday, April 29, 2008 8:59 PM
To: Ruby on Rails: Talk
Subject: [Rails] complex search query
Hi all,
This is my first question, and hopefully someone can help.
Overview:
models:.................................................................
......
1. class Post < ActiveRecord::Base
belongs_to :user
has_and_belongs_to_many :categories
acts_as_mappable :distance_field_name => :distance
2. class Category < ActiveRecord::Base
has_and_belongs_to_many :posts
3. and, of course, the join table: categories_posts
the
search:.................................................................
......
The following find works and is paginated via will_paginate:
results = Post.paginate(:page => page,
:origin => user,
:conditions => [''removed_at IS NULL AND zip = ?
AND intention = ? AND title like ?'',
zip, search_type,
"%#{search}%"],
:order =>
''date_format(updated_at,"%Y%m%d") DESC,
distance ASC''
) if search.blank?
details:
search --- the query enter by the user
search_type --- selected by the user from [''offered'',
''wanted'']
removed_at --- date/time post was made invisible
zip -- posts.zip
intention -- posts.intention is either offered or wanted matches
search_type
:origin => user --- references the current user''s latitude/longitude
for acts_as_mappable,
which calculates a distance column (i.e.
distance of post from current user)
problem:
........................................................................
The search needs to include only rows that also match one or more
categories ???
I have tried :join and :include(this can''t have conditions), but I
can''t seem to make it work properly.
note: the results can be read-only as it''s just a search
If I used find_by_sql I think I would lose acts_as_mappable(GeoKit)
and will_paginate functionality.
Any help or suggestions would be greatly appreciated.
thanks,
chris
--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On Apr 30, 11:14 am, "Pardee, Roy" <parde...-go57ItdSaco@public.gmane.org> wrote:> How confident are you that your plugin won''t work if you do a > find_by_sql? That sounds wrong to me... I would think that as long as > you''re pulling all the columns in your table, AR should be able to > create instances of your model regardless of how they were found. > > But no matter--you can use :conditions to refer to a second table if you > want to. So something like this should work. Imagine you have an array > of ''sought categories''. > > cat_list = "#{sought_categories.join(''", "'')}" > > [''removed_at IS NULL AND zip = ? AND intention = ? AND title like ? AND > category_id in (select id from categories where name in (?))'', zip, > search_type,"%#{search}%", cat_list] > > HTH, > > -RoyYeah, I''m not very confident about plugins, but I was really trying to avoid use all SQL. However, your answer did put me on the right trail: cat_id_list = "#{sought_category_ids.join(''", "'')}" Post.paginate(:page => page, :origin => user, :conditions => [''removed_at IS NULL AND zip = ? AND intention = ? AND title like ? AND id in (select post_id from categories_posts where category_id in (?))'', zip, search_type, "%#{search}%", cat_id_list], :order => ''date_format(updated_at,"%Y%m%d") DESC, distance ASC'') -- had to use categories_posts because the join table links the categories to the posts. thank you for taking the time to help, chris now on to the next problem: updating/editing uploaded images via attachment_fu ... sometimes lost in a sea of plugins --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---