mysql多对多数组_php – 如何使用3个多对多关系表从MySQL连接数组

“eazy”方法是使用CONCAT生成JSON.

并使用GROUP_CONCAT将多个团队记录合并为一个JSON数组.

此方法也适用于不支持创建JSON函数的旧MySQL版本.

询问

SELECT

CONCAT(

"{"

, '"id"' , ":" , '"' , friends.id , '"' , ","

, '"name"' , ":" , '"' , friends.name , '"' , ","

, '"team"' , ":" , "["

, GROUP_CONCAT('"', teams.name, '"')

, "]"

, "}"

) AS json

FROM

friends

INNER JOIN

relations

ON

friends.id = relations.friends_id

INNER JOIN

teams

ON

relations.teams_id = teams.id

WHERE

friends.id = 1

结果

| json |

|-----------------------------------------------------------------|

| {"id":"1","name":"David Belton","team":["Cleveland Cavaliers"]} |

演示

编辑了更多的朋友

询问

SELECT

CONCAT(

"["

, GROUP_CONCAT(json_records.json) # combine json records into a string

, "]"

) AS json

FROM (

SELECT

CONCAT(

"{"

, '"id"' , ":" , '"' , friends.id , '"' , ","

, '"name"' , ":" , '"' , friends.name , '"' , ","

, '"team"' , ":" , "["

, GROUP_CONCAT('"', teams.name, '"')

, "]"

, "}"

) AS json

FROM

friends

INNER JOIN

relations

ON

friends.id = relations.friends_id

INNER JOIN

teams

ON

relations.teams_id = teams.id

WHERE

friends.id IN(SELECT id FROM friends) #select the friends you need or just simply friends.id IN(1, 2)

GROUP BY

friends.id

)

AS json_records

结果

| json |

|--------------------------------------------------------------------------------------------------------------------------------------------------|

| [{"id":"1","name":"David Belton","team":["Cleveland Cavaliers"]},{"id":"2","name":"Alex James","team":["Boston Celtics","Cleveland Cavaliers"]}] |

演示