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

Questions / Help
, ,
1

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?

2

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:

from(
  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"),
)
3

I think you can try:

Prompt
|> 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: