sql - 优化困难查询(可能使用 squeel)

Question

有这样的代码(使用 PublicActivity gem & Squeel)

  def index
    @activities = Activity.limit(20).order { created_at.desc }
    @one = @activities.where{trackable_type == 'Post'}.includes(trackable: [:author, :project])
    @two = @activities.where{trackable_type == 'Project'}.includes trackable: [:owner]
    @activities = @one + @two
  end

但它创建了 8 SQL 请求:

 SELECT "activities".* FROM "activities" WHERE "activities"."trackable_type" = 'Post' ORDER BY "activities"."created_at" DESC LIMIT 20

      SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (800, 799, 798, 797, 796, 795, 794, 793, 792, 791, 790, 789, 788, 787, 786, 785, 784, 783, 782, 781)

      SELECT "users".* FROM "users" WHERE "users"."id" IN (880, 879, 878, 877, 876, 875, 874, 873, 872, 871, 869, 868, 867, 866, 865, 864, 863, 862, 861, 860)

      SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (80, 79)

      SELECT "activities".* FROM "activities" WHERE "activities"."trackable_type" = 'Project' ORDER BY "activities"."created_at" DESC LIMIT 20

      SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (80, 79, 78, 77, 76, 75, 74, 73, 72, 71, 70, 69, 68, 67, 66, 65, 64, 63, 62, 61)

     SELECT "users".* FROM "users" WHERE "users"."id" IN (870, 859, 848, 837, 826, 815, 804, 793, 782, 771, 760, 749, 738, 727, 716, 705, 694, 683, 672, 661)

事件请求未加入

一些用户(帖子所有者和项目所有者)被加载两次

有些项目加载了两次

@activity 是数组。 Rails 关系合并方法(除了 + )不适用于上面的代码。

任何优化它的想法？

Answer 1

非rails-4，非squeel解决方案是:

def index
  @activities = Activity.limit(20).order("created_at desc")
  @one = @activities.where(trackable_type: 'Post')   .joins(trackable: [:author, :project]).includes(trackable: [:author, :project])
  @two = @activities.where(trackable_type: 'Project').joins(trackable: [:owner])           .includes(trackable: [:owner])
  @activities = @one + @two
end

joins的组合和 includes看起来很奇怪，但在我的测试中它运行得非常好。

不过，这会将其减少到两个查询，而不是一个。而@activities 仍然是一个数组。但也许将这种方法与 squeel 结合使用也能解决这个问题。不幸的是，我不使用 squeel，也无法对其进行测试。

编辑:我完全错过了关于多态关联的重点。以上工程强制

如果您想使用 AR 提供的功能，这有点麻烦，但您可以定义只读关联的项目和帖子:

belongs_to :project, read_only: true, foreign_key: :trackable_id
belongs_to :post,    read_only: true, foreign_key: :trackable_id

对于那些强制急切加载的上述方法应该可以工作。 where条件仍然需要，所以这些协会只被要求进行正确的事件。

def index
  @activities = Activity.limit(20).order("created_at desc")
  @one = @activities.where(trackable_type: 'Post')   .joins(post: [:author, :project]).includes(post: [:author, :project])
  @two = @activities.where(trackable_type: 'Project').joins(project: [:owner])        .includes(project: [:owner])
  @activities = @one + @two
end

这不是一个干净的解决方案，关联应该 attr_protected 以确保它们不会被意外设置(我希望这会破坏多态性)，但从我的测试来看，它似乎有效。