Ecto: Subqueries + Joins + Prefixes

[brandonparsons]

Good morning,

I'm looking to use a subquery in a join, but that join needs to look inside a schema to find the proper tables.

Despite adding support for prefixes in subqueries (https://github.com/elixir-ecto/ecto/commit/246a81826d891836880805ea128b91e458d5dc7c), it appears to me that attempting to join still does not work.

Am I missing something obvious?

I've tried something like the following:

prefix = "my-prefix"

count_query =
  from msg in InAppMessage,
    group_by: msg.in_app_conversation_id,
    select: %{in_app_conversation_id: msg.in_app_conversation_id, count: count("*")}

full_query = 
  from conv in query,
    join: counts in subquery(count_query, prefix: ^prefix),
    on: counts.in_app_conversation_id == conv.id,
    select_merge: %{message_count: counts.count}

Repo.all(full_query, prefix: prefix)

Which errors out as:

** (Ecto.Query.CompileError) malformed join `subquery(count_query, prefix: prefix)` in query expression

If I don't include the prefix: ^a_prefix portion in creation of the subquery, it tries to join in the public schema and can't find the correct tables, even if the prefix is set in Repo.all.

[josevalim]

@brandonparsons what happens if you do:

join: counts in ^subquery(count_query, prefix: prefix)

We have added the ability to write subquery(...) without ^ as a convenience but it seems to have just backfired. :)

[brandonparsons]

@josevalim That works. Sorry, I did not think to try it that way, thought I tried all possible combinations before submitting the issue! 👍

[josevalim]

Don't be sorry. It was definitely a bug!