Ecto preload: problem with multiple prefix setup

Questions / Help
1

I have 3 models: A, B and C.
A has relation many_to_many C join_through B.
B and C had set @repo_prefix to: prefix, but A has not.
When I execute: a = Repo.get A, 1 - it works good.
When I execute: Repo.preload(a, [:cs], prefix: :prefix) - it adds prefix to all tables!
It should not add prefix to A table.

SELECT ... FROM "prefix"."cs" AS c0 INNER JOIN "prefix"."as" AS a1 ON a1."id" = ANY($1) INNER JOIN "prefix"."bs" AS b2 ON b2."a_id" = a1."id" WHERE (b2."c_id" = c0."id") ORDER BY a1."id" [[1]]

I got error:
** (Postgrex.Error) ERROR (undefined_table): relation "prefix.as" does not exist.
How I can solve it?

1 Like
2

Sounds like a prefix: [colkey: :prefix] option should be added, it would be awesome if you submitted a PR to Ecto to add that ability. :slight_smile:

Or at the very least submit an issue request to add it so the design can be looked through. :slight_smile:

3

Thanks a lot! I did not know if the problem is my knowledge, or missing of ecto functionality.
I followed by the rule:

Do not use the issues tracker for help or support requests (try Stack Overflow, IRC or mailing lists, etc).

I will submit PR on Google Groups:

For proposing a new feature, please start a discussion on elixir-ecto.

Edit:
I found this topic: “Preloading association with different prefix”, but don’t know how it’s solved.
In last comment @josevalim had opened at an github issue (actually closed), but in this issue only prefix option is added.
Am I missed something (sorry English is not my primary language) or am I using preload repo method bad? I do not want to assume unnecessary duplicate of topic that looks solved.

4

I can totally see how you think adding the preload to all tables is undesired but I am sure that someone else would think adding preloads only to certain tables is bad. :slight_smile:

The way Ecto works today is that a prefix always affect the whole query, so, in this case, it ma be easier to write the association query by hand.

1 Like
5

So It will not be supported in Ecto at all, right?
If so what I need is to write custom method in my repo where I use Ecto query with join and finish that by Map.put on a variable, right?

a = MyProject.Repo.get A, 1
final_result = a |> MyProject.Repo.custom_preload(:cs, :prefix)
6

That could work, yes.

7

@josevalim: I have done it already, but I have one more question related to this topic:
Why struct_or_structs (from &Ecto.Repo.preload/3 method) is in INNER JOIN fragment?
I mean a INNER JOIN fragment that is related to struct_or_structs. It’s possible to change this fragment to WHERE clause in Ecto code?
From docs:

Preloads all associations on the given struct or structs.

This is similar to Ecto.Query.preload/3 except it allows you to preload structs after they have been fetched from the database.

In case the association was already loaded, preload won’t attempt to reload it.

Is INNER JOIN fragment really needed by Ecto adapters?

Edit:

Actual results:

SELECT ... FROM "prefix"."cs" AS c0 INNER JOIN "prefix"."as" AS a1 ON a1."id" = ANY($1) INNER JOIN "prefix"."bs" AS b2 ON b2."a_id" = a1."id" WHERE (b2."c_id" = c0."id") ORDER BY a1."id" [[1]]

Expected results:

SELECT ... FROM "prefix"."cs" AS c0 INNER JOIN "prefix"."bs" AS b2 ON b2."c_id" = c0."id" WHERE (b2."a_id" = ANY($1)) ORDER BY b2."a_id" [[1]]

What do you think about it?