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 gotten the above to work using just AR.

Thanks!

Answer 1

I don't think it is a good idea to use AR for this query. It seems fun at first, but becomes 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 ))