ActiveRecord子查询内连接

Question

我正在尝试将一条“原始”的PostGIS SQL查询转换为Rails ActiveRecord查询。我的目标是将两个连续的ActiveRecord查询（每个查询大约需要1毫秒）转换为一个单独的ActiveRecord查询（需要1毫秒）。使用下面的SQL和 ActiveRecord::Base.connection.execute ，我能够验证时间减少。

因此，我的直接请求是帮助我将这个查询转换为ActiveRecord查询（以及执行它的最佳方法）。

SELECT COUNT(*)
FROM "users"
INNER JOIN (
  SELECT "centroid"
  FROM "zip_caches"
  WHERE "zip_caches"."postalcode" = '<postalcode>'
) AS "sub" ON ST_Intersects("users"."vendor_coverage", "sub"."centroid")
WHERE "users"."active" = 1;

注意，在此查询中，值<postalcode>是唯一的可变数据。显然，这里有两个模型User和ZipCache。User与ZipCache没有直接关系。

当前的两步ActiveRecord查询如下。

zip = ZipCache.select(:centroid).where(postalcode: '<postalcode>').limit(1).first
User.where{st_intersects(vendor_coverage, zip.centroid)}.count

- Ryan

---

我学到的最重要的提示之一是，虽然在Ruby中可以链接方法很好，但如果您在代码中链接方法，则表明您没有遵循Demeter法则。不要从查看SQL查询开始，而应该查看ZipCache.select.where.limit.first并查看如何通过将逻辑下移来减少方法数量。你从ZipCache开始查询而不是用户模型有点奇怪...我错过了什么吗？ - Christos Hrousis

我会假定你“缺失”的部分是因为需要进行ActiveRecord的链接。我挑战你写一个ActiveRecord查询，只选择和填充一个模型和一个属性，并告诉我如何在不使用方法链接的情况下完成它。有些东西告诉我你对迪米特法则过于学究。虽然已经说过很多次了，但需要重申的是：迪米特法则不是点计数的练习。 - Ryan

Answer 1

免责声明：我从未使用过PostGIS

首先，在您的最终请求中，似乎您错过了WHERE "users"."active" = 1;部分。

这是我会做的：

首先，在用户上添加一个active范围（以便重复使用）

scope :active, -> { User.where(active: 1) }

然后对于实际查询，您可以拥有子查询而不执行它，并在User模型的连接中使用它，例如：

subquery = ZipCache.select(:centroid).where(postalcode: '<postalcode>')
User.active
    .joins("INNER JOIN (#{subquery.to_sql}) sub ON ST_Intersects(users.vendor_coverage, sub.centroid)")
    .count

这样可以最小化原始SQL的使用，同时保持仅有一个查询。

无论如何，通过将记录器级别设置为调试(debug)，在控制台/日志中检查实际的SQL请求。

- astreal

---

你需要使用 subquery.to_sql。仅使用 subquery 无法正确插值。 - chadoh

Answer 2

惊人的工具 scuttle.io 是将这些类型的查询转换的完美工具：

User.select(Arel.star.count).where(User.arel_table[:active].eq(1)).joins(
  User.arel_table.join(ZipCach.arel_table).on(
    Arel::Nodes::NamedFunction.new(
      'ST_Intersects', [
        User.arel_table[:vendor_coverage], Sub.arel_table[:centroid]
      ]
    )
  ).join_sources
)

- Dan Kohn

---

整洁，不知道scuttle。这是正确的答案，但在这种情况下，我建议只使用SQL。如果您不进行算法组合，则通过ARel编写它不会获得任何好处。它几乎是不可理解的。.star.count！？ 'ST_Intersects'！？为什么有些DSL方法而NamedFunction不是？没有人应该真正编写这段代码。 - Xavier Shay

这里并不是说性能很重要，但 ARel 至少需要数十个方法调用，并且容易受到 API 更改的影响。使用 SQL 就不需要处理这些问题。 - Xavier Shay