Rails advanced queries with join and sum calculation

Question

I have two models: companies and expenses. Companies have many expenses and expenses belong to companies. My expense model has an 'amount' column.

I was wondering if there is a way to perform a find based on a date range and the amount column of the expenses. Something like top 3 companies by total expense amounts over a 7 day period.

I've tried for the better part of the day to get this to work, I've attempted joins, chaining named scopes, raw sql, etc. and I'm not having any luck.

Thanks for the help.

Answer 1

I added this to my company model and was able to achieve what I was looking for...

named_scope :top_with_expenses, :joins => :expenses, :conditions => ['expenses.created_at > ?', Time.now.midnight - 7.days], :order => "SUM(expenses) DESC", :group => "expenses.company_id", :limit => 3