MySQL Views: polymorphic associations and child tables

Question

How to create views (producer_view and retailer_view) showing the total amount of each transaction SUM( unit_price * quantity ) when they are supplier and client ?

producer_view
+------+-------------------+-----------------+
| name | as_supplier_total | as_client_total |
+------+-------------------+-----------------+
| foo  |               144 |               9 |
| bar  |                 9 |             264 |
+------+-------------------+-----------------+

retailer_view
+------+-------------------+-----------------+
| name | as_supplier_total | as_client_total |
+------+-------------------+-----------------+
| baz  |                16 |             125 |
| qux  |               245 |              16 |
+------+-------------------+-----------------+

My two difficulties are that:

• The supplier_id and the client_id for each transaction is stored in two polymorphic associations where supplier_type and client_type can be either App\Producer or App\Retailer.

• The unit_price and quantity for each transaction are stored in a child table.

The two parent tables are:

Producer
+----+------+
| id | name |      
+----+------+
|  1 | foo  |     
|  2 | bar  |      
+----+------+

Retailer
+----+------+
| id | name |
+----+------+
|  1 | baz  |
|  2 | qux  |
+----+------+

In a transaction, both producer and retailer can be either supplier or client.

Transaction
+----+---------+-------------+---------------+-----------+--------------+
| id | product | supplier_id | supplier_type | client_id |  client_type |
+----+---------+-------------+---------------+-----------+--------------+
|  1 | a       |           1 |  App\Producer |         1 | App\Retailer |
|  2 | b       |           2 |  App\Retailer |         1 | App\Retailer |
|  3 | c       |           1 |  App\Producer |         2 | App\Producer |
|  4 | d       |           2 |  App\Retailer |         2 | App\Producer |
+----+---------+-------------+---------------+-----------+--------------+

Details of each transaction is recorded in an item table:

Item
+----+----------------+------------+----------+
| id | transaction_id | unit_price | quantity |
+----+----------------+------------+----------+
|  1 |              1 |         10 |        1 |
|  2 |              1 |         20 |        1 |
|  3 |              2 |         30 |        1 |
|  4 |              2 |         40 |        1 |
|  5 |              3 |         50 |        1 |
|  6 |              3 |         60 |        1 |
|  7 |              4 |         70 |        1 |
|  8 |              4 |         80 |        1 |
|  9 |              5 |          2 |        2 |
| 10 |              6 |          3 |        3 |
| 11 |              7 |          4 |        4 |
| 12 |              8 |          5 |        5 |
+----+----------------+------------+----------+

---

SQLFiddle from the validated answer below:

• producer_view
• retailer_view

Answer 1

Try these queries:

For producer:

SELECT
  p.name,
  SUM(i.quantity * i.unit_price) AS as_supplier_total,
  cagg.as_client_total
FROM producer p
LEFT JOIN transaction t on t.supplier_id = p.id AND t.supplier_type = 'App\\Producer'
LEFT JOIN item i ON t.id = i.transaction_id
LEFT JOIN (
  SELECT
    p.id,
    SUM(i.quantity * i.unit_price) AS as_client_total
  FROM producer p
  LEFT JOIN transaction t on t.client_id = p.id AND t.client_type = 'App\\Producer'
  LEFT JOIN item i ON t.id = i.transaction_id
  GROUP BY p.id
) AS cagg ON cagg.id = p.id
GROUP BY p.id

For retailer:

SELECT
  r.name,
  SUM(i.quantity * i.unit_price) AS as_supplier_total,
  cagg.as_client_total
FROM retailer r
LEFT JOIN transaction t on t.supplier_id = r.id AND t.supplier_type = 'App\\Retailer'
LEFT JOIN item i ON t.id = i.transaction_id
LEFT JOIN (
  SELECT
    r.id,
    SUM(i.quantity * i.unit_price) AS as_client_total
  FROM retailer r
  LEFT JOIN transaction t on t.client_id = r.id AND t.client_type = 'App\\Retailer'
  LEFT JOIN item i ON t.id = i.transaction_id
  GROUP BY r.id
) AS cagg ON cagg.id = r.id
GROUP BY r.id