Rails find with three tables and a SUM operation

Question

I'm a little stumped as to get the order of records I want with a find operation.

Let's say you had three models: 1. Websites 2. Links 3. Votes

A website has many links and a link has many votes. Each vote has a certain amount of points that a user can attribute to that vote. I'm trying to get a website index page where websites are listed in order of the sum of the points they've received for all the links for that website.

Here's a simplified version of the schema

  create_table "votes", :force => true do |t|
    t.integer  "link_id"
    t.integer  "points"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "user_id"

  end

  create_table "links", :force => true do |t|
    t.string   "name"
    t.string   "link"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "user_id"
    t.integer  "votes_count", :default => 0
    t.integer  "website_id"
  end

  create_table "websites", :force => true do |t|
    t.string   "domain"
    t.boolean  "verified",          :default => false
    t.integer  "user_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

I'm trying to think about the right active record query to use here. Any help would be appreciated.

Answer 1

To think that I spent a day pulling my hair out when I came up with a solution just an hour after I posted this question.

In the website model I put that the website has_many :points, through => :links

Then the query:

array = Website.find(:all)

array.sort_by {|w| w.donations.sum('amount')}.reverse

This seems to work.