lundi 8 juin 2015

Rails 3 advanced query between models with averages and order

I have 3 models User, Commission and Program. A user has_one program and has_many commissions.

I am able to query all the users enrolled in the program by their commissions

User.
joins(:commissions, :program).
select('users.*, sum(commissions.amount) as total_revenue').
group('commissions.user_id').
order('total_revenue DESC').
all

Now I want to query the users by their average renevue while enrolled on the program, that is the total revenue / number of months enrolled on the program

The Program model has the created_at timestamp. I could determine the number of months a single user has been on the program

(Time.now.utc.year * 12 + Time.now.utc.month) - (program.created_at.utc.year * 12 + program.created_at.utc.month)

With the number of months I could get the average revenue made by a single user since he enrolled on the program.

This is easily done with one record, I don't know how to calculate the number of months on the program on the select statement of the query and how to use it to order the results afterwards for all the users on a single query.

Any help would be greatly appreciated.

Aucun commentaire:

Enregistrer un commentaire