CODEWITHSUNDEEP
ruby-on-railsviewmodels
Keenan Thompson
Keenan Thompson

Reputation: 980

(rails) Order by Number of Likes on a Website

I have a model called websites that has_many :likes, and of course another model called likes with belongs_to :website. I want to get an array of all the websites but order them by the number of likes a website has.

helpful info:

I have it set up so @website.likes will return the likes from @website. (@website.likes.count is the number of likes a website has)

I want to get an array of all the websites but order them by the number of likes a website has.

Upvotes: 2

Views: 1179

Answers (3)

Andy Waite
Andy Waite

Reputation: 11076

This query should give you what you need:

all(:select => 'websites.*, count(*) as count',
  :joins => :likes,
  :group => :websites,
  :order => 'count DESC')

Upvotes: 2

madlep
madlep

Reputation: 49666

As others have posted, you can do a join onto likes and then order by the count. Performance may be a bit iffy depending on indexing etc. You'll have slightly different syntax depending on if you're running Rails 2 or 3.

An alternative would be to maintain a denormalised likes_count column on websites which is updated when a Like model object is saved.

Then you just need to query on Website and specify an order likes_count descending (and is easily indexed).

To do this, create a likes_count integer column on the websites and specify the :counter_cache option on the belongs_to declaration in the Likes model. e.g:

class Likes
  belongs_to :website, :counter_cache => true
end

Check out http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html for more info

Upvotes: 5

randomguy
randomguy

Reputation: 12242

Something along the lines of:

SELECT Websites.*, COUNT(Like.ID) AS Liked
FROM websites
    LEFT OUTER JOIN Like ON websites.ID = Like.website_id
GROUP BY Like.website_id ORDER BY Liked DESC

Upvotes: 0

Related Questions