Help with Ecto Query (join, subquery, where, preload)

Questions / Help
1

I have the following query:

defmodule ChampionshipResults do
....
  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 assoc(p, :roster_positions),
      left_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: [:championship],
      preload: [fantasy_player: {p, roster_positions: {r, fantasy_team: f}}]
  end
end

fantasy_player has_many roster_positions

I want to return all championship_results preloaded with any roster_positions meeting my where clauses criteria. However, championship_results that do not have any roster_positions are currently not returned.

In other functions, I’ve used the pin operator to preload a roster_position query, but I can’t figure out how to do it since I need dates from my championship table which I need to join through championship_results.

Does anyone have any recommendations for how I can write the query?

Thanks!
Axel

2 Likes
2

I figured out the roster_position query I could preload with the pin operator and this code seems to be working. I’m curious if there is a different way some people would do it?

defmodule ChampionshipResults do
....
  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)),
        preload: [:fantasy_team]

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

The issue is in your initial query in the where condition:

where: r.active_at < c.championship_at,

When the rooster position is not present it’s effectively WHERE NULL < some_date, which is always false, thus excluding the row from being returned. Adding is_nil check (like you do for the condition below) should solve the issue:

where: (r.active_at < c.championship_at or is_nil(r.active_at)),
3 Likes
4

Ingenious, thank you!

1 Like