CODEWITHSUNDEEP
postgresqlelixirphoenix-frameworkecto
luzny
luzny

Reputation: 2400

How to write sophisticated subquery as from clause in ecto?

I'm trying to write in Ecto syntax following sql query, how to write subquery after FROM hierarchy, line, it's in from clause, but I doubt if it is possible in Ecto? I wonder if I can perform such query with use of table joins or even lateral joins without performance loss with the same effect?

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)

I've stuck on following code:

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

Schemas:

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

EDIT I've tested performance of various queries and this below is fastest:

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}

Results:

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

The same query as above but join replaced by inner_lateral_join for segments subquery:

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

Subqueries from answer:

Planning time: ~1.017 ms Execution time: ~41.288 ms

I thought that inner_lateral_join would be faster but it isn't. Does anybody know how to speed up this query?

Upvotes: 1

Views: 1617

Answers (1)

José Valim
José Valim

Reputation: 51429

Here is what I would try. I haven't verified it works but it should point to the proper direction:

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

Things to keep in mind:

  1. Start from the inner query and go to the outer one
  2. To access the result of a subquery, you need to select a map in the subquery
  3. Ecto only allows subqueries in from and join. The good news is that you can usually rewrite "x IN subquery" as a join
  4. You can try to run each query individually and see if they work

Upvotes: 4

Related Questions