Ordering posts by the aggregated count of likes and saves using Ecto

My SQL skills are a bit rusty, and honestly were never the absolute best, so I’m at a bit of a loss as to how to do this in one go.

What I have is three tables, Prompts, LikedPrompts, and SavedPrompts

LikedPrompts and SavedPronts are simple tables with just the user_id and prompt_id as columns.

What I’m looking to do is to return a list of Prompts in a descending order where the number of likes and saves for each prompt have been added together.

So, for example, if Prompt #1 has 20 saves and 10 likes, but Prompt #2 has 5 saves and 50 likes, the results will be [Prompt2, Prompt1] as Prompt2 will have an aggregated score of 55 while Prompt1 will have an aggregated score of 30.

Can anyone help me figure out what this query looks like?

So of course after posting I actually figured out a way to do this but it seems a little hacky and if there is a better way to do it I’d love to know. My solution is as follows:

  prompt in Prompt,
  select: %Prompt{
    id: prompt.id,
    promoted: prompt.promoted,
    promoted_at: prompt.promoted_at,
    published_at: prompt.published_at,
    status: prompt.status,
    short_id: prompt.short_id,
    title: prompt.title,
    title_slug: prompt.title_slug,
    playing: prompt.playing,
    seeking: prompt.seeking,
    message_length: prompt.message_length,
    time_scale: prompt.time_scale,
    interaction: prompt.interaction,
    response_speed: prompt.response_speed,
    setting: prompt.setting,
    user_id: prompt.user_id,
    inserted_at: prompt.inserted_at,
    updated_at: prompt.updated_at,
    score: fragment("(select count(*) from saved_prompts where prompt_id = p0.id) + (select count(*) from liked_prompts where prompt_id = p0.id) as score")
  order_by: fragment("score desc"),

I think you can try:

|> join(:left, [p], assoc(p, :liked_prompt))
|> join(:left, [p], assoc(p, :saved_prompt))
|> select([p, l, s], %Prompt{p | score: count(l.id) + count(s.id)})
|> order_by([p], desc: p.score)
|> Repo.all()

I haven’t checked if it works, but I think it’s worth giving it a try. :slight_smile: