php - 如何将连接表的结果数组存储在mysql的行结果中？

Question

我们有 3 个表:

1. 捐款
2. 目的
3. 费用

捐赠:

+--------+------+
| do_id  | name |
+--------+------+
|  1     |  A   |
|  2     |  B   |
|  3     |  A   |
|  4     |  D   |
|  5     |  B   |
|  6     |  B   |
|  7     |  A   |
|  8     |  B   |
+--------+----- +

目的:

+-------+-------+--------+
| pu_id | do_id | purpose|
+-------+-------+--------+
|   1   |   2   |  abc   |
|   2   |   2   |  def   |
|   3   |   2   |  gih   |
|   4   |   3   |  jkl   |
+-------+-------+--------+

费用:

+-------+-------+---------+
| ex_id | do_id | expense |
+-------+-------+---------+
|   1   |   2   |   abc   |
|   2   |   2   |   def   |
|   3   |   2   |   gih   |
|   4   |   3   |   jkl   |
+-------+-------+---------+

现在我想查询以获得捐赠者 B 的所有捐赠并加入 purposes 表 以获取与每个 donation_id 相关的所有目的然后加入 expenses table 以获取与 donation_id 相关的所有费用，并将所有这些独立地放在每个循环中，类似这样

Row number 0

donation_id = 1
array(purposes)
array(expenses)

Row number 1

donation_id = 2
array(purposes)
array(expenses)

Row number 2

donation_id = 3
array(purposes)
array(expenses)

Row number 3

donation_id = 4
array(purposes)
array(expenses)

这是我的尝试:

SELECT *, (
    SELECT * 
    FROM `donation_purposes` 
    WHERE `donation_purposes`.`dopu_donation_id` = 4
) AS `purposes` 
FROM `donations` 
WHERE `donation_id` = '4'

提前致谢

Answer 1

您应该能够通过使用 MySQL aggregate function JSON_ARRAYAGG() 的聚合查询来解决此问题，比如:

SELECT
    d.do_id,
    JSON_ARRAYAGG(p.purpose) purposes,
    JSON_ARRAYAGG(e.expense) expenses
FROM donations d
INNER JOIN purposes p ON p.do_id = d.do_id 
INNER JOIN expense e  ON e.do_id = d.do_id 
GROUP BY d.do_id

我想避免数组中的重复值，并且由于 JSON_ARRAYAGG()(很遗憾)不支持 DISTINCT 选项，您可以将聚合移动到子查询，喜欢:

SELECT
    d.do_id,
    p.agg purpose,
    e.agg expenses
FROM donations d
INNER JOIN (
  SELECT do_id, JSON_ARRAYAGG(purpose) agg FROM purposes GROUP BY do_id
) p ON p.do_id = d.do_id 
INNER JOIN (
   SELECT do_id, JSON_ARRAYAGG(expense) agg FROM expense GROUP BY do_id
) e  ON e.do_id = d.do_id 

这个 demo on DB Fiddle 返回:

| do_id | purpose               | expenses              |
| ----- | --------------------- | --------------------- |
| 2     | ["abc", "def", "gih"] | ["abc", "def", "gih"] |
| 3     | ["jkl"]               | ["jkl"]               |