如何在 ecto 中编写复杂的子查询 as from 子句

Question

我正在尝试在sql查询之后用Ecto语法编写，如何在FROM hierarchy,行之后编写子查询，它在from子句中，但我怀疑在Ecto中是否可能？我想知道我是否可以使用表连接甚至横向连接来执行此类查询，而不会以相同的效果损失性能？

SELECT routes.id, routes.name
FROM routes
WHERE routes.id IN
  (SELECT DISTINCT hierarchy.parent
   FROM hierarchy,
    (SELECT DISTINCT unnest(segments.rels) AS rel
     FROM segments
     WHERE ST_Intersects(segments.geom, ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1866349.262143 6886808.978425)', -1), ST_GeomFromText('POINT(1883318.282423 6876413.542579)', -1)), 3857))) AS anon_1
   WHERE hierarchy.child = anon_1.rel)

我坚持使用以下代码：

hierarchy_subquery =
  Hierarchy
  |> distinct([h], h.parent)
Route
|> select([r], r.id, r.name)
|> where([r], r.id in subquery(hierarchy_subquery))

模式：

defmodule MyApp.Hierarchy do
  use MyApp.Schema
  schema "hierarchy" do
    field :parent, :integer
    field :child, :integer
    field :deph, :integer
  end
end
defmodule MyApp.Route do
  use MyApp.Schema
  schema "routes" do
    field :name, :string
    field :intnames, :map
    field :symbol, :string
    field :country, :string
    field :network, :string
    field :level, :integer
    field :top, :boolean
    field :geom, Geo.Geometry, srid: 3857
  end
end
defmodule MyApp.Segment do
  use MyApp.Schema
  schema "segments" do
    field :ways, {:array, :integer}
    field :nodes, {:array, :integer}
    field :rels, {:array, :integer}
    field :geom, Geo.LineString, srid: 3857
  end
end

编辑 我已经测试了各种查询的性能，以下是最快的：

from r in Route,
        join: h in Hierarchy, on: r.id == h.parent,
        join: s in subquery(
          from s in Segment,
            distinct: true,
            where: fragment("ST_Intersects(?, ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1285982.015631 7217169.814674)', -1), ST_GeomFromText('POINT(2371999.313507 6454022.524275)', -1)), 3857))", s.geom),
            select: %{rel: fragment("unnest(?)", s.rels)}
        ),
        where: s.rel == h.child,
        select: {r.id, r.name}

结果：

计划时间：~0.605 ms 执行时间：~37.232 ms

与上述相同的查询，但join替换为 inner_lateral_join for segments子查询：

规划时间：~1.353 ms 执行时间：~38.518 ms

来自答案的子查询：

规划时间：~1.017 ms 执行时间：~41.288 ms

我以为inner_lateral_join会更快，但事实并非如此。有谁知道如何加快此查询速度？

Answer 1

这是我会尝试的。我还没有验证它是否有效，但它应该指向正确的方向：

segments =
  from s in Segment,
    where: fragment("ST_Intersects(?, ST_SetSrid(ST_MakeBox2D(ST_GeomFromText('POINT(1866349.262143 6886808.978425)', -1), ST_GeomFromText('POINT(1883318.282423 6876413.542579)', -1)), 3857)))", s.geom),
    distinct: true,
    select: %{rel: fragment("unnest(?)", s.rel)}
hierarchy =
  from h in Hierarchy,
    join: s in subquery(segments),
    where: h.child == s.rel,
    distinct: true,
    select: %{parent: h.parent}
routes =
  from r in Route,
    join: h in subquery(hierarchy),
    where: r.top and r.id == h.parent

要记住的事项：

1. 从内部查询开始，然后转到外部查询
2. 要访问子查询的结果，您需要在子查询中选择一个地图
3. Ecto 只允许从和加入的子查询。好消息是，您通常可以将"x IN 子查询"重写为连接
4. 您可以尝试单独运行每个查询，看看它们是否有效