时间戳

首页 > 解决方案 > 多对多自连接表上的 ActiveRecord 查询

sql - 多对多自连接表上的 ActiveRecord 查询

问题描述

我有一个称为多对多自联接表people，它使用以下模型：

class Person < ApplicationRecord
  has_and_belongs_to_many :children,
    class_name: "Person",
    join_table: "children_parents",
    foreign_key: "parent_id",
    association_foreign_key: "child_id",
    optional: true

  has_and_belongs_to_many :parents,
    class_name: "Person",
    join_table: "children_parents",
    foreign_key: "child_id",
    association_foreign_key: "parent_id",
    optional: true
end

如果在上述模型中不明显 - 除了people数据库中的表之外，还有一个children_parents带有两个外键索引字段child_id和parent_id. 这使我们能够表示孩子和父母之间的多对多关系。

我想查询一个人的兄弟姐妹，所以我在 Person 模型中添加了以下方法：

def siblings
  self.parents.map do |parent|
    parent.children.reject { |child| child.id == self.id }
  end.flatten.uniq
end

但是，这会产生三个 SQL 查询：

  Person Load (1.0ms)  SELECT "people".* FROM "people" INNER JOIN "children_parents" ON "people"."id" = "children_parents"."parent_id" WHERE "children_parents"."child_id" = $1  [["child_id", 3]]
  Person Load (0.4ms)  SELECT "people".* FROM "people" INNER JOIN "children_parents" ON "people"."id" = "children_parents"."child_id" WHERE "children_parents"."parent_id" = $1  [["parent_id", 1]]
  Person Load (0.4ms)  SELECT "people".* FROM "people" INNER JOIN "children_parents" ON "people"."id" = "children_parents"."child_id" WHERE "children_parents"."parent_id" = $1  [["parent_id", 2]]

我知道可以像这样使它成为一个 SQL 查询：

SELECT DISTINCT(p.*) FROM people p
INNER JOIN children_parents cp ON p.id = cp.child_id
WHERE cp.parent_id IN ($1, $2)
AND cp.child_id != $3

$1和$2是人的父母 id，$3是人的 id。

有没有办法使用 ActiveRecord 进行此查询？

标签： sqlruby-on-railsactiverecord

解决方案

你可以使用这样的东西：

def siblings
  Person.select('siblings.*').from('people AS siblings').where.not(id: id)
    .where(
      parents.joins(
        'JOIN children_parents ON parent_id = people.id AND child_id = siblings.id'
      ).exists
    )
end

在这里你可以看到一些奇怪的东西：

from设置表别名。你应该避免这种情况，因为在这样的表别名活动记录将不再对来自 ruby​​ 的列名有帮助： where(column: value).order(:column) - 将不起作用，只剩下普通的 sql 字符串

存在- 我经常使用它而不是连接。当您将许多记录连接到一个时，您会收到重复的记录，然后它们会出现不同的或分组的新问题。Exists还提供了查询的隔离：EXISTS 表达式中的表和列对于查询的其他部分是不可见的。在 Rails 中使用它的坏处：至少需要 1 个普通 SQL。

这种方法的一个缺点是：如果您将在某处为每条记录调用它，那么您将对每条记录进行 1 次查询 - N+1 问题。

现在，关于 Rails 方式的几句话。Rails 指南建议始终使用 has_many :through 而不是 habtm，我在这里看到：https ://github.com/rubocop-hq/rails-style-guide 。

我理解的 Rails 意识形态代表了开发速度和维护的简单性。首先意味着性能无关紧要（想象一下你需要多少用户开始使用它），其次说普通 SQL 的灵活性很好，但不是在 rails 中，在 rails 中请让代码尽可能简单（参见 rubocop默认值：方法中的 10 个位置，类中的 100 个位置，以及 4 个复杂度指标总是说您的代码太复杂）。我的意思是，许多现实世界的 Rails 项目都在使用 N+1 进行查询，进行无效的查询，这很少会成为问题

因此，在这种情况下，我建议尝试include、preload、eager_load。

推荐阅读