我是一名實(shí)習(xí)前端開發(fā)人員。從來沒有真正喜歡過后端，所以我對 SQL 知之甚少。但現(xiàn)在，在一個(gè)冒險(xiǎn)時(shí)代，我需要做后端，因?yàn)槲乙脖环峙淞?。嗯，我們有一個(gè)內(nèi)部系統(tǒng)來跟蹤我們正在重建的一些關(guān)于我們自己的數(shù)據(jù)。以前的系統(tǒng)使用 JSON 文件來存儲其數(shù)據(jù)，格式如下：{ "id": 123, "userId": 522, "effortDate": "2020-06-05", "data": [ { "projectId": 14, "value": 7 }, { "projectId": 23, "value": 3 } ], "updatedAt": "2020-06-08T10:13:11-03:00", "createdAt": "2020-06-08T10:13:11-03:00"}我們正在轉(zhuǎn)向基于 SQL 的數(shù)據(jù)庫。因此，根據(jù)我對關(guān)系數(shù)據(jù)庫如何工作的基本了解，我建立了這個(gè)模型：問題是：至少現(xiàn)在，我們?yōu)閼?yīng)用程序使用相同的前端，所以我需要以與當(dāng)時(shí)只是 JSON 時(shí)相同的格式解析數(shù)據(jù)。而對于系統(tǒng)的基本操作，它需要一直需要現(xiàn)在的所有數(shù)據(jù)，兩張表。我的第一個(gè)想法是，強(qiáng)行將它們與兩個(gè)不同的 SELECT 混合在一起。我想，這并不是那么糟糕（也許是因?yàn)樗行В宋覀冋谔幚頂?shù)千個(gè)條目，所以我擔(dān)心事情可能會變慢。這是我用來解析它的代碼：public function getAllEfforts() { /* Get all data from `effort` */ $effortQuery = "SELECT id, userId, effortDate, createdAt, updatedAt FROM efforts;"; $efforts = $this->pdo ->query($effortQuery) ->fetchAll(PDO::FETCH_ASSOC); /* Get all data from `effort_data` */ $effortDataQuery = "SELECT id, effortId, projectId, value FROM efforts_data;"; $effortsData = $this->pdo ->query($effortDataQuery) ->fetchAll(PDO::FETCH_ASSOC); /* Since, `effort` doesn't have a data field, let's define the array wished here */ foreach($efforts as &$effortsUnity) { $effortsUnity['data'] = []; } /* Push the stuff from `effort_data` to the just created data array */ foreach($effortsData as &$effortDataUnity) { $effortIndex = ($effortDataUnity['effortId'] - 1); unset($effortDataUnity['id']); unset($effortDataUnity['effortId']); array_push( $efforts[$effortIndex]['data'], $effortDataUnity ); } return $efforts;}我的問題是：有什么辦法只使用 SQL 來做到這一點(diǎn)嗎？或者除了使用 MongoDB 或其他基于 JSON 的數(shù)據(jù)庫之外的任何其他最佳方法，但目前還不可能。抱歉，如果我不夠清楚，這是我的第一個(gè)問題，我愿意澄清任何問題。
2 回答
ITMISS
TA貢獻(xiàn)1871條經(jīng)驗(yàn) 獲得超8個(gè)贊
您可以利用 MySQL 的 JSON 函數(shù)直接從數(shù)據(jù)庫生成嵌套 JSON 結(jié)構(gòu)。
考慮：
SELECT
id,
userId,
effortDate,
createdAt,
updatedAt,
(
SELECT JSON_ARRAYAGG(
JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)
)
FROM efforts_data ed
WHERE ed.effortId = e.id
) AS data
FROM efforts
這為您提供了每行一行efforts，其中有一列名為data，其中包含由 table 中的對象數(shù)組組成的 JSON 有效負(fù)載efforts_data。
如果您想要的話，您可以向前一步將每一行填充到一個(gè)對象中：
SELECT JSON_OBJECT(
'id', id,
'userId', userId,
'effortDate', effortDate,
'createdAt', createdAt,
'updatedAt', updatedAt,
'data', (
SELECT JSON_ARRAYAGG(
JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)
)
FROM efforts_data ed
WHERE ed.effortId = e.id
)
) res
FROM efforts
在 MySQL < 5.7.22 或 MariaDB < 10.5.0 中JSON_ARRAYAGG()尚不可用，一種解決方法是GROUP_CONCAT()字符串連接。基本上你會重寫這個(gè)：
SELECT JSON_ARRAYAGG(
JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)
)
FROM efforts_data ed
WHERE ed.effortId = e.id
作為：
SELECT CONCAT(
'[',
GROUP_CONCAT(JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)),
']'
)
FROM efforts_data ed
WHERE ed.effortId = e.id
慕容森
TA貢獻(xiàn)1853條經(jīng)驗(yàn) 獲得超18個(gè)贊
我的問題是：有什么辦法只使用 SQL 來做到這一點(diǎn)嗎？
efforts id是的，這就是為什么和之間存在關(guān)系efforts_data effortId：
SELECT e.id, e.userId, e.effortDate, e.createdAt, e.updatedAt,
ed.id, ed.effortId, ed.projectId, ed.value
FROM efforts AS e, efforts_data AS ed
WHERE e.id = ed.effortId
- 2 回答
- 0 關(guān)注
- 135 瀏覽
添加回答
舉報(bào)
0/150
提交
取消