"Top N per Group" example in Dataloader

[mydearxym]

Currently the xxx-users loader is not using Dataloader , because the limit and offset in the query will affect the whole result set and not each association, this can be solved by a "Top N per Group" case, but the syntax is ugly ...

reference links:

https://elixirforum.com/t/preloading-top-comments-for-posts-in-ecto/1052/8
elixir-ecto/ecto#2281
https://spin.atomicobject.com/2016/03/12/select-top-n-per-group-postgresql/
https://stackoverflow.com/questions/40529699/how-to-select-id-with-max-date-group-by-category-in-ecto-query-with-phoenix
http://www.achraf-sallemi.com/select-top-n-per-group-in-postgresql/

[mydearxym]

big thanks to my backend workmate, the raw sql is:

select *
  from(
select rank() over(partition by cid
 order by pinserted_at desc) as r, *
  from(
select c.id as cid, c.body as cbody, p.inserted_at as pinserted_at, u.*
  from "cms_posts" as c join "posts_comments" as p on c.id= p.post_id join "users" as u on p.author_id= u.id) as view) as v
 where r<= 3;