Grouping by date in groupconcat after concatenation

I've got a list of stores in a store table, I've got a storecomment table as well which contains multiple comments for each store. They are linked via the storeID foreign key.

This is a subquery for a much larger query to return multiple pieces of information into a single row sorted by each store. The aim of this sub query is to return all of the comments for that store into a single column per store in the format of yyyy-mm-dd - COMMENT - Name and ordered with the newest comment at the top. I can get the string working, however it always fails to order.

LEFT JOIN (SELECT group_concat(storecomment.CommentDate, ' - ', storecomment.Comment, ' - ', users.Name, '\n' SEPARATOR '') as storecomments, StoreID from storecomment
                       inner join users on storecomment.CommentUserID = users.UserID 
                       ORDER BY CommentDate DESC
                       group by StoreID) 
                       as storecomments on store.StoreID = storecomments.StoreID

This mostly works, however the ordering for comments fails and it comes out into the order they were entered.

I've also tried ordering by the first part of the concatinated string such as:

LEFT JOIN (SELECT group_concat(storecomment.CommentDate, ' - ', storecomment.Comment, ' - ', users.Name, '\n' SEPARATOR '') as storecomments, StoreID from storecomment
                       inner join users on storecomment.CommentUserID = users.UserID
                       group by StoreID
                       Order by UNIX_TIMESTAMP(SUBSTRING(storecomments,9)) DESC)
                       as storecomments on store.StoreID = storecomments.StoreID

And finally I tried converting the datetime to a unix timestamp and ordering that way, but it still fails to order:

LEFT JOIN (SELECT group_concat(storecomment.CommentDate, ' - ', storecomment.Comment, ' - ', users.Name, '\n' SEPARATOR '') as storecomments, StoreID from storecomment
                        inner join users on storecomment.CommentUserID = users.UserID
                        group by StoreID
                        Order by UNIX_TIMESTAMP(STR_TO_DATE(storecomment.CommentDate, '%Y-%m-%d %h:%i%p')) DESC
                        as storecomments on store.StoreID = storecomments.StoreID

I'm sure there is an easy way to sort this out, but I just can't see it. Does anyone have any suggestions?

Solution

You can order the way the values are joined together inside the group_concat, as it has built in support for order by.

Change your group_concat to look like this:

group_concat(storecomment.CommentDate, ' - ', storecomment.Comment, ' - ', users.Name, '\n' ORDER BY storecomment.CommentDate DESC SEPARATOR '')

example:

mysql> create table example (user_id integer, group_id integer);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into example values (1, 1), (1, 2), (1, 3), (2, 7), (2, 4), (2, 5);
Query OK, 6 rows affected (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select group_concat(group_id) from example group by user_id;
+------------------------+
| group_concat(group_id) |
+------------------------+
| 1,2,3                  |
| 7,4,5                  |
+------------------------+
2 rows in set (0.00 sec)

mysql> select group_concat(group_id order by group_id asc separator '-') from example group by user_id;
+------------------------------------------------------------+
| group_concat(group_id order by group_id asc separator '-') |
+------------------------------------------------------------+
| 1-2-3                                                      |
| 4-5-7                                                      |
+------------------------------------------------------------+
2 rows in set (0.00 sec)