Łukasz Korol Łukasz Korol - 4 months ago 22
Ruby Question

Getting most liked news from the query

There are two models:

# == Schema Information
#
# Table name: news
#
# id :integer not null, primary key
# title :string not null
# content :text not null
# scope :string not null
# created_at :datetime not null
# updated_at :datetime not null
# person_id :integer not null

# == Schema Information
#
# Table name: likes
#
# id :integer not null, primary key
# like :boolean
# person_id :integer not null
# news_id :integer not null


Relation

news has many likes
like belongs to news


I want to get most liked news from query. Query should substract count of likes equal true from likes equal false. The highest number is most liked news.
What I tried:

@count_true_likes = Like.where('likes.like = ?', true).group(:news_id).count
@count_false_likes = Like.where('likes.like = ?', false).group(:news_id).count


Result is Hash with id and counted likes. I don't have idea how to subtract in query positive likes from negative likes, and do it for every news.

Answer

I resolved my problem:

@most_liked_news_id = Like.group(:news_id)
                          .select('news_id, SUM(case when likes.like then 1 else -1 end) as max_positive')
                          .order('max_positive desc').map(&:news_id).first
@most_liked_news = News.find(@most_liked_news_id)