I am learning how to work with MySQL to put together a database of information on books I own. Sometimes books can be written by multiple authors, be a part of multiple series, and be categorized by multiple genres. It is my understanding that the best way to handle array-like data in relational databases is by using separate tables.

My database looks like this:

Books

[ Genres | Series | Authors ] Three separate tables.

[ Genres List | Series List | Authors List ] Three separate tables. Each genre/series/author for a book is stored in an individual row.

I am building an API for retrieving book data from the database. The code used for retrieving books is as follows:

const express = require('express');

const Router = express.Router();

const mysqlConnection = require('../connection');

Router.get('/all', (req, res) => {
  let results = {};

  mysqlConnection.query('SELECT * FROM book', (bookErr, bookRows, bookFields) => {
    if (bookErr) { console.log(bookErr); return; }

    results = bookRows;

    mysqlConnection.query('SELECT genre.name FROM book_genre JOIN genre ON genre.id = book_genre.genre_id WHERE book_genre.book_id = 1', (genreErr, genreRows, genreFields) => {
      if (genreErr) { console.log(genreErr); return; }

      results[0].genres = genreRows;

      mysqlConnection.query('SELECT author.name FROM book_author JOIN author ON author.id = book_author.author_id WHERE book_author.book_id = 1', (authorErr, authorRows, authorFields) => {
        if (authorErr) { console.log(authorErr); return; }
  
        results[0].authors = authorRows;

        mysqlConnection.query('SELECT series.title FROM book_series JOIN series ON series.id = book_series.series_id WHERE book_series.book_id = 1', (seriesErr, seriesRows, seriesFields) => {
          if (seriesErr) { console.log(seriesErr); return; }
    
          results[0].series = seriesRows;
    
          res.send(results);
        });
      });
    });
  });
});

module.exports = Router;

This code gives the output that I want:

[
    {"id":1,
    "title":"Harry Potter and the Sorcerer's Stone",
    "summary":"When mysterious letters start arriving on his doorstep, Harry Potter has never heard of Hogwarts School of Witchcraft and Wizardry.\n\nThey are swiftly confiscated by his aunt and uncle.\n\nThen, on Harrys eleventh birthday, a strange man bursts in with some important news: Harry Potter is a wizard and has been awarded a place to study at Hogwarts.\n\nAnd so the first of the Harry Potter adventures is set to begin.",
    "pages":223,
    "published_date":"1997-06-26T04:00:00.000Z",
    "ddc":"823.914",
    "age_rating":12,
    "reviews_rating":89,
    "genres":[
        {"name":"Fantasy"},
        {"name":"Fiction"}
    ],
    "authors":[
        {"name":"J.K. Rowling"}
    ],
    "series":[
        {"title":"Harry Potter"}
    ]
}
]

Is there a simplified way of being able to retrieve a book along with its genres, series, and authors?

I used GROUP_CONCAT along with the suggested query to combine multiple rows of results in to one:

SELECT book.*,
GROUP_CONCAT(author.name) as 'authors',
GROUP_CONCAT(series.title) as 'series',
GROUP_CONCAT(genre.name) as 'genres'
FROM book
LEFT OUTER JOIN book_author ON book.id=book_author.book_id
JOIN author ON book_author.author_id = author.id
LEFT OUTER JOIN book_series ON book.id=book_series.book_id
JOIN series ON book_series.series_id = series.id
LEFT OUTER JOIN book_genre ON book.id=book_genre.book_id
JOIN genre ON book_genre.genre_id = genre.id;

Given 3 genres, 1 author, and 1 series... the resulting output is:

[
    {"id":1,
    "title":"Harry Potter and the Sorcerer's Stone",
    "summary":"When mysterious letters start arriving on his doorstep, Harry Potter has never heard of Hogwarts School of Witchcraft and Wizardry.\n\nThey are swiftly confiscated by his aunt and uncle.\n\nThen, on Harrys eleventh birthday, a strange man bursts in with some important news: Harry Potter is a wizard and has been awarded a place to study at Hogwarts.\n\nAnd so the first of the Harry Potter adventures is set to begin.",
    "pages":223,
    "published_date":"1997-06-26T04:00:00.000Z",
    "ddc":"823.914",
    "age_rating":12,
    "reviews_rating":89,
    "genres": "Young Adult,Fiction,Fantasy",
    "authors": "J.K. Rowling,J.K. Rowling,J.K. Rowling",
    "series": "Harry Potter,Harry Potter,Harry Potter"
}
]

I think I understand why it is repeating results. 4 genres would cause 1 author and 1 series to repeat 4 times. Is there a solution to stop that from happening?

CodePudding user response：

Using the MySQL GROUP_CONCAT() function along with its DISTINCT attribute will return a single row from a query with the requested column values concatenated together.

This answer returns a single row with all the genres, authors, and series instead of multiple rows of redundancy.

Code solution:

const express = require('express');

const Router = express.Router();

const mysqlConnection = require('../connection');

Router.get('/all', (req, res) => {
  mysqlConnection.query(`
    SELECT book.*,
    GROUP_CONCAT(DISTINCT author.name) as 'authors',
    GROUP_CONCAT(DISTINCT series.title) as 'series',
    GROUP_CONCAT(DISTINCT genre.name) as 'genres'
    FROM book
    LEFT OUTER JOIN book_author ON book.id = book_author.book_id
      JOIN author ON book_author.author_id = author.id
    LEFT OUTER JOIN book_series ON book.id = book_series.book_id
      JOIN series ON book_series.series_id = series.id
    LEFT OUTER JOIN book_genre ON book.id = book_genre.book_id
      JOIN genre ON book_genre.genre_id = genre.id
    GROUP BY book.title
  `, (err, rows, fields) => {
    if (err) { console.log(err); return; }
    res.send(rows);
  });
});

module.exports = Router;

GROUP BY book.title will return all books instead of just the last one.

CodePudding user response：

You can combine the four database queries into one:

SELECT book.id, ..., book.reviews_rating,
  group_concat(distinct genre.name) as genre_name,
  group_concat(distinct author.name) as author_name,
  group_concat(distinct series.title) as series_title
FROM book
LEFT OUTER JOIN (book_genre ON book.id = book_genre.book_id
JOIN genre ON genre.id = book_genre.genre_id)
LEFT OUTER JOIN (book_author ON book.id = book_author.book_id
JOIN author ON author.id = book_author.author_id)
LEFT OUTER JOIN (book_series ON book.id = book_series.book_id
JOIN series ON series.id = book_series.series_id)
GROUP BY book.id, ..., book.reviews_rating

Without the group_concat and GROUP BY, this would give you a results table with two rows (because of the two genres) and redundant entries in the other columns. If the book had two authors and three series, you would get 2*2*3=12 rows. Transforming that into the JSON format you expect is a bit of work, but thanks to the fewer database calls it should be faster overall.

(The left outer joins ensure that even if a book has no genre, author, or series, you get at least one row, with the genre or author name or the series title as null.)