# Preloading a join inside a from function

**URL:** <https://forum.elixirforum.com/t/preloading-a-join-inside-a-from-function/37606>\
**Category:** Questions\
**Tags:** ecto\
**Created:** [February 17, 2021, 8:38am UTC](https://forum.elixirforum.com/t/preloading-a-join-inside-a-from-function/37606 "2021-02-17T08:38:01Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tjdam](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/tjdam/32/20256_2.png) [@tjdam](https://forum.elixirforum.com/u/tjdam)\
**Post date:** [February 17, 2021, 8:38am UTC](https://forum.elixirforum.com/t/preloading-a-join-inside-a-from-function/37606/1 "2021-02-17T08:38:01Z")

</div>

Hi!

I have this somewhat convoluted query (for me at least) that fetches an `User` based on a given `Group` id he’s part of and if it has an assigned role in this `Group`. Like so:

```elixir
from(g in Group,
  where: g.id == ^g.id
  inner_join: r in assoc(g, :user_group_relationships),
  on: not is_nil(r.role),
  inner_join: u in assoc(r, :user),
  select: u)

```

Then I end up with this list of Users by running the query through `Repo.all`, and call `Repo.preload` to preload a field in `User` schema.

I do that because I couldn’t figure out how to include this preload bit in that `from` function before.

I have two questions;

1. Is doing that (running a query with `Repo.all` and then preloading) inefficient or does Ecto merge everything in one operation behind the scenes anyways?

2. How to preload an `User` field in this case?

Thanks!

---

<div class="post-metadata">

**Author:** ![LostKobrakai](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/lostkobrakai/32/3072_2.png) [@LostKobrakai](https://forum.elixirforum.com/u/LostKobrakai)\
**Post date:** [February 17, 2021, 8:48am UTC](https://forum.elixirforum.com/t/preloading-a-join-inside-a-from-function/37606/2 "2021-02-17T08:48:43Z")

</div>

1. No it does not. But doing two queries in not by by default.

2. It’s likely simpler to reverse the query:

```elixir
from u in User, 
  join: r in assoc(u, :user_group_relationships), 
  on: not is_nil(r.role),
  join: g in assoc(r, :group),
  where: g.id == ^id,
  preload: […]

```

---

<div class="post-metadata">

**Author:** ![tjdam](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/tjdam/32/20256_2.png) [@tjdam](https://forum.elixirforum.com/u/tjdam)\
**Post date:** [February 17, 2021, 9:39am UTC](https://forum.elixirforum.com/t/preloading-a-join-inside-a-from-function/37606/3 "2021-02-17T09:39:50Z")

</div>

Oh my god, hahaha 😅

Yeah, I guess that makes A LITTLE (a lot) more sense, thank you!

But the thing is, I was trying to bring the preload to the `from` function because I wanted to create a custom map using `select` in the end of the query, and I thought I wouldn’t be able to preload it afterwards if the result of the query would not be a Ecto struct.

But if I go:

```elixir
from u in User, 
  join: r in assoc(u, :user_group_relationships), 
  on: not is_nil(r.role),
  join: g in assoc(r, :group),
  where: g.id == ^id,
  preload: [...],
  select: %{name: u.name, role: r.role}

```

I get:

```elixir
    ** (Ecto.QueryError) the binding used in `from` must be selected in `select` when using `preload` in query

```

I don’t quite understand what it means…

I know the preload works cause if I just use `select: u` it returns the list of `User` structs fine and if I select a tuple (i.e. `select: {u, r.role}`) I get the results I want, but then I have to map the result to create the new map for each element returned, which doesn’t seem right to me.

Is there a way to do that new map straight from `select`?

---

<div class="post-metadata">

**Author:** ![tjdam](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/tjdam/32/20256_2.png) [@tjdam](https://forum.elixirforum.com/u/tjdam)\
**Post date:** [February 17, 2021, 9:56am UTC](https://forum.elixirforum.com/t/preloading-a-join-inside-a-from-function/37606/4 "2021-02-17T09:56:54Z")

</div>

> [@tjdam](#):
>
> I don’t quite understand what it means…

I realized it means I wasn’t including the preload in the select… obviously.

I don’t know if its unproductive to keep writing in this thread since my issues seem to be slightly moving away from the original question, but the issue I’m having now is that by using `select: %{name: u.name, preloaded_field: u.preloaded_field}` I get an error saying the field I’m preloading is a “virtual field”.

Isn’t that always the case for preloads, though?

My reasoning is that because preload happens after the query I can’t use the result of a preload to form the result of my query, is that right?  
In that case, I’d always have to query, preload and then map the results get a final result?
