如何通过3对多关系表将数组与MySQL连接 [英] How to join arrays with MySQL from 3 tables of many-to-many relationship
问题描述
我在本地服务器上使用phpMyAdmin创建了一个mySQL数据库.在这个数据库中,我存储了我的朋友的名字和最喜欢的NBA球队,这显然是多对多的关系.因此,我在MySQL中运行以下脚本来为此数据库创建适当的表:
I created a mySQL database with phpMyAdmin in my local server. In this database I store the names and the favourite NBA teams of my friends.This is obviously a many-to-many relationship. For this reason, I run the followed script in MySQL to create the appropriate tables for this database:
CREATE TABLE `friends` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `teams` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `relations` (
`friends_id` int(4) NOT NULL,
`teams_id` int(4) NOT NULL,
)
很显然,我在这些表中插入了一些值,但是为了节省空间,在这里我没有广泛提供源代码.一小部分内容如下:
Obviously, I inserted some values to these tables but I do not provide extensively the source code here so as to save some space. An small piece of it is the following:
INSERT INTO `friends` (`id`, `name`)
VALUES
(1,'David Belton'),
(2,'Alex James');
INSERT INTO `teams` (`id`, `name`)
VALUES
(1,'Cleveland Cavaliers'),
(2,'Boston Celtics');
INSERT INTO `relations` (`friends_id`, `teams_id`)
VALUES
(1,1),
(2,1),
(2,2);
运行一个从数据库中获取数据并打印它们的PHP脚本之后,我想为我的每个朋友提供以下有效的json输出:
After running a PHP script that fetches the data from the database and print them, I want to have the following kind of valid json output for each of my friends:
{
"id": "1",
"name": "Alex James",
"team": ["Boston Celtics", "Cleveland Cavaliers"]
}
我怎样才能使每个使用MySQL的人都喜欢的团队呢?
How can I make this array of favourite teams for each person with MySQL?
P.S. 我以为最好在使用PHP检索数据之前在MySQL中完成此操作.
P.S. I presuppose that this is better to be done in MySQL before the data are retrieved with PHP.
推荐答案
简便"方法是使用CONCAT生成JSON.
并使用GROUP_CONCAT将多个团队记录合并到一个JSON数组中.
该方法还可以在不支持创建JSON函数的MySQL较早版本中使用.
The "eazy" method is to use CONCAT to generate JSON.
And use GROUP_CONCAT to combine the multiple teams records into a JSON array.
This methode also works in the older MySQL versions that don't support create JSON functions.
查询
SET SESSION group_concat_max_len = @@max_allowed_packet
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"]} |
演示
http://www.sqlfiddle.com/#!9/4cd244/19
编辑了更多朋友
查询
SET SESSION group_concat_max_len = @@max_allowed_packet
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"]}] |
演示
http://www.sqlfiddle.com/#!9/4cd244/61
这篇关于如何通过3对多关系表将数组与MySQL连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!