Ruby Forum Ruby on 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

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

On Apr 30, 11:14 am, "Pardee, Roy" <parde...@ghc.org> wrote:
>
> ['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

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