[Django] Query with Group_Concat (MySQL)

以下為Table Author

author_id | author_name
----------+-----------
 1        | Jack
 2        | Alex
 3        | Bob
----------+-----------

以下為Table Book

id | author_id | book_name
---+-----------+----------
 1 | 1         | Hello
 2 | 1         | World
 3 | 1         | Wolf
 4 | 2         | Sex
---+-----------+----------

MySQL

Case1 (簡單的case)

希望將Book by Author Group 起來，然後將 book_name 全部 string concat 起來後，期望得到的結果如下：
author_id=1, book_name=Hello,World,Wolf
author_id=2, book_name=Sex

在MySQL中，提供了Aggregate Function: GROUP_CONCAT() 可以輕鬆做到這個功能

SQL Command

SELECT author_id, GROUP_CONCAT(book_name)
FROM Book
GROUP BY author_id

Case2 (較複雜的case, table join)

今天假設希望知道 Author的 Name 加上所有的 book_name String Concat 的結果
author_name=Jack, book_name=Hello,World,Wolf
author_name=Alex, book_name=Sex

SQL Command

SELECT A.author_name, GROUP_CONCAT(B.book_name) AS book_name
FROM Author A
JOIN Book B ON A.author_id = B.author_id
GROUP BY B.author_id