Group concat to include corresponding data

Question

This is my table structure:

orders
--------------------------
id        | customer_name
--------------------------
23        | John Doe
24        | Jane Doe

order_comments
--------------------------------------------------------------------
id | order_id  | username | created_at  | comment
--------------------------------------------------------------------
1  | 23        | Bob      | 2019-04-01  | my first comment
2  | 23        | Jim      | 2019-04-03  | another comment
3  | 24        | Jim      | 2019-04-05  | testing
4  | 24        | Jim      | 2019-04-06  | testing again

I want to select the comments concatenated by newlines but also include the username and created_at. This is what I have so far:

select *
from (SELECT order_id, GROUP_CONCAT(`comment` order by id desc SEPARATOR '\n') as comments
      FROM `order_comments`
      group by order_id) comments

Result:

order_id | comments
--------------------------------
23       | my first comment
         | another comment
--------------------------------
24       | testing
         | testing again

Here's my desired result to include the username and created at for each comment concat:

order_id | comments
--------------------------------
23       | Bob on 2019-04-01:
         | my first comment
         |
         | Jim on 2019-04-03:
         | another comment
---------------------------------
24       | Jim on 2019-04-05:
         | testing
         |
         | Jim on 2019-04-06:
         | testing again

How can I get the desired result?

Answer 1

Try this one

select *
from (SELECT order_id, GROUP_CONCAT(
      DISTINCT CONCAT(username,’ on ‘, created_at, ‘:’, comment order by id desc SEPARATOR '\n') as comments
      FROM `order_comments`
      group by order_id) comments