postgresql - 如何在ecto中编写复杂的子查询作为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}

结果:

Planning time: ~0.605 ms Execution time: ~37.232 ms

与上面相同的查询，但 join 被 inner_lateral_join 替换为 segments 子查询:

Planning time: ~1.353 ms Execution time: ~38.518 ms

来自答案的子查询:

Planning time: ~1.017 ms Execution time: ~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 只允许 from 和 join 中的子查询。好消息是您通常可以将“x IN 子查询”重写为连接
4. 您可以尝试单独运行每个查询，看看它们是否有效