Get total revenue for special product

Question

I want to get total revenue from a productId on Drupal commerce. How can I do that? I found in drupal commerce's table but didn't see any relationship to get that info.

Answer 1

After some research here is the result. I think it work fine

$totalRevenueQuery = db_select('commerce_payment_transaction', 'cpt')
    ->fields('cpt', array('currency_code'));
  $totalRevenueQuery->addExpression('SUM(amount)', 'amount');
    $totalRevenueQuery->leftJoin('commerce_order', 'commerce_order', 'cpt.order_id = commerce_order.order_id');
    $totalRevenueQuery->leftJoin('field_data_commerce_line_items', 'field_data_commerce_line_items', 'cpt.order_id = field_data_commerce_line_items.entity_id');
    $totalRevenueQuery->innerJoin('commerce_line_item', 'commerce_line_item_field_data_commerce_line_items', 'field_data_commerce_line_items.commerce_line_items_line_item_id = commerce_line_item_field_data_commerce_line_items.line_item_id');
    $totalRevenueQuery->leftJoin('field_data_commerce_product', 'commerce_line_item_field_data_commerce_line_items__field_data_commerce_product', 'commerce_line_item_field_data_commerce_line_items.line_item_id = commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.entity_id');
    $totalRevenueQuery->innerJoin('commerce_product', 'commerce_product_field_data_commerce_product', 'commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.commerce_product_product_id = commerce_product_field_data_commerce_product.product_id');
    $totalRevenueQuery->condition('commerce_product_field_data_commerce_product.product_id', $product_id);
    $totalRevenueQuery->condition('field_data_commerce_line_items.entity_type', 'commerce_order');
    $totalRevenueQuery->condition('field_data_commerce_line_items.deleted', 0);
    $totalRevenueQuery->condition('commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.entity_type', 'commerce_line_item');
    $totalRevenueQuery->condition('commerce_line_item_field_data_commerce_line_items__field_data_commerce_product.deleted', 0);
    $totalRevenueQuery->condition('commerce_order.type', 'commerce_order');
  $totalRevenue = $totalRevenueQuery->execute();