How to get the top X results of a given category using Ecto?

Questions / Help
8

Reading from this post about lateral joins:

I believe what I want in SQL could be something like this:

(assuming file_info_with_counts is the inner join of all_counts with file_info):

SELECT home_id, path, first_item, other_items FROM
  (SELECT home_id, path, min(item_id) AS first_item FROM file_info_with_counts GROUP BY home_id, path) o1
  INNER JOIN LATERAL
  (SELECT id, item_id AS other_items
   FROM file_info_with_counts
   WHERE home_id = o1.home_id and path = o1.path AND item_id > o1.item_id
   ORDER BY item_id ASC LIMIT 2)
   o2 ON true;

Now, if this makes sense, I need to find a way to convert it into Ecto.