How to preload item outside of from binding

I have a query where I want to select an association only, how do I preload an item within the same query?

ie

from(p in Post,
  join: user in assoc(p, :user),
  where: p.user_id == ^user_id,
  select: user,
  preload: [:profile]
)

returns
(Ecto.QueryError) the binding used in 'from' must be selected in 'select' when using 'preload' in query

You only want the user? Do the other way around.

I assume your database structure is: Post -> Profile and Post -> User

from u in User,
  join: p in assoc(u, :posts),
  where: p.user_id == ^user_id,
  preload: [posts: :profile]

Or, depending on your DB structure:

from u in User,
  where: u.id == ^user_id,
  preload: [posts: :profile]

In this example actually I want the user profile, so Post belongs to a User and Profile belongs to a User.
Post -> User -> Profile

@Codball is there an id of a specific post you’re looking at? Right now the query you’re asking for would load all posts in the whole database.

You want the profile and you have the user ID?

from p in Profile,
  where: p.user_id == ^user_id

Point is, your original error is because the schema specified in the from clause is what’s returned. You can’t query a Post and have it return only the User, since Elixir is trying to return a list of %Post{} in the first place

If you want a User, use from u in User, if you want a Profile, from p in Profile, and navigate the table references from there.

If you are still confused, please try and replicate what you want to do with a working SQL query and share it with us.

1 Like

Maybe it’s a bad example, I am likely trying to take an unnecessary/impossible shortcut to avoid diving into implementation, was still wondering if this method is possible though. I will likely just have to rewrite the query to start through a user.

ie

from(u in User,
  join: post in assoc(u, :post)
  where: u.id == ^user_id,
  preload: :profile
)

again, probably not the best example, I am more concerned about preload's functionality. I thought I remember seeing you could preload any joined/named binding, but that does not look like the case.