#StackBounty: #mysql #group-concat GROUP_CONCAT numbering with multiple columns group by

Bounty: 50

I have a problem with a GROUP_CONCAT select which should also have the row numbering included similar to this question GROUP_CONCAT numbering
the difference is that i have to group by multiple columns.

As an example I have 2 tables review and review_detail.
Schema (MySQL v5.5)

create table review (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `submission_id` int(11) NOT NULL,
   PRIMARY KEY (`id`)
);

create table review_detail (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `review_id` int(11),
  `category_id` int(11),
  `rating` varchar(100),
  PRIMARY KEY (`id`)
);

insert into review (`id`, `submission_id`) values (1, 1), (2, 1), (3, 2), (4, 3), (5,1), (6,3), (7,2), (8,3);

insert into review_detail (`review_id`, `category_id`, `rating`)
values 
(1, 1, ' submission 1.1 cat 1'), (1, 2, ' submission 1.1 cat 2'),
(2, 1, ' submission 1.2 cat 1'), (2, 2, ' submission 1.2 cat 2'),
(3, 1, ' submission 2.1 cat 1'), (3, 2, ' submission 2.1 cat 2'),
(4, 1, ' submission 3.1 cat 1'), (4, 2, ' submission 3.1 cat 1'),
(5, 1, ' submission 1.3 cat 1'), (5, 2, ' submission 1.3 cat 2'),
(6, 1, ' submission 3.2 cat 1'), (6, 2, ' submission 3.2 cat 2'),
(7, 1, ' submission 2.2 cat 1'), (7, 2, ' submission 2.2 cat 2'),
(8, 1, ' submission 3.3 cat 1'), (6, 2, ' submission 3.3 cat 2')
;

Query #1

SELECT * FROM review;

| id  | submission_id |
| --- | ------------- |
| 1   | 1             |
| 2   | 1             |
| 3   | 2             |
| 4   | 3             |
| 5   | 1             |
| 6   | 3             |
| 7   | 2             |
| 8   | 3             |

Query #2

SELECT * FROM review_detail;

| id  | review_id | category_id | rating                |
| --- | --------- | ----------- | --------------------- |
| 1   | 1         | 1           |  submission 1.1 cat 1 |
| 2   | 1         | 2           |  submission 1.1 cat 2 |
| 3   | 2         | 1           |  submission 1.2 cat 1 |
| 4   | 2         | 2           |  submission 1.2 cat 2 |
| 5   | 3         | 1           |  submission 2.1 cat 1 |
| 6   | 3         | 2           |  submission 2.1 cat 2 |
| 7   | 4         | 1           |  submission 3.1 cat 1 |
| 8   | 4         | 2           |  submission 3.1 cat 1 |
| 9   | 5         | 1           |  submission 1.3 cat 1 |
| 10  | 5         | 2           |  submission 1.3 cat 2 |
| 11  | 6         | 1           |  submission 3.2 cat 1 |
| 12  | 6         | 2           |  submission 3.2 cat 2 |
| 13  | 7         | 1           |  submission 2.2 cat 1 |
| 14  | 7         | 2           |  submission 2.2 cat 2 |
| 15  | 8         | 1           |  submission 3.3 cat 1 |
| 16  | 6         | 2           |  submission 3.3 cat 2 |

Every review for a submission (foreign key = submission_id) have multiple review_detail entries with category_id (in my example only 2 categories (1,2) which are not relevant for the query).

I have to create a select where i get the GROUP_CONCAT grouped by submission_id and category_id.

The Concat string should return
Reviewer 1: {rating}, Reviewer 2: {rating}, Reviewer 3: {rating} etc..

e.g. for submission_id = 1 and category_id = 1 the group concat should return
Reviewer 1: submission 1.1 cat 1, Reviewer 2: submission 1.2 cat 1, Reviewer 3: submission 1.3 cat 1.

But i couldn’t get the numbering in the group concat correct.

I have done multiple tests so far.

Group with only one column counter (works):
https://www.db-fiddle.com/f/6hA4Vft1mQGdw2Pew2An2T/3
Reviewer 1: submission 1.1 cat 1 of review 1 / Reviewer 2: submission 3.3 cat 1 of review 8 / Reviewer 3: submission 2.2 cat 1 of review 7 / Reviewer 4: submission 3.2 cat 1 of review 6 / ... etc.

SELECT
    --review.submission_id,
    review_detail.category_id,
    @i,
    GROUP_CONCAT(
        CONCAT(
            'Reviewer ',
            @i := @i + 1,
            ': ',
            rating,
            ' of review ',  review_id
        )
    SEPARATOR ' / '
    ) concatText,
    @i := 0
FROM
    review_detail
LEFT JOIN review ON review.id = review_detail.review_id,
    (
SELECT
    @i := 0
) init
GROUP BY
    review_detail.category_id
ORDER BY
    review_detail.category_id ASC
;

Test with if and a compare against a string of the 2 grouped columns (doesn’t work):
https://www.db-fiddle.com/f/3woAVSw5hrav15jAmuWVdT/3
Reviewer 1: submission 1.1 cat 1 of review 1 / Reviewer 1: submission 1.2 cat 1 of review 2 / Reviewer 1: submission 1.3 cat 1 of review 5

SELECT
    submission_id,
    category_id,
    @i,
    @grp,
    CONCAT_WS("-", submission_id, category_id) AS catgroup,
    GROUP_CONCAT(
        CONCAT(
            'Reviewer ',
            @i := IF(
                @grp = CONCAT_WS("-", submission_id, category_id),
                @i + 1,
                IF(
                    @grp := CONCAT_WS("-", submission_id, category_id),
                    1,
                    1
                )
            ),
            ': ',
            rating,
            ' of review ',  review_id
        )
    ORDER BY review_id, submission_id, category_id 
    SEPARATOR ' / '
    ) concatText
FROM
    review_detail
LEFT JOIN review ON review.id = review_detail.review_id,
    (
SELECT
    @i := 0,
    @grp := ''
) init
GROUP BY
    review.submission_id,
    review_detail.category_id

So does anyone know a way to get the numbering in a GROUP_CONCAT call correct when multiple columns are grouped by?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.