Programmatically Preload has_many, through: in Elixir Ecto

Question

I am attempting to programmatically attach a preload to a query for one of my models that has a has_many, through: relationship.

My modules:

defmodule MyApp.Chemical do
  use MyApp.Web, :model

  schema "chemicals" do
    has_many :company_chemicals, MyApp.CompanyChemical
    has_many :companies, through: [:company_chemicals, :companies]

    field :name, :string
  end

  def with_companies(query) do
          from  chem      in query,
     left_join: comp_chem in assoc(chem, :company_chemicals),
          join: company   in assoc(comp_chem, :company),
       preload: [companies: company]
  end

end

defmodule MyApp.Company do
  use MyApp.Web, :model

  schema "companies" do
    has_many :company_chemicals, MyApp.CompanyChemical
    has_many :chemicals, through: [:company_chemicals, :chemicals]

    field :name, :string
  end
end

defmodule MyApp.CompanyChemical do
  use MyApp.Web, :model

  schema "company_chemicals" do
    belongs_to :chemical, MyApp.Chemical
    belongs_to :company, MyApp.Company
  end
end

With these models, the MyApp.Chemical.with_companies/1 works as expected returning a query that will produce a Chemical with a populated :companies field, but I was trying to make a function like the following to programmatically preload fields through an association table:

def preload_association(query, local_assoc, assoc_table, assoc_table_assoc) do
       from  orig in query,
  left_join: association_table in assoc(orig, ^assoc_table),
       join: distal in assoc(association_table, ^assoc_table_assoc),
    preload: [{^local_assoc, distal}]
end

However, this function will not compile due to the preload: [{^local_assoc, distal}] line.

How can one preload an assoc that is a has_many through? Thanks.

Answer 1

Are you filtering your joins in any way? Because, if you are not, you should call preload instead:

query = from c in MyApp.Company, where: ...
companies = Repo.all(query)
companies_with_chemicals = Repo.preload(companies, :chemicals)

Or:

query = from c in MyApp.Company, preload: :chemicals
companies_with_chemicals = Repo.all(query)

It is going to be faster too as it does two separate queries, reducing the overall result set size to be processed from companies_size * chemicals_size to companies_size + chemicals_size.

Note you should also be able to join on a has_many :through.