# Chaining queries in ActiveRecord

**URL:** <https://www.ruby-forum.com/t/chaining-queries-in-activerecord/181679>\
**Category:** Rails\
**Created:** [January 15, 2010, 10:27pm UTC](https://www.ruby-forum.com/t/chaining-queries-in-activerecord/181679 "2010-01-15T22:27:43Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![parkurm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/90db22/32.png) [@parkurm](https://www.ruby-forum.com/u/parkurm)\
**Post date:** [January 15, 2010, 10:27pm UTC](https://www.ruby-forum.com/t/chaining-queries-in-activerecord/181679/1 "2010-01-15T22:27:43Z")

</div>

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:

```auto
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
```

But when I chain the query like this:

```auto
Pic.dog.cat
```

The SQL query that I’m actually getting is the merged conditions:

```auto
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'))
```

And the above query won’t give me any result, since no joined table  
entry will have both ‘dog’ and ‘cat’ at the same time. The reason for  
this is that using named\_scope, query is done only when all the query  
conditions are merged together. I tried scope\_out, searchlogic plugin,  
but they all share the same basic concept of using named\_scope, so the  
result is the same.

So here’s my question: is there any way to query step by step? If so,  
then Pic.dog should be able to return all pics tagged with “dog”, and  
then when I continue to query on the result, say, Pic.dog.cat, I can  
have all pics tagged with “cat” based on the previous result.

---

<div class="post-metadata">

**Author:** ![parkurm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/90db22/32.png) [@parkurm](https://www.ruby-forum.com/u/parkurm)\
**Post date:** [January 16, 2010, 8:36pm UTC](https://www.ruby-forum.com/t/chaining-queries-in-activerecord/181679/2 "2010-01-16T20:36:49Z")

</div>

On Jan 15, 4:25Â pm, parkurm [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> method to generate joined table search conditions, not important  
> “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 J.