mysql group concat into multiple fields

Question

I have a recipe table, called recipes. There is the IDRecipe field and other parameters of the recipe except the categories. Categories are multi dimensional, so I have another table that connects one to many with one recipe. It is called category table (table 1 below). As you will see below, one recipe can have multiple categories in multiple dimensions. So I have another table (table 2) that describes the categories and dimensions, also below:

-- Table 1
CREATE TABLE `recepti_kategorije` (
  `IDRecipe` int(11) NOT NULL,
  `IDdimenzija` int(11) NOT NULL,
  `IDKategorija` int(11) NOT NULL,
  KEY `Iskanje` (`IDdimenzija`,`IDKategorija`,`IDRecipe`) USING BTREE,
  KEY `izvlecek_recept` (`IDdimenzija`,`IDRecipe`),
  KEY `IDRecipe` (`IDRecept`,`IDdimenzija`,`IDKategorija`) USING BTREE,
  KEY `kategorija` (`IDKategorija`,`IDdimenzija`,`IDRecipe`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;

INSERT INTO `recepti_kategorije` VALUES 
(1,1,1),
(1,1,2),
(1,2,3),
(1,3,2);

-- Table 2
CREATE TABLE `recipes_dimensions` (
  `IDDimenzija` int(11) NOT NULL,
  `IDKategorija` int(11) NOT NULL,
  `Ime` char(50) COLLATE utf8_slovenian_ci NOT NULL,
  KEY `IDDmenzija` (`IDDimenzija`,`IDKategorija`) USING BTREE,
  KEY `IDKategorija` (`IDKategorija`,`IDDimenzija`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;

INSERT INTO `recipes_dimensions` VALUES 
(1,1,'cheese'),
(1,2,'eggs'),
(1,3,'meat'),
(1,4,'vegetables'),
(2,1,'main dish'),
(2,2,'sweet'),
(2,3,'soup'),
(3,1,'summer'),
(3,2,'winter');

-- Table 3
CREATE TABLE `recepti_dimenzije_glavne` (
  `IDDimenzija` int(11) NOT NULL,
  `DimenzijaIme` char(50) COLLATE utf8_slovenian_ci DEFAULT NULL,
  PRIMARY KEY (`IDDimenzija`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;

INSERT INTO `recepti_dimenzije_glavne` VALUES 
(1,'ingredient'),
(2,'type'),
(3,'season');

Table 2 is the key table to find out the legend of each dimensions and each category.

So from this example we see that my recipe with ID1 has the tag: cheese and eggs from dimension 1 and is soup for winter season.

Now on my recipes page I need to get all this out to print the names of each dimension together with all the category names.

Ok, so there is another table, table 3, to get the names of the dimensions out:

Now what I need is a query that would get me at the same time for recipe ID=1 all the dimensions group concatenated with names, like:

ingredient: cheese, eggs | type: soup | season: winter

I tried doing a query for each of them in SELECT statement and it works, but I need 8 select queries (in total I have 8 dimensions, for the example I only wrote 3), my select query is:

SELECT
    r.ID
    (
        SELECT
            group_concat(ime SEPARATOR ', ')
        FROM
            recepti_kategorije rkat
        JOIN recepti_dimenzije rd ON rd.IDKategorija = rkat.IDKategorija
        AND rd.IDDimenzija = rkat.IDdimenzija
        WHERE
            rkat.IDRecipe = r.ID
        AND rkat.IDDimenzija = 1
        ORDER BY
            ime ASC
    ) AS ingredient,
    (
        SELECT
            group_concat(ime SEPARATOR ', ')
        FROM
            recepti_kategorije rkat
        JOIN recepti_dimenzije rd ON rd.IDKategorija = rkat.IDKategorija
        AND rd.IDDimenzija = rkat.IDdimenzija
        WHERE
            rkat.IDRecipe = r.ID
        AND rkat.IDDimenzija = 2
        ORDER BY
            ime ASC
    ) AS type,
    (
        SELECT
            group_concat(ime SEPARATOR ', ')
        FROM
            recepti_kategorije rkat
        JOIN recepti_dimenzije rd ON rd.IDKategorija = rkat.IDKategorija
        AND rd.IDDimenzija = rkat.IDdimenzija
        WHERE
            rkat.IDRecipe = r.ID
        AND rkat.IDDimenzija = 3
        ORDER BY
            ime ASC
    ) AS season
FROM
    recipes r
WHERE
    r.ID = 1

That works, but it is somehow slow because the explain says it is searching like 6-8 rows each time and it is a long query and I don't get the names of the dimensions out because I need another join.

What would be optimal way to get all the dimensions separated into fields and concated with category names? I need to have this optimised as this is for one recipe presentation that happens each second, I can not fool around here. And whta indexes do I need so that this would be fast.

Must be a transcription error, from when you changed the names from Slovenian to English - Fixed — Strawberry, Nov 18 '16 at 17:52
Incidentally, this query results in a syntax error - so when you say 'this works', that's not quite correct — Strawberry, Nov 18 '16 at 17:54
If it was me, I'd start over, with no correlated subqueries - but without all the relevant tables, a sample data set, and a desired result, it's really too difficult to help. We can come back and look at indexes again once we have a sensible query. — Strawberry, Nov 18 '16 at 18:00
I would stay start over. Your table structures don't seem to make much sense — e4c5, Nov 19 '16 at 02:32

score 0 · Answer 1 · answered Nov 18 '16 at 21:41

Something like below, not sure I typed the table/column names right or not, but should be easy to debug:

  SELECT c.ID,GROUP_CONCAT(CONCAT(d.DimenzijaIme,': ',c.imes) SEPARATOR ' | ')
  FROM (
    SELECT
        r.ID,rkat.IDDimenzija,
        group_concat(rd.ime SEPARATOR ', ' ORDER BY rd.ime) AS imes
    FROM recepti_kategorije rkat
    JOIN recepti_dimenzije rd
    ON rd.IDKategorija = rkat.IDKategorija
    AND rd.IDDimenzija = rkat.IDdimenzija
    INNER JOIN recipes r
    ON r.ID=rkat.IDRecipe
    GROUP BY r.ID,rkat.IDDimenzija) c
  INNER JOIN recepti_dimenzije_glavne d
  ON d.IDDimenzija=c.IDDimenzija
  GROUP BY c.ID

mysql group concat into multiple fields

1 Answers1