CODEWITHSUNDEEP
sqlruby-on-railssorting
v0rin
v0rin

Reputation: 500

SQL query sorting groups by the most recent post

I am creating a rails 3 application.

It has Groups (like social groups with memebers and so on and members can post on groups). So my model is Group and Post, and group has_many posts (each of them (posts) obviously have group_id).

I am trying to figure out, how to get a list of groups sorted by the most recent post, including groups without any posts.

Thanks!

Upvotes: 0

Views: 174

Answers (3)

v0rin
v0rin

Reputation: 500

For anybody who would be looking for final resolution in rails 3, it would look like this:

sorted_groups_ids = Group.joins("LEFT JOIN posts p ON groups.id = p.group_id").select("groups.id").group("groups.id").order("MAX(p.id) DESC").map(&:id) sorted_groups = Group.find(sorted_groups_ids).sort_by { |g| sorted_groups_ids.index(g.id) }

the second line is necessary since another find breaks the previous order

Upvotes: 0

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79838

Depending on what columns you have, it could be something like

select g.group_id, g.group_name, max( p.post_date )
from   group g left join post p on g.group_id = p.group_id
group by g.group_id, g.group_name
order by max( p.post_date ) desc

Upvotes: 1

DeathHammer
DeathHammer

Reputation: 660

   Group.joins('LEFT OUTER JOIN posts ON groups.id = posts.group_id').order('posts.created_at desc').group('groups.id')

Upvotes: 0

Related Questions