I’m trying to preload data into a many_to_many relationship using a custom query to get at a complex set of data. (See: https://hexdocs.pm/ecto/Ecto.Query.html#preload/3-preload-functions for the docs)
The preload function gets executed and the data returned is correct. (By inspecting the results of the reload function). I’ve included the parent id (franchise_id) mapped into a virtual field of the schema returned. But… it seems that the data is thrown away and not mapped into my Franchise model. It comes out with an empty array on the franchise.franchise_groups property.
Any ideas? What am I missing to get this data into the Franchise struct?
Thanks.
Code snippets:
defmodule Molly.FranchiseGroup do
use Molly.Schema, :model
schema "franchise_groups" do
field :name, :string
field :start_season, :integer
field :end_season, :integer, default: 3000
belongs_to :parent, Molly.FranchiseGroup
belongs_to :league, Molly.League
many_to_many :franchises, Molly.Franchise, join_through: Molly.FranchiseAffiliation
field :path, {:array, :integer}, virtual: true
field :franchise_id, :integer, virtual: true
timestamps()
end
end
defmodule Molly.Franchise do
use Molly.Schema, :model
schema "franchises" do
field :name, :string
has_many :teams, Molly.Team
# This is kind of a fake for preloading groups in a hierarchy
many_to_many :franchise_groups, Molly.FranchiseGroup, join_through: Molly.FranchiseAffiliation
timestamps()
end
end
defmodule FranchiseQuery do
def preload_groups(query, season) do
groups_preload = fn ids -> groups_query(ids, season) |> Repo.all() end
from f in query,
preload: [franchise_groups: ^groups_preload]
end
defp groups_query(ids, season) do
from fg in FranchiseGroup,
inner_join: cte in fragment("""
... -- Trust me, a complex recursive CTE query that returns the data I need
""", ^ids), on: fg.id == cte.id,
where: fg.start_season <= ^season and fg.end_season >= ^season,
select: %{ fg | path: cte.path, franchise_id: cte.franchise_id}
end
end
