# Complex search query

**URL:** <https://www.ruby-forum.com/t/complex-search-query/136257>\
**Category:** Rails\
**Created:** [April 30, 2008, 5:59am UTC](https://www.ruby-forum.com/t/complex-search-query/136257 "2008-04-30T05:59:30Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [April 30, 2008, 5:59am UTC](https://www.ruby-forum.com/t/complex-search-query/136257/1 "2008-04-30T05:59:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [April 30, 2008, 5:16pm UTC](https://www.ruby-forum.com/t/complex-search-query/136257/2 "2008-04-30T17:16:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 1, 2008, 4:04am UTC](https://www.ruby-forum.com/t/complex-search-query/136257/3 "2008-05-01T04:04:26Z")

</div>

On Apr 30, 11:14 am, “Pardee, Roy” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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