# Problem with habtm conditions in pagination

**URL:** <https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476>\
**Category:** Rails\
**Created:** [March 31, 2006, 3:52pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476 "2006-03-31T15:52:19Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![juli](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@juli](https://www.ruby-forum.com/u/juli)\
**Post date:** [March 31, 2006, 3:52pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476/1 "2006-03-31T15:52:19Z")

</div>

Hello list,

I’ve had this problem for two days now. Perhaps someone here would  
care to comment on it. I’d be REALLY appreciative if that’d be the case.

Basically, it’s outlined at this paste: [http://rafb.net/paste/results/](http://rafb.net/paste/results/)  
kXQSHZ83.html

But I’ll paste it here, too.

The main problem is in the customers/list action. The problem is with  
this line:  
@customer\_pages, @customers = paginate :customers, {:per\_page =\>  
10, :include =\> ‘tags’, :conditions=\>‘tag\_id = 3’}

It’s generating the exception below. Any help would be INCREDIBLY  
appreciated. Please b/cc me in on any replies.

Kind Regards,  
Julian.

#The problem is in the customer-list action.  
#… it yields the error below the source code that follows:

# source code follows:

class CreateCustomers \< ActiveRecord::Migration  
def self.up  
create\_table :customers do |t|  
# t.column :name, :string  
end  
end

def self.down  
drop\_table :customers  
end  
end

class CreateTags \< ActiveRecord::Migration  
def self.up  
create\_table :tags do |t|  
# t.column :name, :string  
end  
end

def self.down  
drop\_table :tags  
end  
end

class Customer \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :tags  
end

class Tag \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :customers  
end

class CustomersController \< ApplicationController  
def index  
list  
render :action =\> ‘list’  
end

# GETs should be safe (see [Index of /2001/tag/doc](http://www.w3.org/2001/tag/doc/)

whenToUseGet.html)  
verify :method =\> :post, :only =\> [:destroy, :create, :update],  
:redirect\_to =\> { :action =\> :list }

def list  
@customer\_pages, @customers = paginate :customers, {:per\_page =\>  
10, :include =\> ‘tags’, :conditions=\>‘tag\_id = 3’}  
end

def show  
@customer = Customer.find(params[:id])  
end

def new  
@customer = Customer.new  
end

def create  
@customer = Customer.new(params[:customer])  
if @customer.save  
flash[:notice] = ‘Customer was successfully created.’  
redirect\_to :action =\> ‘list’  
else  
render :action =\> ‘new’  
end  
end

def edit  
@customer = Customer.find(params[:id])  
end

def update  
@customer = Customer.find(params[:id])  
if @customer.update\_attributes(params[:customer])  
flash[:notice] = ‘Customer was successfully updated.’  
redirect\_to :action =\> ‘show’, :id =\> @customer  
else  
render :action =\> ‘edit’  
end  
end

def destroy  
Customer.find(params[:id]).destroy  
redirect\_to :action =\> ‘list’  
end  
end

# exception that is generated when trying to access list page:

Processing CustomersController#list (for 127.0.0.1 at 2006-04-01  
00:40:10) [GET]  
Session ID: ee0f5f8afb0b2740be5f9463b8962e0b  
Parameters: {“action”=\>“list”, “controller”=\>“customers”}  
Customer Count (0.000700) SELECT COUNT(DISTINCT customers.id)  
FROM customers LEFT OUTER JOIN customers\_tags ON  
customers\_tags.customer\_id = customers.id LEFT OUTER JOIN tags ON  
tags.id = customers\_tags.tag\_id WHERE (tag\_id = 3)  
Customer Columns (0.000248) SHOW FIELDS FROM customers  
Tag Columns (0.000186) SHOW FIELDS FROM tags  
Customer Load IDs For Limited Eager Loading (0.000000)  
Mysql::Error: Unknown column ‘tag\_id’ in ‘where clause’: SELECT id  
FROM customers WHERE (tag\_id = 3) LIMIT 0, 10

ActiveRecord::StatementInvalid (Mysql::Error: Unknown column ‘tag\_id’  
in ‘where clause’: SELECT id FROM customers WHERE (tag\_id = 3) LIMIT  
0, 10):  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/  
active\_record/connection\_adapters/abstract\_adapter.rb:120:in `log' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/ active_record/connection_adapters/mysql_adapter.rb:185:in `execute’  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/  
active\_record/connection\_adapters/mysql\_adapter.rb:337:in `select' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/ active_record/connection_adapters/mysql_adapter.rb:176:in `select\_all’  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/  
active\_record/connection\_adapters/abstract/database\_statements.rb:  
23:in `select_values' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/ active_record/associations.rb:1147:in `select\_limited\_ids\_list’  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/  
active\_record/associations.rb:1141:in `add_limited_ids_condition!' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/ active_record/associations.rb:1131:in `construct\_finder\_sql\_with\_included\_associations’  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/  
active\_record/associations.rb:1094:in `select_all_rows' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/ active_record/associations.rb:960:in `find\_with\_associations’  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/  
active\_record/base.rb:923:in `find_every' /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.14.0/lib/ active_record/base.rb:381:in `find’  
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/  
action\_controller/pagination.rb:182:in `find_collection_for_pagination' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/ action_controller/pagination.rb:198:in `paginator\_and\_collection\_for’  
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/  
action\_controller/pagination.rb:129:in `paginate' /app/controllers/customers_controller.rb:12:in `list’  
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/  
action\_controller/base.rb:908:in `perform_action_without_filters' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/ action_controller/filters.rb:355:in `perform\_action\_without\_benchmark’  
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/  
action\_controller/benchmarking.rb:69:in `perform_action_without_rescue' /usr/local/lib/ruby/1.8/benchmark.rb:293:in `measure’  
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/  
action\_controller/benchmarking.rb:69:in `perform_action_without_rescue' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/ action_controller/rescue.rb:82:in `perform\_action’  
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/  
action\_controller/base.rb:379:in `process_without_filters' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/ action_controller/filters.rb:364:in `process\_without\_session\_management\_support’  
/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/  
action\_controller/session\_management.rb:117:in `process' /usr/local/lib/ruby/gems/1.8/gems/rails-1.1.0/lib/dispatcher.rb: 38:in `dispatch’  
/usr/local/lib/ruby/gems/1.8/gems/rails-1.1.0/lib/  
fcgi\_handler.rb:150:in `process_request' /usr/local/lib/ruby/gems/1.8/gems/rails-1.1.0/lib/ fcgi_handler.rb:54:in `process!’  
/usr/local/lib/ruby/site\_ruby/1.8/fcgi.rb:600:in `each_cgi' /usr/local/lib/ruby/site_ruby/1.8/fcgi.rb:597:in `each\_cgi’  
/usr/local/lib/ruby/gems/1.8/gems/rails-1.1.0/lib/  
fcgi\_handler.rb:53:in `process!' /usr/local/lib/ruby/gems/1.8/gems/rails-1.1.0/lib/ fcgi_handler.rb:23:in `process!’  
/Users/julian/rails/test/public/dispatch.fcgi:24

Rendering /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.0/lib/  
action\_controller/templates/rescues/layout.rhtml (500 Internal Error)

---

<div class="post-metadata">

**Author:** ![juli](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@juli](https://www.ruby-forum.com/u/juli)\
**Post date:** [March 31, 2006, 3:58pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476/2 "2006-03-31T15:58:35Z")

</div>

I guess my questoin to you would be is there such a column called  
‘tag\_id’  
in you customers table? If not then this is where the issue lies as the  
mysql errors states that it cannot find the field in when the query is  
ran.

Thanks

Jason L. West, Sr.

---

<div class="post-metadata">

**Author:** ![juli](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@juli](https://www.ruby-forum.com/u/juli)\
**Post date:** [March 31, 2006, 4:44pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476/3 "2006-03-31T16:44:41Z")

</div>

Jason,

Did you notice that there is an :include =\> ‘tags’ section in the  
hash as passed in to the pagination request?

Oh… it should be tags.id = 2 , shouldn’t it?

ARGH…

Julian.

---

<div class="post-metadata">

**Author:** ![juli](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@juli](https://www.ruby-forum.com/u/juli)\
**Post date:** [March 31, 2006, 5:15pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476/4 "2006-03-31T17:15:15Z")

</div>

Hmmmm

My first thought was to look at the SQL statement that is being passed  
to  
MySQL and the error returned.

'ActiveRecord::StatementInvalid (Mysql::Error: Unknown column ‘tag\_id’  
in ‘where clause’: SELECT id FROM customers WHERE (tag\_id = 3) ’

This is why I was asking about the column in your customers table. I am  
fairly new to the RoR architecture and development but I have had many  
years  
with MySQL and this seems to be an easy debug if looked into in a  
simplistic  
way.

In the end this is not working because of a miss reference to a column:

def list  
@customer\_pages, @customers = paginate :customers, {:per\_page =\>  
10, :include =\> ‘tags’, :conditions=\>‘tag\_id = 3’}  
end

Hope this helps. 🙂

Jason

---

<div class="post-metadata">

**Author:** ![juli](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@juli](https://www.ruby-forum.com/u/juli)\
**Post date:** [March 31, 2006, 5:42pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476/5 "2006-03-31T17:42:55Z")

</div>

Hmmm it seems incredibly slow tho… ☹

---

<div class="post-metadata">

**Author:** ![juli](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@juli](https://www.ruby-forum.com/u/juli)\
**Post date:** [March 31, 2006, 6:04pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476/6 "2006-03-31T18:04:09Z")

</div>

Okay, further down the tree of this, I got around the speed issue by  
changing this:

```
     options_hash[:conditions] = "tags.id = " + @params['tag_limit']

```

into this:

```
     options_hash[:conditions] = "customers_tags.tag_id = " +

```

@params[‘tag\_limit’]

Now it’s DAMN fast. But before it just wasn’t finishing. (scratching  
head).

Julian.

---

<div class="post-metadata">

**Author:** ![juli](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@juli](https://www.ruby-forum.com/u/juli)\
**Post date:** [March 31, 2006, 5:45pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476/7 "2006-03-31T17:45:59Z")

</div>

LOL!

---

<div class="post-metadata">

**Author:** ![juli](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/dfb087/32.png) [@juli](https://www.ruby-forum.com/u/juli)\
**Post date:** [March 31, 2006, 6:25pm UTC](https://www.ruby-forum.com/t/problem-with-habtm-conditions-in-pagination/56476/8 "2006-03-31T18:25:53Z")

</div>

It would be really nice if named associations automatically did  
this… so I could do tags.id = rather than customers\_tags.tag\_id…

which brings me to my next (1.1) eager-loading question…

Is it possible to filter on 1+th-level associations?

In other words… (as an example)…

I have: customer belongs\_to :owner, owner :has\_many :supervisors,  
supervisor :has\_many addresses.

I want to find all customers whose owners’ supervisors have addresses  
in the state of california…

Could I even do this? Or would I have to grab what I could using an  
eager find, and then filter it post-sql? I’m guessing that’d probably  
be the fastest and best way to do it anyway.

Ah, bring on the object-databases such as Gemstone Smalltalk! 🙂

Julian.