sql - 如何像在 Railscast 340 中那样使用 datatable/will_paginate 对 “foreign” 列进行排序？

Question

环境:Ruby 2.0.0、Rails 4.0.3、Windows 8.1、PostreSQL、Datatable 1.12.2、Will_Paginate 3.0.5

感谢此处提供的帮助，我已经成功实现了 Railscast 340 解决方案。但是，我的表中包含的列不是显示表的 native 列。这些列是使用 has_many 通过的多态关系。

正在显示的表是:

class Product < ActiveRecord::Base
  has_one :location, dependent: :destroy
  has_one :patron, through: :location, source: :locator, source_type: 'Patron'
  has_one :shelf, through: :location, source: :locator, source_type: 'Shelf'

编辑以添加位置(通过)表:

class Location < ActiveRecord::Base
  belongs_to :product
  belongs_to :locator, polymorphic: true

需要用于排序的一个多态列是:

class Shelf < ActiveRecord::Base
  has_many :locations, as: :locator, dependent: :nullify
  has_many :products, through: :locations, dependent: :nullify

class Patron < ActiveRecord::Base
  has_many :locations, as: :locator, dependent: :nullify
  has_many :products, through: :locations, dependent: :nullify

该列正在正常显示。但是，由于 Product 表中不存在该列，因此无法使用基本语句进行排序:

products = Product.order("#{sort_column} #{sort_direction}")

我知道我可以读取表格，对其进行排序并使其可供显示，但由于性能受到影响，这是我通过实现 Railscast 340 试图避免的情况。

我假设有某种查询、连接或排序序列可以让我这样做，但我不知道从哪里开始……阅读 ActiveRecord 查询指南，尽管它相对较好向导走了，让我比开始时更加困惑。任何关于如何攻击这个的方向将不胜感激。谢谢。

编辑堆栈跟踪如下

排序是:

products = Product.all.joins(:location).order("location.product.readable_loc #{sort_direction}")

堆栈跟踪是:

Started GET "/products.json?sEcho=3&iColumns=8&sColumns=&iDisplayStart=0&iDisplayLength=10&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4&mDataProp_5=5&mDataProp_6=6&mDataProp_7=7&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=true&sSearch_5=&bRegex_5=false&bSearchable_5=true&sSearch_6=&bRegex_6=false&bSearchable_6=true&sSearch_7=&bRegex_7=false&bSearchable_7=true&iSortCol_0=6&sSortDir_0=desc&iSortingCols=1&bSortable_0=true&bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true&bSortable_5=true&bSortable_6=true&bSortable_7=true&_=1401813038880" for 127.0.0.1 at 2014-06-03 14:50:55 -0400
Processing by ProductsController#index as JSON
  Parameters: {"sEcho"=>"3", "iColumns"=>"8", "sColumns"=>"", "iDisplayStart"=>"0", "iDisplayLength"=>"10", "mDataProp_0"=>"0", "mDataProp_1"=>"1", "mDataProp_2"=>"2", "mDataProp_3"=>"3", "mDataProp_4"=>"4", "mDataProp_5"=>"5", "mDataProp_6"=>"6", "mDataProp_7"=>"7", "sSearch"=>"", "bRegex"=>"false", "sSearch_0"=>"", "bRegex_0"=>"false", "bSearchable_0"=>"true", "sSearch_1"=>"", "bRegex_1"=>"false", "bSearchable_1"=>"true", "sSearch_2"=>"", "bRegex_2"=>"false", "bSearchable_2"=>"true", "sSearch_3"=>"", "bRegex_3"=>"false", "bSearchable_3"=>"true", "sSearch_4"=>"", "bRegex_4"=>"false", "bSearchable_4"=>"true", "sSearch_5"=>"", "bRegex_5"=>"false", "bSearchable_5"=>"true", "sSearch_6"=>"", "bRegex_6"=>"false", "bSearchable_6"=>"true", "sSearch_7"=>"", "bRegex_7"=>"false", "bSearchable_7"=>"true", "iSortCol_0"=>"6", "sSortDir_0"=>"desc", "iSortingCols"=>"1", "bSortable_0"=>"true", "bSortable_1"=>"true", "bSortable_2"=>"true", "bSortable_3"=>"true", "bSortable_4"=>"true", "bSortable_5"=>"true", "bSortable_6"=>"true", "bSortable_7"=>"true", "_"=>"1401813038880"}
  Company Load (1.0ms)  SELECT "companies".* FROM "companies" WHERE "companies"."prefix" = 'ucf' ORDER BY "companies"."id" ASC LIMIT 1
  Device Load (0.0ms)  SELECT "devices".* FROM "devices" WHERE "devices"."company_id" = 54 AND "devices"."id" = 601 ORDER BY "devices"."id" ASC LIMIT 1
   (1.0ms)  SELECT COUNT(*) FROM "roles" INNER JOIN "devices_roles" ON "roles"."id" = "devices_roles"."role_id" WHERE "devices_roles"."device_id" = $1 AND (((roles.name = 'admin') AND (roles.resource_type IS NULL) AND (roles.resource_id IS NULL)))  [["device_id", 601]]
   (0.0ms)  SELECT COUNT(*) FROM "products" WHERE "products"."company_id" = 54
   (1.0ms)  SELECT COUNT(*) FROM "products" INNER JOIN "locations" ON "locations"."product_id" = "products"."id" AND "locations"."company_id" = 54 WHERE "products"."company_id" = 54
  Product Load (2.0ms)  SELECT "products".* FROM "products" INNER JOIN "locations" ON "locations"."product_id" = "products"."id" AND "locations"."company_id" = 54 WHERE "products"."company_id" = 54 ORDER BY location.product.readable_loc desc LIMIT 10 OFFSET 0
PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "product"
LINE 1: ...id" = 54 WHERE "products"."company_id" = 54  ORDER BY product_locati...
                                                             ^
: SELECT  "products".* FROM "products" INNER JOIN "locations" ON "locations"."product_id" = "products"."id" AND "locations"."company_id" = 54 WHERE "products"."company_id" = 54  ORDER BY location.product.readable_loc desc LIMIT 10 OFFSET 0
Completed 500 Internal Server Error in 22ms

PG::UndefinedTable - ERROR:  missing FROM-clause entry for table "product"
LINE 1: ...id" = 54 WHERE "products"."company_id" = 54  ORDER BY product_locati...
                                                             ^
:
  activerecord (4.0.3) lib/active_record/connection_adapters/postgresql_adapter.rb:774:in `exec_no_cache'
  activerecord (4.0.3) lib/active_record/connection_adapters/postgresql/database_statements.rb:138:in `block in exec_query'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract_adapter.rb:435:in `block in log'
  activesupport (4.0.3) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract_adapter.rb:430:in `log'
  activerecord (4.0.3) lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
  activerecord (4.0.3) lib/active_record/connection_adapters/postgresql_adapter.rb:891:in `select'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/query_cache.rb:61:in `block in select_all'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/query_cache.rb:76:in `cache_sql'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/query_cache.rb:61:in `select_all'
  activerecord (4.0.3) lib/active_record/querying.rb:36:in `find_by_sql'
  activerecord (4.0.3) lib/active_record/relation.rb:585:in `exec_queries'
  activerecord (4.0.3) lib/active_record/relation.rb:471:in `load'
  activerecord (4.0.3) lib/active_record/relation.rb:220:in `to_a'
  will_paginate (3.0.5) lib/will_paginate/active_record.rb:124:in `to_a'
  activerecord (4.0.3) lib/active_record/relation.rb:598:in `exec_queries'
  activerecord (4.0.3) lib/active_record/relation.rb:471:in `load'
  activerecord (4.0.3) lib/active_record/relation.rb:220:in `to_a'
  will_paginate (3.0.5) lib/will_paginate/active_record.rb:127:in `block in to_a'
  will_paginate (3.0.5) lib/will_paginate/collection.rb:96:in `create'
  will_paginate (3.0.5) lib/will_paginate/active_record.rb:126:in `to_a'
  D:65535:in `map'
  app/datatables/products_datatable.rb:20:in `data'
  app/datatables/products_datatable.rb:13:in `as_json'
  activesupport (4.0.3) lib/active_support/json/encoding.rb:50:in `block in encode'
  activesupport (4.0.3) lib/active_support/json/encoding.rb:81:in `check_for_circular_references'
  activesupport (4.0.3) lib/active_support/json/encoding.rb:49:in `encode'
  activesupport (4.0.3) lib/active_support/json/encoding.rb:34:in `encode'
  activesupport (4.0.3) lib/active_support/core_ext/object/to_json.rb:16:in `to_json'
  actionpack (4.0.3) lib/action_controller/metal/renderers.rb:90:in `block in <module:Renderers>'
  actionpack (4.0.3) lib/action_controller/metal/renderers.rb:33:in `block in _handle_render_options'
  D:/BitNami/rubystack-2.0.0-11/ruby/lib/ruby/2.0.0/set.rb:232:in `each'
  actionpack (4.0.3) lib/action_controller/metal/renderers.rb:30:in `_handle_render_options'
  actionpack (4.0.3) lib/action_controller/metal/renderers.rb:26:in `render_to_body'
  actionpack (4.0.3) lib/abstract_controller/rendering.rb:97:in `render'
  actionpack (4.0.3) lib/action_controller/metal/rendering.rb:16:in `render'
  actionpack (4.0.3) lib/action_controller/metal/instrumentation.rb:41:in `block (2 levels) in render'
  activesupport (4.0.3) lib/active_support/core_ext/benchmark.rb:12:in `block in ms'
  D:/BitNami/rubystack-2.0.0-11/ruby/lib/ruby/2.0.0/benchmark.rb:296:in `realtime'
  activesupport (4.0.3) lib/active_support/core_ext/benchmark.rb:12:in `ms'
  actionpack (4.0.3) lib/action_controller/metal/instrumentation.rb:41:in `block in render'
  actionpack (4.0.3) lib/action_controller/metal/instrumentation.rb:84:in `cleanup_view_runtime'
  activerecord (4.0.3) lib/active_record/railties/controller_runtime.rb:25:in `cleanup_view_runtime'
  actionpack (4.0.3) lib/action_controller/metal/instrumentation.rb:40:in `render'
  app/controllers/products_controller.rb:8:in `block (2 levels) in index'
  actionpack (4.0.3) lib/action_controller/metal/mime_responds.rb:191:in `respond_to'
  app/controllers/products_controller.rb:6:in `index'
  actionpack (4.0.3) lib/action_controller/metal/implicit_render.rb:4:in `send_action'
  actionpack (4.0.3) lib/abstract_controller/base.rb:189:in `process_action'
  actionpack (4.0.3) lib/action_controller/metal/rendering.rb:10:in `process_action'
  actionpack (4.0.3) lib/abstract_controller/callbacks.rb:18:in `block in process_action'
  activesupport (4.0.3) lib/active_support/callbacks.rb:453:in `_run__936629966__process_action__callbacks'
  activesupport (4.0.3) lib/active_support/callbacks.rb:80:in `run_callbacks'
  actionpack (4.0.3) lib/abstract_controller/callbacks.rb:17:in `process_action'
  actionpack (4.0.3) lib/action_controller/metal/rescue.rb:29:in `process_action'
  actionpack (4.0.3) lib/action_controller/metal/instrumentation.rb:31:in `block in process_action'
  activesupport (4.0.3) lib/active_support/notifications.rb:159:in `block in instrument'
  activesupport (4.0.3) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activesupport (4.0.3) lib/active_support/notifications.rb:159:in `instrument'
  actionpack (4.0.3) lib/action_controller/metal/instrumentation.rb:30:in `process_action'
  actionpack (4.0.3) lib/action_controller/metal/params_wrapper.rb:245:in `process_action'
  activerecord (4.0.3) lib/active_record/railties/controller_runtime.rb:18:in `process_action'
  actionpack (4.0.3) lib/abstract_controller/base.rb:136:in `process'
  actionpack (4.0.3) lib/abstract_controller/rendering.rb:44:in `process'
  actionpack (4.0.3) lib/action_controller/metal.rb:195:in `dispatch'
  actionpack (4.0.3) lib/action_controller/metal/rack_delegation.rb:13:in `dispatch'
  actionpack (4.0.3) lib/action_controller/metal.rb:231:in `block in action'
  actionpack (4.0.3) lib/action_dispatch/routing/route_set.rb:80:in `dispatch'
  actionpack (4.0.3) lib/action_dispatch/routing/route_set.rb:48:in `call'
  actionpack (4.0.3) lib/action_dispatch/journey/router.rb:71:in `block in call'
  actionpack (4.0.3) lib/action_dispatch/journey/router.rb:59:in `call'
  actionpack (4.0.3) lib/action_dispatch/routing/route_set.rb:680:in `call'
  request_store (1.0.5) lib/request_store/middleware.rb:9:in `call'
  warden (1.2.3) lib/warden/manager.rb:35:in `block in call'
  warden (1.2.3) lib/warden/manager.rb:34:in `call'
  rack (1.5.2) lib/rack/etag.rb:23:in `call'
  rack (1.5.2) lib/rack/conditionalget.rb:25:in `call'
  rack (1.5.2) lib/rack/head.rb:11:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/params_parser.rb:27:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/flash.rb:241:in `call'
  rack (1.5.2) lib/rack/session/abstract/id.rb:225:in `context'
  rack (1.5.2) lib/rack/session/abstract/id.rb:220:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/cookies.rb:486:in `call'
  activerecord (4.0.3) lib/active_record/query_cache.rb:36:in `call'
  activerecord (4.0.3) lib/active_record/connection_adapters/abstract/connection_pool.rb:626:in `call'
  activerecord (4.0.3) lib/active_record/migration.rb:369:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
  activesupport (4.0.3) lib/active_support/callbacks.rb:373:in `_run__103024161__call__callbacks'
  activesupport (4.0.3) lib/active_support/callbacks.rb:80:in `run_callbacks'
  actionpack (4.0.3) lib/action_dispatch/middleware/callbacks.rb:27:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/reloader.rb:64:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/remote_ip.rb:76:in `call'
  better_errors (1.1.0) lib/better_errors/middleware.rb:84:in `protected_app_call'
  better_errors (1.1.0) lib/better_errors/middleware.rb:79:in `better_errors_call'
  better_errors (1.1.0) lib/better_errors/middleware.rb:56:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
  railties (4.0.3) lib/rails/rack/logger.rb:38:in `call_app'
  railties (4.0.3) lib/rails/rack/logger.rb:20:in `block in call'
  activesupport (4.0.3) lib/active_support/tagged_logging.rb:67:in `block in tagged'
  activesupport (4.0.3) lib/active_support/tagged_logging.rb:25:in `tagged'
  activesupport (4.0.3) lib/active_support/tagged_logging.rb:67:in `tagged'
  railties (4.0.3) lib/rails/rack/logger.rb:20:in `call'
  quiet_assets (1.0.2) lib/quiet_assets.rb:18:in `call_with_quiet_assets'
  actionpack (4.0.3) lib/action_dispatch/middleware/request_id.rb:21:in `call'
  rack (1.5.2) lib/rack/methodoverride.rb:21:in `call'
  rack (1.5.2) lib/rack/runtime.rb:17:in `call'
  activesupport (4.0.3) lib/active_support/cache/strategy/local_cache.rb:83:in `call'
  rack (1.5.2) lib/rack/lock.rb:17:in `call'
  actionpack (4.0.3) lib/action_dispatch/middleware/static.rb:64:in `call'
  rack (1.5.2) lib/rack/sendfile.rb:112:in `call'
  railties (4.0.3) lib/rails/engine.rb:511:in `call'
  railties (4.0.3) lib/rails/application.rb:97:in `call'
  rack (1.5.2) lib/rack/content_length.rb:14:in `call'
  thin (1.6.2) lib/thin/connection.rb:86:in `block in pre_process'
  thin (1.6.2) lib/thin/connection.rb:84:in `pre_process'
  thin (1.6.2) lib/thin/connection.rb:53:in `process'
  thin (1.6.2) lib/thin/connection.rb:39:in `receive_data'
  eventmachine-1.0.3-x86 (mingw32) lib/eventmachine.rb:187:in `run'
  thin (1.6.2) lib/thin/backends/base.rb:73:in `start'
  thin (1.6.2) lib/thin/server.rb:162:in `start'
  rack (1.5.2) lib/rack/handler/thin.rb:16:in `run'
  rack (1.5.2) lib/rack/server.rb:264:in `start'
  railties (4.0.3) lib/rails/commands/server.rb:84:in `start'
  railties (4.0.3) lib/rails/commands.rb:76:in `block in <top (required)>'
  railties (4.0.3) lib/rails/commands.rb:71:in `<top (required)>'
  bin/rails:4:in `<top (required)>'
  ruby-debug-ide (0.4.23.beta1) lib/ruby-debug-ide.rb:86:in `debug_program'
  ruby-debug-ide (0.4.23.beta1) bin/rdebug-ide:110:in `<top (required)>'
  -e:1:in `<main>'

编辑添加产品方法 readable_loc

  def readable_loc
    if self.location.locator.class == Patron
      self.location.locator.name  # Yields patron's name
    else
      self.location.locator.row.name + " " + self.location.locator.name  # Yields row name and shelf name. 
    end
  end

Answer 1

我认为你应该这样使用它:

products = Product.joins(:location).order("locations.#{sort_column} #{sort_direction}")

这将生成比 .includes 版本更好的 SQL 查询，但我不确定它如何与 has_one 关联一起工作。