Ecto: Join query with sub query in on clause

Questions / Help
, ,
1

Hi,

I’m trying to translate the following query into Ecto:

SELECT p.id,
      p.name,
      ds.count
FROM packages p
LEFT OUTER JOIN download_stats ds ON ds.package_id = p.id
AND ds.count =
 (SELECT max(COUNT)
  FROM download_stats
  WHERE package_id = p.id)
ORDER BY COUNT DESC
LIMIT 10

Specifically, I’m struggling to create the sub query in the on clause. How would that work? The query is supposed to return the most downloaded packages based on their latest download stat.
The schemas look like this:

  schema "packages" do
  # [...]
    has_many :download_stats, Empad.DownloadStat
    timestamps()
  end

  schema "download_stats" do
    field :count, :integer
    field :tracked_at, :date
    belongs_to :package, Empad.Package
    timestamps()
  end
2

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

3

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)
4

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.

5

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)}”