Applying select on preloads (Ecto)

Questions / Help
1

I have this User schema and a UserRelationship schema that define a tutor-student relationship between users:

User

has_many :students UserRelationship, foreign_key: :instructor_id
has_many :instructors, UserRelationship, foreign_key: :student_id

UserRelationship

field :discipline, Ecto.Enum, values: [:maths, :science]
belongs_to :student, User, foreign_key: :student_id
belongs_to :instructor, User, foreign_key: :instructor_id

Now I want to query an user and get his/hers instructors, but It’d like the query to return a list of users in the instructors field, like so:

%User {
  name: "John Doe",
  instructors: [
    %User{name: "Jane Doe"}, %User{name: "Another John"}
  ]
}

But as it is, if I just query for the user and preload the instructors field, I get a list of UserRelationship structs.
I’ve been playing with different ways to use preload and joins to achieve it, and I’m stuck.
It did however raised some questions.

This is the “closed” I got from an answer:

from(user in User,
  where: user.username == ^username,
  preload: [
    instructors: ^from(relationship in UserRelationship
      where: relationship.discipline == :maths,
      select: relationship.instructor_id
  ]
|> Repo.one()

It returns a list of ID of the instructors, but:

  1. If I try to use relationship.instructor on select: it gives me an error saying the field is a virtual field. I understand association fields are virtual, but why would this be an issue here? If I return just relationship on select, the instructor field will be available there (provided I preload it, of course), so why wouldn’t be available if I try to access it directly?

  2. If I preload instructor and don’t use a select:, everything works as expected (i.e. the field is preloaded and it returns all the rest of the query result with it). But if I keep the preload: and add a select: it gives me an error (even if I don’t reference the preloaded field):

(ArgumentError) you attempted to apply :__struct__ on <<182, 26, 243, 36, 249, 45, 70, 73, 178, 38, 184, 13, 194, 198, 141, 176>>. If you are using apply/3, make sure the module is an atom. If you are using the dot syntax, such as map.field or module.function(), make sure the left side of the dot is an atom or a map

I don’t understand this error, but it seems that just the fact you do a preload in a query if fundamentally changes the query structure?

  1. Finally, is there a way to apply select on preload fields?

I apologize this is huge. Haha :slight_smile:
Thanks!

2

Now I want to query an user and get his/hers instructors, but It’d like the query to return a list of users in the instructors field, like so:

If you want instructors to be an association that contains User structs, you likely want through:

# in user
has_many :student_relationships, UserRelationship, foreign_key: :instructor_id
has_many :instructor_relationships, UserRelationship, foreign_key: :student_id

has_many :students, through: [:student_relationships, :student]
has_many :instructors, through: [:instructor_relationships, :instructor]
2 Likes
3

Oh my god, that’s amazing… I was butchering my query trying to get it to work. That’s such an elegant solution, thanks you so much!

So much to learn it’s scary, haha.

4

Hi @al2o3cr, sorry to bother you in particular, but I thought this could be too directly related to your answer to create a new thread (let me know if that is not the case!)

I was wondering if since through exist to facilitate handling models, is there an specific Ecto mechanism to query through? For instance, iif my UserRelationship schema has some extra fields, is there a way I could query students or instructors based on these extra fields? Or the only way around it is with nesting queries?

I’ve been searching for something like it in the docs, but I might be lacking the terminology to find it (if that exists)

Thank you :slight_smile:

5

For preloading records filtered on the join table, check out Ecto.Query.preload. You’d write the student -> user_relationships -> instructor join explicitly and preload instructors with a query, something like:

from(s in Student, where: ...some student query...,
join: ur in assoc(s, :instructor_relationships),
where: ...some query involving ur...
join: i in assoc(ur, :instructor),
preload: [instructors: i]
2 Likes
6

Oh, right, so will have to work with joins.
I dread this, but I guess this is a great opportunity to ACTUALLY learn this haha :slight_smile:

Thank you so much, again!