Count and Sum in Join sub query - can I build this sql query using ActiveRecord?

Question

I am learning ActiveRecord. Can I build this query?

@sales_by_product = ActiveRecord::Base.connection.execute("SELECT it.name, it.id, it.seller_id, pur.volume, pur.sales FROM items it INNER JOIN (SELECT item_id, COUNT(*) AS volume, SUM(price) AS sales, workflow_state AS state FROM purchases WHERE workflow_state = 'payment_successful' GROUP BY item_id, workflow_state) pur ON pur.item_id = it.id WHERE it.seller_id = '" + current_user.id.to_s + "'") 

I would like to use the AR api as much as possible, but I have not yet received the above action using only AR.

Thanks!

Answer 1

I do not think it is a good idea to use AR for this request. At first it seems funny, but it gets annoying. And it will be difficult to change later.

You can create your own query builder:

 def query_for current_user <<-SQL SELECT it.name, it.id, it.seller_id, pur.volume, pur.sales FROM items it INNER JOIN (SELECT item_id, COUNT(*) AS volume, SUM(price) AS sales, workflow_state AS state FROM purchases WHERE workflow_state = 'payment_successful' GROUP BY item_id, workflow_state) pur ON pur.item_id = it.id WHERE it.seller_id = '" + current_user.id.to_s + "'") SQL end @sales_by_product = ActiveRecord::Base.connection.execute( query_for( current_user ))