MySQL View : polymorphic associations and child tables

Question

如何创建 View (producer_view 和retailer_view)以显示每个交易 的总额SUM( unit_price * quantity )他们什么时候是供应商和客户？

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 |
+------+-------------------+-----------------+

我的两个困难是:

• supplier_id和 client_id对于每个事务，都存储在两个多态关联中，其中supplier_type和 client_type可以是 App\Producer或 App\Retailer .

• unit_price和 quantity每个交易都存储在一个子表中。

两个父表是:

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

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

在交易中，生产商和零售商都可以是供应商或客户 .

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 |
+----+---------+-------------+---------------+-----------+--------------+

每个交易的详细信息都记录在item表中:

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:

• producer_view
• retailer_view

Answer 1

试试这些查询:

对于生产者:

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

对于零售商:

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