Problem with habtm conditions in pagination

Rails
1

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/
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

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)

2

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.

3

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.

4

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. :slight_smile:

Jason

5

Hmmm it seems incredibly slow tho… :frowning:

6

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.

7

LOL!

8

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! :slight_smile:

Julian.