complex search query

rubyonrails-talk
1

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

2

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

3

Yeah, 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