Has_many association with multiple levels

Phoenix Forum Questions / Help
,
1

I have a User schema, which is connected to a TimeEntry schema via a has_many association.
The TimeEntry schema has a has_many association to the Member schema, which in turn belongs_to both User and Employee schemas (via user_id and employee_id columns respectively). A time entry member can be either a user or an employee, but not both.

Now, I want to design a query that will preload both a user’s own time entries, as well as time entries on which it is a Member (it is not possible for a user to be both the owner of a time entry and one of its members). The structs returned by Repo.all should have this shape:

[
    %User{
        first_name: "Bob",
        last_name: "Marley",
        time_entries: [%TimeEntry{}, %TimeEntry{}],
        member_time_entries: [%TimeEntry{}, %TimeEntry{}]
    }
]

What is the best way to go about this?

2

Turned out to be easy. I added a has_many :members association to the User schema, and then has_many :member_time_entries, through: [:members, :time_entry]. Special thanks to @Benjy on the Ecto Slack channel!

3 Likes