How to join arrays with MySQL from 3 tables of many-to-many relationship

Question

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);

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"] 
}

How can I make this array of favourite teams for each person with MySQL?

P.S. I presuppose that this is better to be done in MySQL before the data are retrieved with PHP.

can you give some example data for your tables? |Because your JSON example only gives one row! And how should a multi user JSON response look like? "P.S. I presuppose that this is better to be done in MySQL before the data are retrieved with PHP." No not really in PHP it's much eazier to generate JSON data. — Raymond Nijland, Mar 14 '18 at 14:15
I think you are looking for something like this: https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value — Sander, Mar 14 '18 at 14:23
Thanks @Raymond, I think this one suits this situation better: https://stackoverflow.com/questions/397708/is-it-possible-to-concatenate-strings-from-multiple-rows-and-tables-into-one-res — Sander, Mar 14 '18 at 14:34

Raymond Nijland · Accepted Answer · 2019-07-03T11:49:56.167

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.

Query

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

Result

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

demo

http://www.sqlfiddle.com/#!9/4cd244/19

Edited more friends

Query

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

Result

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

demo

http://www.sqlfiddle.com/#!9/4cd244/61

Very nice one. However, you do not show me the result for 'Alex James' who has two favourite teams and this is why I asked this question. — Outcast, Mar 14 '18 at 14:44
Alex is friend.id = 2 you need to change the WHERE statement.. check the sqlfiddle demo there is also the alex example.. Your example JSON is only using one record so ive based mine answer on that.. Besides your JSON example is mixing up David Belton and Alex James data. — Raymond Nijland, Mar 14 '18 at 14:47
Very good answer and sorry for delaying ticking it as correct. — Outcast, Mar 14 '18 at 17:44
However, would you like to edit your post (or write a really analytical comment) to explain me how to do this for many `friends_id`? Shall I use a counter variable and a while loop? — Outcast, Mar 14 '18 at 17:46
Very good(upvote). But I will test both of them tomorrow (so keep an eye here...perhaps something may not work exactly like this in my machine...actually I have added some columns to some of my tables at the right of the existing columns....so I hope that your source code works right despite this)... — Outcast, Mar 14 '18 at 23:54
Hello again @Raymond. Can you check please the following question:https://stackoverflow.com/questions/49294704/how-to-select-the-columns-for-inner-join-table-depending-on-values-of-the-joined? It must be easy to you now to respond to it. — Outcast, Mar 15 '18 at 08:42

How to join arrays with MySQL from 3 tables of many-to-many relationship

1 Answers1

Linked