(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.co开发者_运维百科unt 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.

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

This query should give you what you need:

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

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