Rails - Effectively pull and compute data across multiple relationships

Question

Trying to get data in the most efficient way for some reports using Rails 2.3 and MySQL.

Our application has users, offers and purchases. Relations look like this:

class User has_many :purchased_deals has_many :deals, :through => :purchased_deals end class Deal has_many :purchased_deals has_many :users, :through => :purchased_deals end class PurchasedDeal belongs_to :deal belongs_to :user end 

For the report that I run, I need to get all the users who made the purchase (i.e. have at least one BuyasedDeal), and then the total amount of all the deals they bought (the price is tied to the deal, not BuyasedDeal).

Of course, I could start with a list of all users, including both transactions and purchased transactions. I tried this and the request is massive (30,000 users, give or take, 3,000 transactions, 100,000+ transactions purchased).

I could start with users and then make .each and find those that have a purchased deal, divide them into my group, and then sort through each of them to get the total purchase amount, but this is the number of requests.

Both of these methods currently take so much time that requests fail. What would be the most efficient way to get the data I need? Adding columns to tables is a completely acceptable solution, by the way. I have full access to the database to do what I need.

Thanks!

Answer 1

To get a list of user IDs with more than one purchase, you can do the following, which will have access to only one table:

 user_ids = PurchasedDeal.count(:group => :user_id, :having => 'count_all > 0').keys 

Subsequently, you can get all of these users:

 users = User.find user_ids 

---

Things can be sped up with a cache counter. In your user model, add the option :counter_cache => true to the has_many association for acquired transactions. You will need an additional integer column in your users table and initialize, which may look like this during migration:

 add_column :users, :purchased_deals_count, :integer, :null => false, :default => 0 User.each { |u| User.reset_counters u, :purchased_deals } 

Once this is set aside, it will become much easier:

 users = User.all :conditions => 'purchased_deals_count > 0' 

Rails will save a column for you with most of the standard operations.

---

To get the total price, the connection will always be included. Or you can build a hash of transaction prices and perform tedious processing in Ruby. I'm not an SQL expert, but you can get rid of the join by saving the price with BuyasedDeal. Otherwise, here's how to do it with the connection:

 user_id_to_price = PurchasedDeal.sum 'deal.price', :include => :deal, :group => :user_id 

You can filter this out only for users by adding something like :conditions => ['user_id IN (?)', users] . (Where users can be a list of identifiers as well as User objects.)

Answer 2

Assuming you added a price column to the purchase_deals table, you could get information about users and the total price of transactions as follows:

 select users.id, sum(purchased_deals.price) from users, purchased_deals where users.id = purchased_deals.user_id group by users.id having sum(purchased_deals.price) > 0