Ecto: Use select / select_merge in a join query

Questions / Help
1

Hey!

I wonder if it is possible to do a select or select_merge in a join query.

User table: first_name, last_name
Post table: title, body, user_id

Now, I want to join the user table into the post table. In addition, I want to use a fragment to compose the first_name and last_name of the user to a full_name.

The result should be something like this:

%Post {
  title: "",
  body: "",
  user: %User {
    first_name: "",
    last_name: "",
    full_name: ""
   }
}

When querying the user, I could do:

from u in User, 
  select_merge: %{full_name: fragment("concat(?, ' ', ? )", u.first_name, u.last_name)}

But how would I achieve such concatenation in a join query?

The ecto select_merge documentation says - Ecto.Query — Ecto v3.11.1

select_merge cannot be used to set fields in associations, as associations are always loaded later, overriding any previous value.

So I wonder whether it is possible at all. Is there another to approach to get the query result I stated above?

Best regards

1 Like
2

The nearest thing I can imagine:

iex> query =
...>   from u in User,
...>     join: p in Post,
...>     on: p.user_id == u.id,
...>     where: p.title == "Foo",
...>     select: %{
...>       user: %{
...>         full_name: fragment("concat(?, ' ', ? )", u.first_name, u.last_name),
...>         first_name: u.first_name,
...>         last_name: u.last_name
...>       },
...>       title: p.title,
...>       body: p.body
...>     }
iex> Repo.all(query)
[
  %{
    body: "No",
    title: "Foo",
    user: %{first_name: "Jo", full_name: "Jo Do", last_name: "Do"}
  }
]
3

You could use this approach for select_merge with a virtual field full_name on User to build a query that loads Users:

and then pass that to preload like:

from p in Post, preload: [user: ^<QUERY THAT POPULATES full_name>]

That would give you Posts with a User that has full_name populated.

3 Likes