ActiveRecord Nested Includes producing SQL Error.

Question

paginate :per_page => per_page, :page => page,
         :include => [{:stores => :locations}, :categories, :kosher], :origin => origin,
         :conditions => ["products.name LIKE ? #{conditions}", "%#{search}%"],
         :within => distance,
         :order => sort_order

I am using ruby 1.9.1p243 with Rails 2.3.5 on OSX and this code works fine (development computer)

On the production server, Ruby 1.9.1p376 with Rails 2.3.5 on Ubuntu, it produces a SQL error.

>> Product.mobile_search('rolls', '', '', '7421 Frankford Rd, Dallas, TX', 1, 25, 'products.name', 100)
ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'locations.lat' in 'where clause': SELECT count(DISTINCT `products`.id) AS count_all FROM `products`  LEFT OUTER JOIN `product_stores` ON (`products`.`id` = `product_stores`.`product_id`)  LEFT OUTER JOIN `stores` ON (`stores`.`id` = `product_stores`.`store_id`) WHERE (((products.name LIKE '%rolls%' ) AND (locations.lat>31.5523614714641 AND locations.lat<34.4437585285359 AND locations.lng>-98.4992307465503 AND locations.lng<-95.0520192534497)) AND (
          (ACOS(least(1,COS(0.57592479377064)*COS(-1.68905329192534)*COS(RADIANS(locations.lat))*COS(RADIANS(locations.lng))+
          COS(0.57592479377064)*SIN(-1.68905329192534)*COS(RADIANS(locations.lat))*SIN(RADIANS(locations.lng))+
          SIN(0.57592479377064)*SIN(RADIANS(locations.lat))))*3963.19)
          <= 100)) 
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `rescue in log'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:202:in `log'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/connection_adapters/mysql_adapter.rb:323:in `execute'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/connection_adapters/mysql_adapter.rb:608:in `select'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all_with_query_cache'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in `select_one'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in `select_value'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/calculations.rb:237:in `execute_simple_calculation'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/calculations.rb:134:in `block in calculate'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/calculations.rb:130:in `catch'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/calculations.rb:130:in `calculate'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/activerecord-2.3.5/lib/active_record/calculations.rb:48:in `count'
    from /var/www/vhosts/mgm.semaphoremobile.com/releases/20100112231026/vendor/plugins/geokit-rails/lib/geokit-rails/acts_as_mappable.rb:162:in `count'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/will_paginate-2.3.11/lib/will_paginate/finder.rb:223:in `block in wp_count'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/will_paginate-2.3.11/lib/will_paginate/finder.rb:235:in `call'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/will_paginate-2.3.11/lib/will_paginate/finder.rb:235:in `wp_count'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/will_paginate-2.3.11/lib/will_paginate/finder.rb:85:in `block in paginate'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/will_paginate-2.3.11/lib/will_paginate/collection.rb:87:in `create'
    from /usr/local/lib/ruby1.9/gems/1.9.1/gems/will_paginate-2.3.11/lib/will_paginate/finder.rb:76:in `paginate'
    from /var/www/vhosts/mgm.semaphoremobile.com/releases/20100112231026/app/models/product.rb:60:in `mobile_search'
    from (irb):1
    from /usr/bin/irb:12:in `<main>'

Does anyone have any thoughts on this?

Also, all gems that are used match in version numbers.



Here is the sql that is generated

    SELECT `products`.`id` AS t0_r0, `products`.`name` AS t0_r1, `products`.`brand` AS t0_r2, `products`.`source_url` AS t0_r3, `products`.`kosher_id` AS t0_r4, `products`.`gluten_free_id` AS t0_r5, `products`.`created_at` AS t0_r6, `products`.`updated_at` AS t0_r7, `products`.`image_url` AS t0_r8, `stores`.`id` AS t1_r0, `stores`.`name` AS t1_r1, `stores`.`source_url` AS t1_r2, `stores`.`created_at` AS t1_r3, `stores`.`updated_at` AS t1_r4, `locations`.`id` AS t2_r0, `locations`.`store_id` AS t2_r1, `locations`.`lat` AS t2_r2, `locations`.`lng` AS t2_r3, `locations`.`phone` AS t2_r4, `locations`.`address` AS t2_r5, `locations`.`city` AS t2_r6, `locations`.`state` AS t2_r7, `locations`.`zip` AS t2_r8, `locations`.`created_at` AS t2_r9, `locations`.`updated_at` AS t2_r10, `categories`.`id` AS t3_r0, `categories`.`name` AS t3_r1, `categories`.`created_at` AS t3_r2, `categories`.`updated_at` AS t3_r3, `koshers`.`id` AS t4_r0, `koshers`.`name` AS t4_r1, `koshers`.`brand` AS t4_r2, `koshers`.`category` AS t4_r3, `koshers`.`org` AS t4_r4, `koshers`.`org_id` AS t4_r5, `koshers`.`created_at` AS t4_r6, `koshers`.`updated_at` AS t4_r7, `koshers`.`certified` AS t4_r8, `stores_products`.`id` AS t5_r0, `stores_products`.`name` AS t5_r1, `stores_products`.`source_url` AS t5_r2, `stores_products`.`created_at` AS t5_r3, `stores_products`.`updated_at` AS t5_r4 FROM `products` LEFT OUTER JOIN `product_stores` ON (`products`.`id` = `product_stores`.`product_id`) LEFT OUTER JOIN `stores` ON (`stores`.`id` = `product_stores`.`store_id`) LEFT OUTER JOIN `locations` ON locations.store_id = stores.id LEFT OUTER JOIN `category_products` ON (`products`.`id` = `category_products`.`product_id`) LEFT OUTER JOIN `categories` ON (`categories`.`id` = `category_products`.`category_id`) LEFT OUTER JOIN `koshers` ON `koshers`.id = `products`.kosher_id LEFT OUTER JOIN `product_stores` stores_products_join ON (`products`.`id` = `stores_products_join`.`product_id`) LEFT OUTER JOIN `stores` stores_products ON (`stores_products`.`id` = `stores_products_join`.`store_id`) WHERE (((products.name LIKE '%%' ) AND (locations.lat>31.5523614714641 AND locations.lat<34.4437585285359 AND locations.lng>-98.4992307465503 AND locations.lng<-95.0520192534497)) AND (
    (ACOS(least(1,COS(0.57592479377064)*COS(-1.68905329192534)*COS(RADIANS(locations.lat))*COS(RADIANS(locations.lng))+
    COS(0.57592479377064)*SIN(-1.68905329192534)*COS(RADIANS(locations.lat))*SIN(RADIANS(locations.lng))+
    SIN(0.57592479377064)*SIN(RADIANS(locations.lat))))*3963.19)
    <= 100)) AND `products`.id IN (9560, 9561, 9562, 9563, 9564, 9565, 9566, 9567, 9568, 9569, 9570, 9571, 9572, 9573, 9574, 9575, 9576, 9577, 9578, 9579, 9580, 9581, 9582, 9583, 9584) ORDER BY products.name

EDIT: This is being caused by limiting the results with the will_paginate plugin. If I limit to results less than the query returns, it breaks, if I limit above the number, it is fine.

Answer 1

you probably forgot to to a migration on the server. check if 'locations.lat' exists.

Answer 2

I fixed this issue by replacing the will_paginate information with :offset and :limit.

find     :all, :offset => per_page.to_i * (page.to_i - 1), :limit => per_page,
         :include => [{:stores => :locations}, :categories, :kosher], :origin => origin,
         :conditions => ["products.name LIKE ? #{conditions}", "%#{search}%"],
         :within => distance,
         :order => sort_order