mysql - How can I recreate this query in ActiveRecord -

i know sounds "do homework me" question, i've been struggling recreating query activerecord while , wondering if it's possible without resorting using activerecord::base.connection.execute

the structure basically:

member has_many orders , transactions

i need display members have placed order, total amount orders, , number of transactions given date range ordered total amount.

i can data need following query:

select members.*, sum(orders.amount) total_amount, count(transactions.id) total_transactions  members  inner join orders on (orders.member_id = member.id)  left join transactions on (transactions.member_id = members.id) orders.created_at between "2012-03-01" , "2012-04-01"  group member.id  order total_amount desc 

is possible formulate query via activerecord?

this how i'd through activerecord:

member.includes(:orders, :transactions).select("sum(orders.amount) total_amount").where("orders.created_at between '2012-03-01' , '2012-04-01'").group('members.id').order('total_amount desc') 

this includes lot of raw sql snippets. if want replace actual ruby code can using member.arel_table creatively. see the arel documentation information on how that.