Ecto CTE queries without a prefix

Questions / Help
1

Hi all,

I’m having trouble using CTE queries in combination with prefixes. I have a multi tenancy Phoenix application that uses recursive queries to get a tree. The problem is that when I give a prefix to the query, this also gets used for the CTE query.

Is there any way to say that the CTE query does not need to use the prefix?

I.e.

category_tree_initial_query =
  Category
  |> where([c], is_nil(c.parent_id))

category_tree_recursion_query =
  Category
  |> join(:inner, [c], ct in "category_tree", on: c.parent_id == ct.id)

category_tree_query =
  category_tree_initial_query
  |> union_all(^category_tree_recursion_query)

Product
|> recursive_ctes(true)
|> with_cte("category_tree", as: ^category_tree_query)
|> join(:inner, [p], c in "category_tree", on: c.id == p.category_id)
|> Repo.all(prefix: "test")

Is generating the following SQL:

WITH RECURSIVE "category_tree" AS (
    SELECT * FROM "test"."categories" WHERE c.parent_id IS NULL
    UNION ALL
    SELECT * FROM "test"."categories" AS c JOIN "test"."category_tree" AS ct ON c.parent_id == ct.id
)
SELECT ...
FROM "test"."products" AS l0
    INNER JOIN "test"."category_tree" AS r1 ON r1."id" = l0."category_id"

Gives the following error:
ERROR 42P01 (undefined_table) relation “test.category_tree” does not exist

2 Likes
2

Did you solved your problem?

3

Unfortunatly I did not. The only way I found to fix the issue is by typing the query manually and executing it through Ecto.Adapters.SQL.query!/3. I did not like this solution at all and gave up.

At one point in time unsafe_fragment/1 was available and would have been perfect for this situation, but it has been removed.

If anyone has any idea how to do this in an elegant way, I would appreciate it very much.

4

I put an issue at Ecto Github (https://github.com/elixir-ecto/ecto/issues/3387).

According them, this is by design and can be solved doing this: |> with_cte("category_tree", as: ^%{category_tree_query | prefix: "another one"})

After the change it still didn’t work for me, so I has an idea to use fragment in the JOIN.

So, the complete code is this:

category_tree_initial_query =
  Category
  |> where([c], is_nil(c.parent_id))

category_tree_recursion_query =
  Category
  |> join(:inner, [c], ct in fragment("\"category_tree\""), on: c.parent_id == ct.id)

category_tree_query =
  category_tree_initial_query
  |> union_all(^category_tree_recursion_query)

{"category_tree", Category}
|> recursive_ctes(true)
|> with_cte("category_tree", as: ^%{category_tree_query | prefix: "test"}) 
|> Repo.all()

In my case I don’t use Product table, but I thing you can solve your case to with this tip.

2 Likes
5

Thank you! I like the fix with the use of fragments, unfortunately this does not work in my case.

I have 3 CTE query’s and use a subquery in the last query. The problem is that I can’t use the fragment in the subquery. I tried doing it with the fix on github, but I can’t say that it does not need to use a prefix (nil is default, so it just uses the prefix. An empty string also does not work).

starting =
  from(a in Accommodation,
    select: %{id: a.id, parent_id: a.parent_id},
    where: a.id == ^accommodation_id
  )

descendants =
  from(s in fragment("\"starting\""),
    select: %{id: s.id, parent_id: s.parent_id},
    union_all: ^from(a in Accommodation,
      select: %{id: a.id, parent_id: a.parent_id},
      join: d in fragment("\"descendants\""), on: a.parent_id == d.id
    )
  )

ancestors =
  from(a in Accommodation,
    select: %{id: a.id, parent_id: a.parent_id},
    where: a.id in subquery(from(s in fragment("\"starting\""), select: s.parent_id)), # <- Throws error
    union_all: ^from(a in Accommodation,
      select: %{id: a.id, parent_id: a.parent_id},
      join: d in fragment("\"ancestors\""), on: a.id == d.parent_id
    )
  )
6

Can you provide me the final query that you wish?
Maybe I can help you.

7

I just ran into this problem and was able to workaround it using the fragment trick, so thanks @alexcassol

I did also run into the issue @jurre did because it is not possible to select from a fragment, so the prefix was still being erroneously attached in that case. For that I ended up joining a table to the CTE like so: SomeTable.join(:inner, [t], c in fragment("some_CTE"), on: c.id == t.id)

Feels pretty hacky but it works.

1 Like