按关联属性对ActiveRecord查询排序

Question

在我的Rails应用程序中，一个项目有很多步骤和图像，步骤有很多图像。

class Project < ActiveRecord::Base
  has_many :steps
  has_many :images
end

class Step < ActiveRecord::Base
  has_many :images
  belongs_to :project
end

class Image < ActiveRecord::Base
  belongs_to :step
end

我想做一个查询，在这里我可以检索项目中的最终图像--这将涉及确定包含图像的最后一步，并获取该步骤中的最后一个映像。

目前，我通过执行类似于@project.default_image的操作获取最后一个映像：

class Project < ActiveRecord::Base
 def last_step_with_images
    last_step = ""
    steps.order("published_on DESC").each do |step|
      if step.images.count>0 
        last_step = step
        return last_step
      end
    end
    return last_step
  end

  def default_image
    step_of_last_image = last_step_with_images
    unless step_of_last_image.blank?
        step_of_last_image.last_image
    end
  end
end

在我的步进模型中：

  class Step < ActiveRecord::Base
     def last_image
        images.order("position ASC").last if images
     end
  end

但我想我能更有效率地完成这件事。我试着使用以下一行：

@project.images.includes(:step).order("step.published_on")

但它返回错误：

1.9.3p448 :012 > project.images.includes(:step).order("step.published_on DESC")
  SQL (0.6ms)  SELECT "images"."id" AS t0_r0, "images"."step_id" AS t0_r1, "images"."image_path" AS t0_r2, "images"."created_at" AS t0_r3, "images"."updated_at" AS t0_r4, "images"."caption" AS t0_r5, "images"."project_id" AS t0_r6, "images"."position" AS t0_r7, "images"."saved" AS t0_r8, "images"."video_id" AS t0_r9, "images"."original_id" AS t0_r10, "images"."sound_id" AS t0_r11, "images"."user_id" AS t0_r12, "images"."s3_filepath" AS t0_r13, "steps"."id" AS t1_r0, "steps"."project_id" AS t1_r1, "steps"."name" AS t1_r2, "steps"."position" AS t1_r3, "steps"."description" AS t1_r4, "steps"."created_at" AS t1_r5, "steps"."updated_at" AS t1_r6, "steps"."ancestry" AS t1_r7, "steps"."published_on" AS t1_r8, "steps"."last" AS t1_r9, "steps"."user_id" AS t1_r10, "steps"."original_authors" AS t1_r11, "steps"."label" AS t1_r12, "steps"."label_color" AS t1_r13, "steps"."position_index" AS t1_r14 FROM "images" LEFT OUTER JOIN "steps" ON "steps"."id" = "images"."step_id" WHERE "images"."project_id" = 17 ORDER BY step.published_on DESC
Hirb Error: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "step"
LINE 1: ...tep_id" WHERE "images"."project_id" = 17 ORDER BY step.publi...
                                                             ^
: SELECT "images"."id" AS t0_r0, "images"."step_id" AS t0_r1, "images"."image_path" AS t0_r2, "images"."created_at" AS t0_r3, "images"."updated_at" AS t0_r4, "images"."caption" AS t0_r5, "images"."project_id" AS t0_r6, "images"."position" AS t0_r7, "images"."saved" AS t0_r8, "images"."video_id" AS t0_r9, "images"."original_id" AS t0_r10, "images"."sound_id" AS t0_r11, "images"."user_id" AS t0_r12, "images"."s3_filepath" AS t0_r13, "steps"."id" AS t1_r0, "steps"."project_id" AS t1_r1, "steps"."name" AS t1_r2, "steps"."position" AS t1_r3, "steps"."description" AS t1_r4, "steps"."created_at" AS t1_r5, "steps"."updated_at" AS t1_r6, "steps"."ancestry" AS t1_r7, "steps"."published_on" AS t1_r8, "steps"."last" AS t1_r9, "steps"."user_id" AS t1_r10, "steps"."original_authors" AS t1_r11, "steps"."label" AS t1_r12, "steps"."label_color" AS t1_r13, "steps"."position_index" AS t1_r14 FROM "images" LEFT OUTER JOIN "steps" ON "steps"."id" = "images"."step_id" WHERE "images"."project_id" = 17 ORDER BY step.published_on DESC
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `exec'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `exec_no_cache'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:660:in `block in exec_query'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
    .rvm/gems/ruby-1.9.3-p448/gems/activesupport-3.2.15/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:659:in `exec_query'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:1262:in `select'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation/finder_methods.rb:212:in `find_with_associations'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation.rb:171:in `exec_queries'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation.rb:160:in `block in to_a'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/explain.rb:34:in `logging_query_plan'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation.rb:159:in `to_a'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation/delegation.rb:6:in `to_ary'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/formatter.rb:88:in `Array'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/formatter.rb:88:in `determine_output_class'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/formatter.rb:53:in `format_output'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/view.rb:204:in `render_output'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/view.rb:123:in `view_output'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/view.rb:200:in `view_or_page_output'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/view.rb:186:in `output_value'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:160:in `block (2 levels) in eval_input'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:273:in `signal_status'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:156:in `block in eval_input'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:243:in `block (2 levels) in each_top_level_statement'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:229:in `loop'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:229:in `block in each_top_level_statement'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:228:in `catch'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:228:in `each_top_level_statement'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:155:in `eval_input'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:70:in `block in start'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:69:in `catch'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:69:in `start'
    .rvm/gems/ruby-1.9.3-p448/gems/railties-3.2.15/lib/rails/commands/console.rb:47:in `start'
    .rvm/gems/ruby-1.9.3-p448/gems/railties-3.2.15/lib/rails/commands/console.rb:8:in `start'
    .rvm/gems/ruby-1.9.3-p448/gems/railties-3.2.15/lib/rails/commands.rb:41:in `<top (required)>'
    script/rails:6:in `require'
    script/rails:6:in `<main>'

编辑

下面是我在控制台中执行以下操作时的错误日志：

@project.images.joins(:step).order('step.published_on')

 Image Load (59.7ms)  SELECT "images".* FROM "images" INNER JOIN "steps" ON "steps"."id" = "images"."step_id" WHERE "images"."project_id" = 17 ORDER BY step.published_on
Hirb Error: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "step"
LINE 1: ...tep_id" WHERE "images"."project_id" = 17 ORDER BY step.publi...
                                                             ^
: SELECT "images".* FROM "images" INNER JOIN "steps" ON "steps"."id" = "images"."step_id" WHERE "images"."project_id" = 17 ORDER BY step.published_on
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `exec'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:1163:in `exec_no_cache'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:660:in `block in exec_query'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
    .rvm/gems/ruby-1.9.3-p448/gems/activesupport-3.2.15/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:659:in `exec_query'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/postgresql_adapter.rb:1262:in `select'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/querying.rb:38:in `block in find_by_sql'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/explain.rb:41:in `logging_query_plan'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/querying.rb:37:in `find_by_sql'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation.rb:171:in `exec_queries'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation.rb:160:in `block in to_a'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/explain.rb:34:in `logging_query_plan'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation.rb:159:in `to_a'
    .rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.15/lib/active_record/relation/delegation.rb:6:in `to_ary'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/formatter.rb:88:in `Array'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/formatter.rb:88:in `determine_output_class'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/formatter.rb:53:in `format_output'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/view.rb:204:in `render_output'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/view.rb:123:in `view_output'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/view.rb:200:in `view_or_page_output'
    .rvm/gems/ruby-1.9.3-p448/gems/hirb-0.7.1/lib/hirb/view.rb:186:in `output_value'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:160:in `block (2 levels) in eval_input'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:273:in `signal_status'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:156:in `block in eval_input'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:243:in `block (2 levels) in each_top_level_statement'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:229:in `loop'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:229:in `block in each_top_level_statement'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:228:in `catch'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb/ruby-lex.rb:228:in `each_top_level_statement'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:155:in `eval_input'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:70:in `block in start'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:69:in `catch'
    .rvm/rubies/ruby-1.9.3-p448/lib/ruby/1.9.1/irb.rb:69:in `start'
    .rvm/gems/ruby-1.9.3-p448/gems/railties-3.2.15/lib/rails/commands/console.rb:47:in `start'
    .rvm/gems/ruby-1.9.3-p448/gems/railties-3.2.15/lib/rails/commands/console.rb:8:in `start'
    .rvm/gems/ruby-1.9.3-p448/gems/railties-3.2.15/lib/rails/commands.rb:41:in `<top (required)>'
    script/rails:6:in `require'
    script/rails:6:in `<main>'

有人知道如何为这样的查询使用急切的加载吗？

Answer 1

原因是includes只是减少了1+N问题，如果检查@project.images.includes(:step)生成的SQL，应该如下所示：

SELECT * from images WHERE images.project_id = 2

SELECT * from steps WHERE steps.image_id IN (1,2,3,4,5)

正如您在查询中看到的，它获取图像，然后步骤属于每个图像。你可能需要加入而不是加入。

@project.images.joins(:step).order('steps.published_on')

这将返回step的published_on命令的图像，如果还想使用step中的属性，请尝试如下所示：

@project.images.joins(:step).order('steps.published_on').select('images.*, steps.field_1 AS step_field1, steps.field_2 AS step_field2')

如果有帮助请告诉我！