MySQL query to select from different tables and group data [duplicate]

Question

I have some tables about tv shows. Also I have one of the pages where I show where user can see some seasons and episodes.

CREATE TABLE `tv` (
  `tv_id` int(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `title` VARCHAR(30),
  `rating` float(2,1) DEFAULT NULL,
  `total_seasons` tinyint UNSIGNED,
)

CREATE TABLE `tv_player_episode_mapping` (
  `tv_id` int(11) UNSIGNED,
  `season` tinyint(11) UNSIGNED,
  `episode` tinyint(11) UNSIGNED,
  `player_id` tinyint(11) UNSIGNED,
  `file_name` TEXT
);

CREATE TABLE `player` (
  `player_id` int(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `hostname` VARCHAR(30),
  UNIQUE KEY `name` (`hostname`)
);

To select a data from a table I use this query:

SELECT tpe.season, tpe.episode, p.hostname, tpe.file_name, t.rating, t.total_seasons
      FROM tv_player_episode_mapping tpe
      INNER JOIN player p ON p.player_id = tpe.player_id
      INNER JOIN tv t ON t.tv_id = tpe.tv_id
      WHERE tpe.tv_id = 1;

Below is the result of query and here the problem - some data like total_seasons and ratins repeated:

[
  {
    file_name:"19891-molodoy-papa.html",
    hostname:"kinoclub.cc",
    season: 1,
    episode: 1,
    total_seasons: 1,
    rating: 8.5,
  },
  {
    file_name:"19891-molodoy-papa.html",
    hostname:"kinoclub.cc",
    season: 1,
    episode: 2,
    total_seasons: 1,
    rating: 8.5,
  }
]

I try to change my query to get somethink like this (I am novice in databases and I think this is good structure for response from the server):

{
  total_seasons: 1,
  rating: 8.5,
  players: [
    [ // Here first element of array `playes` is array for season 1
      [ // Episode 1
        {
          file_name:"1-1-the-young-pope.html",
          hostname:"my-player.io",
        },
        {
          file_name:"1-1-the-young-pope.html",
          hostname:"another-one-player.io",
        }
      ],
      [ // Episode 2
        {
          file_name:"1-2-the-young-pope.html",
          hostname:"my-player.io",
        },
        {
          file_name:"1-2-the-young-pope.html",
          hostname:"another-one-player.io",
        }
      ],
    ],
    [] // Season 2
  ]
};

But because I am little experienced in databases I stop trying with this query and can't find the way:

SELECT tpe.season, tpe.episode,
  JSON_OBJECT(
    'total_seasons', t.total_seasons,
    'players', JSON_OBJECT('hostname', p.name, 'file_name', tpe.file_name))
      FROM tv_player_episode_mapping tpe
      INNER JOIN player p ON p.player_id = tpe.player_id
      INNER JOIN tv t ON t.tv_id = tpe.tv_id
      WHERE tpe.tv_id = 1;

How can I change this query to solve my problem?

UPD:

I wrote some query using example and I am good with it, but now result of my query looks strange.

New query:

select json_object(
    'rating', t.rating,
    'total_seasons', t.total_seasons,
    'players', json_array(
        (select GROUP_CONCAT(
            json_object(
                'hostname', p.hostname,
                'file_name', tpe.file_name,
                'season', tpe.season,
                'episode', tpe.episode
            )
        )
         FROM tv_player_episode_mapping tpe
           INNER JOIN player p ON p.player_id = tpe.player_id
           INNER JOIN tv t ON t.tv_id = tpe.tv_id
         where tpe.tv_id = 52)
    )
)
 from tv t WHERE tv_id=52;

Result of query:

{
  "json_object( 'rating', t.rating, 'total_seasons', t.total_seasons, 'players', json_array( (select GROUP_CONCAT( json_object( 'hostname', p.hostname, 'file_name', tpe.file_name, 'season', tpe.season, 'episod":
  "{\"players\": [\"{\\\"season\\\": 1, \\\"episode\\\": 1, \\\"hostname\\\": \\\"kinoclub.cc\\\", \\\"file_name\\\": \\\"19891-molodoy-papa.html\\\"},{\\\"season\\\": 1, \\\"episode\\\": 2, \\\"hostname\\\": \\\"kinoclub.cc\\\", \\\"file_name\\\": \\\"19891-molodoy-papa.html\\\"},{\\\"season\\\": 1, \\\"episode\\\": 1, \\\"hostname\\\": \\\"kinoclub.cc\\\", \\\"file_name\\\": \\\"123.html\\\"}\"], \"rating\": 8.5, \"total_seasons\": 1, \"rating\": 8.300000190734863}"
}

What can I do to format result like this?

   {
      total_seasons: 1,
      rating: 8.5,
      players: []
    };

You can't get a group of rows and a group of fields as a response from one query. From a quick glance, my best guess is you need two queries - one which gets the row of info like rating, and another which gets the set of rows about players, then optionally embed one inside the other. — Luke Briggs
– Luke Briggs, Commented Dec 12, 2016 at 20:14
@LukeBriggs, No that's a lie. In mysql it already possible. please see this question stackoverflow.com/questions/41094391/… — Dennisrec
– Dennisrec, Commented Dec 12, 2016 at 20:21
@Dennisrec You misread my comment; GROUP is something entirely different. The OP is after [field][field][group of rows] which a query cannot respond with. — Luke Briggs
– Luke Briggs, Commented Dec 12, 2016 at 20:24
This type of grouping of related rows into sub-arrays needs to be done in an application language, it can't be done directly in MySQL. — Barmar
– Barmar, Commented Dec 12, 2016 at 20:28
@rel1x Looks like you're missing the CAST(..) as JSON part - see the lower down info in that answer :) — Luke Briggs
– Luke Briggs, Commented Dec 12, 2016 at 21:37

Oleg Imanilov · Accepted Answer · 2016-12-12 20:30:00Z

1

The short answer is no, but you still can partially implement JSON like structure by using group_concat function.

Example:

SELECT x, y, concat('[', group_concat(z), ']') as z FROM tbl group by x, y;

Will give you answer like:

x   y   z
1   2   [3,4,5]
6   7   [8,9,10]

answered Dec 12, 2016 at 20:30

Oleg Imanilov

2,7791 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query to select from different tables and group data [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related