jissy jissy - 1 year ago 53
Ruby Question

Sort based on associated points in rails4

Post model

has_many :votes

Vote model

belongs-to :post

I need to fetch all posts from posts table and to display. But the entries should be sorted in ASC/DESC(params[:order]) of its votes. If all posts have Vote table entry then below code works perfectly.

I have a query as below:

@posts = Post.joins('LEFT OUTER JOIN votes ON votes.post_id = posts.id').select('posts.*, SUM(votes.point) AS total_point').group('posts.id').order("total_point #{params[:order]}")

But, if any post have no votes table entry, then the non-voted posts makes the list sorting appear wrong. If no votes to some posts, and if the sort order is ASC ,then need to appear the non-voted posts first in the list , and
if the sort order is DESC ,then need to appear the non-voted posts last in the list. Please help.


Answer Source

Cause not all of your posts have votes then after join some records will have votes.point value as a NULL. NULLs are specific values which should be handled separately and may cause issues with descending ordering, because they are sorted last in default ascending order in PostgreSql. In order to have records with NULL votes properly sorted I would suggest to coerce NULL values to 0.

SUM(COALESCE(votes.point, 0))

After that all posts which don't have votes will have total_point value equal to 0 which is a regular number that can be ordered as you want.

Alternatively, PostgreSql has additional syntax for specifying how NULL values should be sorted with ORDER BY clause. https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-ORDERBY Example: