Ecto: Join query with sub query in on clause

Can you try something like

ds_counts = (from ds in DownloadStat, select: max(count))
(from p in Package,
  left_join: ds in subquery(ds_counts), on: p.id == ds.package_id,
  select: %{p_id: p.id, p_name: p.name, ds_count: ds.count}
) |> Repo.all

I had another working query. Try with above one and do add your working query in reply

That does not work, I’m getting the following Ecto error @tenzil:

** (Ecto.SubQueryError) the following exception happened when compiling a subquery.

    ** (Ecto.QueryError) subquery/cte must select a source (t), a field (t.field) or a map, got: `[max(&0.tracked_at())]` in query:
    
    from d0 in Empad.DownloadStat,
      select: [max(d0.tracked_at)]
    

The subquery originated from the following query:

from p0 in Empad.Package,
  left_join: d1 in subquery(from d0 in Empad.DownloadStat,
  select: [max(d0.tracked_at)]),
  on: p0.id == d1.package_id,
  select: %{p_id: p0.id, p_name: p0.name, ds_count: d1.count}

    (ecto 3.9.0) lib/ecto/repo/queryable.ex:211: Ecto.Repo.Queryable.execute/4
    (ecto 3.9.0) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    iex:36: (file)

Not sure, but I think you need to add the :id to the :select In the sub query, so it can be used to join on.

yes @Hermanverschooten , we need something like this
ds_counts = (from ds in DownloadStat, select: %{package_id: ds.package_id, count: max(ds.count)}, group_by: ds.package_id, limit: 10, order_by: [count: :desc])

@nTraum , do try this out, and maybe with other fields or options on
“select: %{package_id: ds.package_id, count: max(ds.count)}”