Is it possible to write a preload query for this?

I have a posts table and a post_versions table. 1 post many post_versions. I would like to query for a post record preloading the latest version for each language something list this:

select distinct on (pv.language) language, inserted_at, id,content 
from post_versions pv 
order by language, inserted_at desc

But i have having problem trying to form the preload query. The one below is my attempt that does not work. I have to somehow work in Post.id

Repo.get!(Post, id)
|> Repo.preload([
versions: build_preload_versions_query(),
])

defp build_preload_versions_query() do
  from(
    pv in PostVersion,
    select: %{
      :site_code => fragment("distinct on (?) ?", pv.language, pv.language),
      :id => pv.id,
      :content => pv.content,
      :inserted_at => pv.inserted_at
    },
    order_by: [asc: pv.language, desc: pv.inserted_at]
  )
end

Try this:

Repo.get!(Post, id)
|> Repo.preload([
  # The `^` character tells Ecto that you're passing in a variable from
  # outside the query. It's *not* a standard Elixir pin operator.
  versions: ^build_preload_versions_query(id),
])

defp build_preload_versions_query(post_id) do
  from(
    pv in PostVersion,
    # use the `^` character again for the same reason as above
    where: pv.post_id == ^post_id,
    select: %{
      :site_code => fragment("distinct on (?) ?", pv.language, pv.language),
      :id => pv.id,
      :content => pv.content,
      :inserted_at => pv.inserted_at
    },
    order_by: [asc: pv.language, desc: pv.inserted_at]
  )
end

Please let me know if that helps.

thank for taking a look at this. I am getting this error

(Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "distinct"

I think you would have to change your query a bit, it looks like distinct works a little differently than your attempt. Maybe something like this:

    select: %{
      :site_code => pv.language,
      :id => pv.id,
      :content => pv.content,
      :inserted_at => pv.inserted_at
    },
    distinct: pv.language

https://hexdocs.pm/ecto/Ecto.Query.html#distinct/3