Forum: Ruby on Rails problem with habtm conditions in pagination

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
3131fcea0a711e5ad89c8d49cc9253b4?d=identicon&s=25 Julian Leviston (Guest)
on 2006-03-31 15:52
(Received via mailing list)
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 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)
19e911d39ba0268056582057508ec2c0?d=identicon&s=25 Jason West (Guest)
on 2006-03-31 15:58
(Received via mailing list)
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.
3131fcea0a711e5ad89c8d49cc9253b4?d=identicon&s=25 Julian Leviston (Guest)
on 2006-03-31 16:44
(Received via mailing list)
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... <grinz>

Julian.
19e911d39ba0268056582057508ec2c0?d=identicon&s=25 Jason West (Guest)
on 2006-03-31 17:15
(Received via mailing list)
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
3131fcea0a711e5ad89c8d49cc9253b4?d=identicon&s=25 Julian Leviston (Guest)
on 2006-03-31 17:42
(Received via mailing list)
Hmmm it seems incredibly slow tho... :(
19e911d39ba0268056582057508ec2c0?d=identicon&s=25 Jason West (Guest)
on 2006-03-31 17:45
(Received via mailing list)
LOL!
3131fcea0a711e5ad89c8d49cc9253b4?d=identicon&s=25 Julian Leviston (Guest)
on 2006-03-31 18:04
(Received via mailing list)
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.
3131fcea0a711e5ad89c8d49cc9253b4?d=identicon&s=25 Julian Leviston (Guest)
on 2006-03-31 18:25
(Received via mailing list)
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.
This topic is locked and can not be replied to.