Help translating recursive cte query to Ecto

Questions / Help
1

Heh, I have another cte query that I’m struggling with.

WITH RECURSIVE recent_applications AS (
  (
    SELECT application_id, created_at
    FROM submissions
    WHERE award_cycle_id = 66
      AND opportunity_id = 52053
    ORDER BY application_id, created_at DESC
    LIMIT 1
  )
  UNION ALL
  SELECT l.*
  FROM recent_applications r
  CROSS JOIN LATERAL (
    SELECT s.application_id, s.created_at
    FROM submissions s
    WHERE s.award_cycle_id = 66
      AND s.opportunity_id = 52053
      AND s.application_id > r.application_id
    ORDER BY s.application_id, created_at DESC
    LIMIT  1
  ) l
  WHERE r.application_id IS NOT NULL
)
SELECT * FROM recent_applications
WHERE application_id IS NOT NULL;

Here’s my code…

    recursive_query = Ecto.Query.from(r in "recent_applications", as: :recent_applications,
      cross_lateral_join: subquery(inner_query),
      where: not is_nil(r.application_id),
      select: %{id: r.id, application_id: r.application_id}
    )

    union_query =
      initial_query
      |> Ecto.Query.union_all(^recursive_query)

    Submission
    |> Ecto.Query.recursive_ctes(true)
    |> Ecto.Query.with_cte("recent_applications", as: ^union_query)
    |> Ecto.Query.where([r], not is_nil(r.application_id))
    |> Ecto.Query.select([r], [r.id, r.application_id])

Postgres complains about “recent_applications” table not existing.

** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "__aw_schema_$dev.recent_applications" does not exist

    query: WITH RECURSIVE "recent_applications" AS (SELECT ss0."id" AS "id", ss0."award_cycle_id" AS "award_cycle_id", ss0."opportunity_id" AS "opportunity_id", ss0."application_id" AS "application_id", ss0."category_id" AS "category_id", ss0."import_id" AS "import_id", ss0."answers" AS "answers", ss0."answer_sets" AS "answer_sets", ss0."import_row" AS "import_row", ss0."created_at" AS "created_at" FROM "__aw_schema_$dev"."submissions" AS ss0 WHERE (ss0."award_cycle_id" = $1) UNION ALL (SELECT sr0."id", sr0."application_id" FROM "__aw_schema_$dev"."recent_applications" AS sr0 CROSS JOIN LATERAL (SELECT sss0."id" AS "id", sss0."award_cycle_id" AS "award_cycle_id", sss0."opportunity_id" AS "opportunity_id", sss0."application_id" AS "application_id", sss0."category_id" AS "category_id", sss0."import_id" AS "import_id", sss0."answers" AS "answers", sss0."answer_sets" AS "answer_sets", sss0."import_row" AS "import_row", sss0."created_at" AS "created_at" FROM "__aw_schema_$dev"."submissions" AS sss0 WHERE ((sss0."award_cycle_id" = $2) AND (sss0."application_id" > sr0."application_id")) ORDER BY sss0."application_id", sss0."created_at" DESC LIMIT 1) AS ss1 WHERE (NOT (sr0."application_id" IS NULL))) ORDER BY ss0."application_id", ss0."created_at" DESC LIMIT 1) SELECT s0."id", s0."application_id" FROM "__aw_schema_$dev"."submissions" AS s0 WHERE (NOT (s0."application_id" IS NULL))

Any help would be greatly appreciated! Thank you.

2

CTE queries in ecto are a little messed up. I can’t remember from the top of my head how you would usually approach them, however here is a working example: lib/ssl_moon/hosts_and_paths.ex · main · SSL MOON / SSL MOON · GitLab

From what I remember, this query is forming a URI path from entries that have parent_id recursively.

PS: You should be able to run this project locally, with default configuration there is a sqlite database that has data in it, it can be very useful for debugging.

1 Like
3

I actually figured it out. The key for me was manually managing prefixes. Also, the “base case” needs to be in parenthesis, and I couldn’t figure out how to do that, so I just made it a subquery instead.

What I mean by that is the SQL should look like this:

WITH RECURSIVE latest (
  (
    SELECT *
    FROM ...
    ORDER BY ...
  )
  UNION ALL
  SELECT ( ... )
)
...

But Ecto produced SQL like this:

WITH RECURSIVE latest (
  SELECT *
  FROM ...
  UNION ALL
  SELECT ( ... )
  ORDER BY ...
)

Here’s my working code…

    prefix = Datastores.current!()
    |> repo.client_to_schema()

    initial_query = Ecto.Query.from(s in Submission,
      prefix: ^prefix,
      where: s.award_cycle_id == ^award_cycle_id
        and s.opportunity_id == ^opportunity_id,
      order_by: [s.application_id, desc: s.created_at],
      limit: 1
    )

    inner_query = Ecto.Query.from(s in Submission,
      prefix: ^prefix,
      where: s.award_cycle_id == ^award_cycle_id
        and s.opportunity_id == ^opportunity_id
        and s.application_id > parent_as(:latest).application_id,
      order_by: [s.application_id, desc: s.created_at],
      limit: 1
    )

    recursive_query = Ecto.Query.from(s in {name, Submission}, as: :latest,
      cross_lateral_join: c in ^Ecto.Query.subquery(inner_query),
      where: not is_nil(s.application_id),
      select: c
    )

    initial_query
    |> Ecto.Query.subquery()
    |> Ecto.Query.union_all(^recursive_query)

Notice the manually handling of prefixes and making a subquery out of initial_query to get the effects of parenthesis around the base case.

4 Likes