How to preload when association id is not present

Questions / Help
1

Hi, I think this is quite a simple question, but the best way for me to ask this question is to give a fictional example of what my situation is:

I have various Teams, and teams have 1 or 0 TeamSchedules for each day (a planned itinerary for the day).

Each team member needs to fill out their TimeSheet to log what actually took place on each day for that person.

  %TeamSchedule{
    team_id: p,
    date: date,
    ...
  }
  %TimeSheet{
    team_id: t_id,
    date: date,
    # I want to be able to preload this: 
    # team_schedule: %TeamSchedule{} (virtual)
    ...
  }
  %Team{
    id: id
  }

I do not store a team_schedule_id in the TimeSheet schema as I do not want to manage that static link in case of updates/deletions/late submissions.

I am often working with TimeSheet structs, and I want to be able to preload the relevant TeamSchedule if one exists, but I do not know how to achieve this.

Any help much appreciated. Thanks :pray:

2

Did you try adding a has_one :team_schedule, TeamSchedule on your Team schema and a has_one :team_schedule, TeamSchedule, through: :team to the TimeSheet?

I think that then you would be able to preload using the association :slight_smile:

3

Thanks that sounds like a step in the right direction. However I don’t see how the date is being referenced?

To clarify, timesheets exist for many dates and TeamSchedule exist many dates.

4

Oh, I see. I think you can use the where option on has_one with a fragment, check this section of the docs:

https://hexdocs.pm/ecto/Ecto.Schema.html#has_many/3-filtering-associations

Not sure it would work though, maybe you would have to use a has_many :team_schedules, through: :team and filter by date manually

5

Yes I think if it were to work, it looks like where option would be the key. I just don’t know how to reference the current row in the fragment that we pass to the where.

The documentation is pretty thin…

Does anyone have any hints?

6

You could use the on option on joined queries directly. Assuming you have declared an association (has_many) based on the team_id which would automatically only preload team schedules related to the same team id has the time sheet, It would look like this:

from(tsheet in TimeSheet)
|> join(:inner, [tsheet], tsched in assoc(tsheet, :team_schedules), on: tsched.date == tsheet.date)
|> preload([_, tsched], team_schedule: tsched)

It should then filter on both the team id and the date, returning only what you want. I don’t remember if you need to specify again the tsched.team_id == tsheet.team_id. Note that i’m using an inner join, which assumes at least one team schedule is always available, otherwise, it won’t return anything (use a :left instead if that’s not the case).

7

Thanks. I was hoping to be able to define the relationship in the schema and then just use a simple preload: :team_schedule but according to the Ecto.Schema docs, :where cannot be used with :through so I think that probably rules out the simple preload plan…

I will take a look at the preload with designated query.

8

Thanks that worked, I slightly updated to receive a parent timesheet query:

timesheet_query =
  from(tsheet in Timesheet,
    where: tsheet.user_id == 1,
    where: tsheet.date == ^~D[2020-01-02],
    preload: :user
  )

from(tsheet in timesheet_query,
  left_join: tsched in assoc(tsheet, :team_schedule),
  on: tsched.date == tsheet.date,
  preload: [team_schedule: tsched]
)
|> Repo.all()
|> IO.inspect
1 Like
9

For reference, here are the schema files:

defmodule MyApp.Team do
  use Ecto.Schema

  schema "teams" do
    field(:name, :string)
    has_many(:team_schedules, MyApp.TeamSchedule)
    has_many(:timesheets, MyApp.Timesheet)
  end
end

defmodule MyApp.TeamSchedule do
  use Ecto.Schema

  schema "team_schedules" do
    field(:plan, :string)
    field(:date, :date)
    belongs_to(:team, MyApp.Team)
  end
end


defmodule MyApp.Timesheet do
  use Ecto.Schema

  schema "timesheets" do
    field(:description, :string)
    field(:date, :date)
    belongs_to(:team, MyApp.Team)
    belongs_to(:user, MyApp.User)

    has_one(:team_schedule, through: [:team, :team_schedules])
  end
end

In an ideal world I would find a way to simply call:

  from(tsheet in Timesheet,
    where: tsheet.user_id == 1,
    where: tsheet.date == ^~D[2020-01-02],
    preload: :team_schedule
  )

But so far I do not think that is possible.