I have users who sign in to classes as part of a course. I’d like to return a list of the top 5 users with the highest attendance, along with their attendance count, in the last 90 days.
I came up with the following, but was wondering if it is the ‘best’ way to do this in Ecto? I thought it might be quicker with a second query to preload users rather than a join but wasn’t able to figure out how to do it that way.
MyApp.Repo.all(
from s in Signin,
join: u in User, where: s.user_id == u.id,
where: s.class_date > datetime_add(^Ecto.DateTime.utc, -90, "day") and
s.course_id == ^course.id,
group_by: [s.user_id, u.id],
order_by: [desc: count(s.user_id)],
limit: 5,
select: %{user: u, count: count(s.user_id)}
)
