Rails: Order activerecord object by attribute under has_many relation

Question

class Post
  has_many :commments
end

class Comment
  belongs_to :post
end

I wish to display a list of posts ordered by date of post creation (submitted_at). I also want some post xyz to appear at the top if it has some new comment posted and yet to be reviewed by moderator. We will determine this by a boolean attribute/field at comments level (moderated = 1/0)

I tried

Posts.join(:comments)
  .distinct
  .order("submitted_at DESC, comments.moderated")

but this excludes posts that have no comments and results aren't sorted as expected. I am sure that we can do this at ruby level, but looking for a way to do this using AR.

may be this could help http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3 — chaitanya, Mar 03 '16 at 11:39

score 0 · Answer 1 · answered Mar 03 '16 at 12:04

0

For the join, use this:

Posts.join("LEFT JOIN comments ON comments.post_id = posts.id")

Which will include the ones with no comments.

Your sorting seems to suggest you want a count of moderated comments, in which case, try this:

.order("submitted_at DESC, COUNT(comments.moderated)")

Although, you may need to use group in some way too.

answered Mar 03 '16 at 12:04

Matt Gibson

14,616
7
47
79

I don't need the count. I just want the posts with comments yet to be moderated at the top. Could count help me here? – Nirav Gandhi Mar 03 '16 at 12:26
Ah. Is moderated a boolean field? – Matt Gibson Mar 03 '16 at 23:19
yes.. but in db its stored as 0/1 as per rails convention. – Nirav Gandhi Mar 04 '16 at 01:20

Rails: Order activerecord object by attribute under has_many relation

1 Answers1