Help with Preload Query

I have a query I’m trying to preload, but I’m running into a bug where I should get one RosterPosition, but I’m getting two.

Here is the function:

defmodule Ex338.ChampionshipResult do
  @moduledoc false

  use Ex338.Web, :model

  alias Ex338.FantasyPlayer

  schema "championship_results" do
    belongs_to :championship, Ex338.Championship
    belongs_to :fantasy_player, FantasyPlayer
    field :rank, :integer
    field :points, :integer

    timestamps()
  end

  def preload_assocs_by_league(query, league_id) do
    roster_positions =
      from r in Ex338.RosterPosition,
        join: p in assoc(r, :fantasy_player),
        join: cr in assoc(p, :championship_results),
        join: c in assoc(cr, :championship),
        join: f in assoc(r, :fantasy_team),
        where: f.fantasy_league_id == ^league_id,
        where: r.active_at < c.championship_at,
        where: (r.released_at > c.championship_at or is_nil(r.released_at)),
        # where: c.id == ^championship_id, #I need something like this
        preload: [:fantasy_team]

    from cr in query,
      preload: [:championship],
      preload: [fantasy_player: [roster_positions: ^roster_positions]]
  end
end

I need something like the where line I have commented out, but I don’t want to pass the championship_id into the function so I can use it to return multiple championships, e.g. I want to pass this query to Repo.all.

I pasted my failing test along with the other relevant schemas below. The roster position in the test was active during both championships so it gets duplicated during the preload. I considered a hack using limit: 1, but if another team owns the player during the other championship, I can’t guarantee the limit will return the right team.

Any help with rewriting the query would be greatly appreciated!! Thanks!

Axel

test "preloads roster position when there are multiple results" do
  active_date =    CalendarAssistant.days_from_now(-30)
  champ_a_date = CalendarAssistant.days_from_now(-15)
  champ_b_date = CalendarAssistant.days_from_now(-1)

  league = insert(:fantasy_league)
  team = insert(:fantasy_team, fantasy_league: league)
  player = insert(:fantasy_player)

  champ_a = insert(:championship, championship_at: champ_a_date)
  champ_b = insert(:championship, championship_at: champ_b_date)

  pos_a = insert(:roster_position, fantasy_team: team,
    fantasy_player: player, active_at: active_date,
    released_at: nil)

  a_result = insert(:championship_result, fantasy_player: player,
    championship: champ_a, points: 1)
  b_result = insert(:championship_result, fantasy_player: player,
    championship: champ_b, points: 3)

  [result_a, result_b] =
    ChampionshipResult
    |> ChampionshipResult.preload_assocs_by_league(league.id)
    |> Repo.all

  IO.inspect result_a.fantasy_player.roster_positions

  assert Enum.count(result_a.fantasy_player.roster_positions) == 1
  assert Enum.count(result_b.fantasy_player.roster_positions) == 1
end

Here is the result of the test:


[%Ex338.RosterPosition{__meta__: #Ecto.Schema.Metadata<:loaded, "roster_positions">,
  active_at: #Ecto.DateTime<2017-05-11 00:05:37>,
  championship_slots: #Ecto.Association.NotLoaded<association :championship_slots is not loaded>,
  fantasy_player: #Ecto.Association.NotLoaded<association :fantasy_player is not loaded>,
  fantasy_player_id: 120385,
  fantasy_team: %Ex338.FantasyTeam{__meta__: #Ecto.Schema.Metadata<:loaded, "fantasy_teams">,
   champ_with_events_results: #Ecto.Association.NotLoaded<association :champ_with_events_results is not loaded>,
   commish_notes: "",
   draft_picks: #Ecto.Association.NotLoaded<association :draft_picks is not loaded>,
   dues_paid: 0.0,
   fantasy_league: #Ecto.Association.NotLoaded<association :fantasy_league is not loaded>,
   fantasy_league_id: 85195,
   fantasy_players: #Ecto.Association.NotLoaded<association :fantasy_players is not loaded>,
   id: 88779,
   injured_reserves: #Ecto.Association.NotLoaded<association :injured_reserves is not loaded>,
   inserted_at: ~N[2017-06-10 00:05:37.742151],
   owners: #Ecto.Association.NotLoaded<association :owners is not loaded>,
   roster_positions: #Ecto.Association.NotLoaded<association :roster_positions is not loaded>,
   team_name: "Team #0",
   trade_gains: #Ecto.Association.NotLoaded<association :trade_gains is not loaded>,
   trade_loses: #Ecto.Association.NotLoaded<association :trade_loses is not loaded>,
   updated_at: ~N[2017-06-10 00:05:37.742158],
   users: #Ecto.Association.NotLoaded<association :users is not loaded>,
   waiver_position: 1,
   waivers: #Ecto.Association.NotLoaded<association :waivers is not loaded>,
   winnings_adj: 0.0, winnings_received: 0.0}, fantasy_team_id: 88779,
  id: 70411,
  in_season_draft_picks: #Ecto.Association.NotLoaded<association :in_season_draft_picks is not loaded>,
  inserted_at: ~N[2017-06-10 00:05:37.754425], position: "Unassigned",
  released_at: nil, status: "active",
  updated_at: ~N[2017-06-10 00:05:37.754431]},
 %Ex338.RosterPosition{__meta__: #Ecto.Schema.Metadata<:loaded, "roster_positions">,
  active_at: #Ecto.DateTime<2017-05-11 00:05:37>,
  championship_slots: #Ecto.Association.NotLoaded<association :championship_slots is not loaded>,
  fantasy_player: #Ecto.Association.NotLoaded<association :fantasy_player is not loaded>,
  fantasy_player_id: 120385,
  fantasy_team: %Ex338.FantasyTeam{__meta__: #Ecto.Schema.Metadata<:loaded, "fantasy_teams">,
   champ_with_events_results: #Ecto.Association.NotLoaded<association :champ_with_events_results is not loaded>,
   commish_notes: "",
   draft_picks: #Ecto.Association.NotLoaded<association :draft_picks is not loaded>,
   dues_paid: 0.0,
   fantasy_league: #Ecto.Association.NotLoaded<association :fantasy_league is not loaded>,
   fantasy_league_id: 85195,
   fantasy_players: #Ecto.Association.NotLoaded<association :fantasy_players is not loaded>,
   id: 88779,
   injured_reserves: #Ecto.Association.NotLoaded<association :injured_reserves is not loaded>,
   inserted_at: ~N[2017-06-10 00:05:37.742151],
   owners: #Ecto.Association.NotLoaded<association :owners is not loaded>,
   roster_positions: #Ecto.Association.NotLoaded<association :roster_positions is not loaded>,
   team_name: "Team #0",
   trade_gains: #Ecto.Association.NotLoaded<association :trade_gains is not loaded>,
   trade_loses: #Ecto.Association.NotLoaded<association :trade_loses is not loaded>,
   updated_at: ~N[2017-06-10 00:05:37.742158],
   users: #Ecto.Association.NotLoaded<association :users is not loaded>,
   waiver_position: 1,
   waivers: #Ecto.Association.NotLoaded<association :waivers is not loaded>,
   winnings_adj: 0.0, winnings_received: 0.0}, fantasy_team_id: 88779,
  id: 70411,
  in_season_draft_picks: #Ecto.Association.NotLoaded<association :in_season_draft_picks is not loaded>,
  inserted_at: ~N[2017-06-10 00:05:37.754425], position: "Unassigned",
  released_at: nil, status: "active",
  updated_at: ~N[2017-06-10 00:05:37.754431]}]


  1) test preload_assocs_by_league/2 preloads roster position when there are multiple results (Ex338.ChampionshipResultTest)
     test/models/championship_result_test.exs:178
     Assertion with == failed
     code:  Enum.count(result_a.fantasy_player().roster_positions()) == 1
     left:  2
     right: 1
     stacktrace:
       test/models/championship_result_test.exs:206: (test)

Here are my other schemas:

defmodule Ex338.FantasyPlayer do
  @moduledoc false

  use Ex338.Web, :model

  alias Ex338.{RosterPosition, FantasyTeam, Championship, Repo,
               ChampionshipResult, SportsLeague, FantasyPlayer}

  schema "fantasy_players" do
    field :player_name, :string
    field :draft_pick, :boolean
    belongs_to :sports_league, Ex338.SportsLeague
    has_many :roster_positions, Ex338.RosterPosition
    has_many :fantasy_teams, through: [:roster_positions, :fantasy_team]
    has_many :championship_results, Ex338.ChampionshipResult
    has_many :championships, through: [:championship_results, :championships]

    timestamps()
  end
end


defmodule Ex338.RosterPosition do
  @moduledoc false

  use Ex338.Web, :model

  alias Ex338.{FantasyPlayer, Repo}

  schema "roster_positions" do
    belongs_to :fantasy_team, Ex338.FantasyTeam
    field :position, :string
    belongs_to :fantasy_player, Ex338.FantasyPlayer
    field :status, :string
    field :active_at, Ecto.DateTime
    field :released_at, Ecto.DateTime

    timestamps()
  end
end

defmodule Ex338.Championship do
  @moduledoc false
  use Ex338.Web, :model

  alias Ex338.{ChampionshipResult, ChampionshipSlot, ChampWithEventsResult,
               InSeasonDraftPick}

  schema "championships" do
    field :title, :string
    field :category, :string
    field :waiver_deadline_at, Ecto.DateTime
    field :trade_deadline_at, Ecto.DateTime
    field :championship_at, Ecto.DateTime
    field :year, :integer
    field :in_season_draft, :boolean
    belongs_to :sports_league, Ex338.SportsLeague
    has_many :championship_results, Ex338.ChampionshipResult
    has_many :fantasy_players, through: [:championship_results, :fantasy_player]


    timestamps()
  end
end

I need to pass a value from my outside query into the preload query. Can you do something like this where I pass cr.championship_id into my preload query?

  def preload_assocs_by_league(query, league_id) do
    from cr in query,
      order_by: [desc: cr.points, asc: cr.rank],
      preload: [:championship],
      preload: [fantasy_player: [roster_positions: (
        from r in Ex338.RosterPosition,
          join: p in assoc(r, :fantasy_player),
          join: sub_cr in assoc(p, :championship_results),
          join: c in assoc(sub_cr, :championship),
          join: f in assoc(r, :fantasy_team),
          where: sub_cr.championship_id == cr.championship_id,
          where: f.fantasy_league_id == ^league_id,
          where: r.active_at < c.championship_at,
          where: (r.released_at > c.championship_at or is_nil(r.released_at)),
          preload: [:fantasy_team]
      )]]
  end

It doesn’t work (see error below), but is there something similar I can do?

== Compilation error on file web/models/championship_result.ex ==
** (Ecto.Query.CompileError) `from(r in Ex338.RosterPosition, join: p in assoc(r, :fantasy_player), join: cr in assoc(p, :championship_results), join: c in assoc(cr, :championship), join: f in assoc(r, :fantasy_team), where: f.fantasy_league_id() == ^league_id, where: r.active_at() < c.championship_at(), where: r.released_at() > c.championship_at() or is_nil(r.released_at()), preload: [:fantasy_team])` is not a valid preload expression. preload expects an atom, a list of atoms or a keyword list with more preloads as values. Use ^ on the outermost preload to interpolate a value
    expanding macro: Ecto.Query.preload/3
    web/models/championship_result.ex:48: Ex338.ChampionshipResult.preload_assocs_by_league/2
    expanding macro: Ecto.Query.from/2
    web/models/championship_result.ex:48: Ex338.ChampionshipResult.preload_assocs_by_league/2

If anyone else runs into an issue where they want to preload results from within a query, nested associations can be preloaded like this:

preload: [fantasy_player: {p, roster_positions: {r, fantasy_team: f}}]

Here is the entire function:

  def preload_assocs_by_league(query, league_id) do
    from cr in query,
      join: c in assoc(cr, :championship),
      join: p in assoc(cr, :fantasy_player),
      left_join: r in RosterPosition, on: r.fantasy_player_id == p.id
        and r.active_at < c.championship_at
        and (r.released_at > c.championship_at or is_nil(r.released_at)),
      left_join: f in FantasyTeam, on: f.id == r.fantasy_team_id,
      where: f.fantasy_league_id == ^league_id or is_nil(f.fantasy_league_id),
      order_by: [desc: cr.points, asc: cr.rank],
      preload: [:championship],
      preload: [fantasy_player: {p, roster_positions: {r, fantasy_team: f}}]
  end
1 Like