SQL join and group items as an array

Question

I have the following SQL

SELECT articles.id, articles.title, tags.name AS tags
FROM articles
LEFT JOIN article_tag_association ON articles.id = article_tag_association.article_id
LEFT JOIN tags ON tags.id = article_tag_association.tag_id

This works OK except it creates a row for each tag an article has, which messes with the limit

e.g.

[
 "0" => ["id" => "1", "title" => "test", "tags" => "tag1"],
 "1" => ["id" => "1", "title" => "test", "tags" => "tag2"],
 "2" => ["id" => "2", "title" => "test2", "tags" => "tag1"],
]

(only 2 articles but three rows)

is there a way to make it return each article with an array of tags?

something like:

[
 "0" => ["id" => "1", "title" => "test", "tags" => ["tag1", "tag2"]],
 "1" => ["id" => "2", "title" => "test2", "tags" => ["tag1"]],
]

Answer 1

by default you cannot return an array. but you can decorate/concatenate your columns to produce an array like string. if its a good idea? depends on your situation. Also please be aware MySQL has some limitations for group_concat (will only return 1024*chars)

anyway just for test purpose you can try this:

SELECT 
    concat(
    '[',
    concat('ID => "', articles.id,'"'),
    concat('Title => "', articles.title,'"'),
    concat('Tags => [', GROUP_CONCAT(concat('"',tags.name, '"')), ']'),
    ']'
    ) as Array_String
FROM
    articles
        LEFT JOIN
    article_tag_association ON articles.id = article_tag_association.article_id
        LEFT JOIN
    tags ON tags.id = article_tag_association.tag_id
GROUP BY articles.id

this will give you each row as an array, if you want everything in one line put them all under a group_concat.

note: if your result is larger than 1024 char you have to use

SET group_concat_max_len = 1000000; >> size of your string length

PS: haven't tested above code. test it :)