1

Statement I came up with:

   SELECT p.user_id AS started_by,
          p.position,
          p.created AS started_time,
          GROUP_CONCAT(
            c.id,
            c.user_id,
            c.comment,
            c.created) AS comments
     FROM pointers AS p
     JOIN comments AS c
       ON p.id = c.pointer_id
    WHERE p.archive_id = 3
 GROUP BY p.id

It gets me:

enter image description here

What happens is that GROUP_CONCAT() simply concatenates c.id . c.user_id c.comment and c.created. I cannot even explode that value in any meaningful way. (Notice how date and id are together as 1 string).

I don't want to create separate query for comments. OR should I?

IN any case, I need orderby c.created the comments, and have two delimiters. for example:

comments => 1,1,text this is!,2014-12-20 17:52:02;3,1,asasd,2014-12-20 20:46:40
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • 2
    You might want to approach this problem from a different way — `GROUP_CONCAT` allows you to use `SEPARATOR` but we cannot sure that the separator remains unique (i.e. not found in any of the field values). Also, `GROUP_CONCAT` has a maximum length of 1024 characters. It might be a good idea to have a separate query for comments. – Terry Dec 20 '14 at 18:13
  • @Terry thanks, going that way right now :) –  Dec 20 '14 at 18:18
  • you can increase the 1024 byte limit with `SET group_concat_max_len = 4096;` before exec the query – Matteo Dec 20 '14 at 20:03
  • @Matteo yes I'm aware of that, but 4096 will be enough? you never know. –  Dec 20 '14 at 21:13

1 Answers1

0

How about defining a separator for the concatenation of the columns and another one for the group itself

GROUP_CONCAT(concat_ws(',',
                       c.id,
                       c.user_id,
                       c.comment,
                       c.created)
            separator ';') AS comments
juergen d
  • 201,996
  • 37
  • 293
  • 362