20 6月 2017

[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


Django

上面所提到的,都是MySQL的方法,那在Django中要如何透過Django的ORM做到這個功能呢? 就要靠Django的Aggregate class 來處理。

首先,要先繼承Aggregate,將MySQL的 Function包裝成Aggregate Wrapper
如果看 Django source code (django.db.models.aggregates.py)
可以發現常用來aggregate的 Sum, Count, Avg, Max, Min 都是用這種作法做的
from django.db.models import Aggregate, CharField

class GroupConcat(Aggregate):
    # GROUP_CONCAT(DISTINCT field_name SEPARATOR ', ')
    function = 'GROUP_CONCAT'
    template = "%(function)s(%(distinct)s %(expressions)s %(separator)s)"

    def __init__(self, expression, distinct=False, separator=', ', **extra):
        super(GroupConcat, self).__init__(
            expression,
            distinct='DISTINCT' if distinct else '',
            separator="SEPARATOR '%s'" % separator,
            output_field=CharField(),
            **extra
        )

接下來就可以用Django的ORM的乾淨語法得到我們要的結果了
GroupConcat的 separator指定為 ', ' (含空格) 可以依需求修改
其實還可以指定Order By,如果有需求的話再根據MySQL的文件修改GroupConcat的template
class Author(models.Model):
    author_id = models.AutoField(primary_key=True)
    author_name = models.CharField(max_length=32)

class Book(models.Model):
    id = models.AutoField(primary_key=True)
    author = models.ForeignKey(Author, relate_name="books")
    book_name = models.CharField(max_length=32)

query = Author.objects.annotate(book_name=GroupConcat('books__book_name'))
for author in query:
    print("author_name=%s book_name=%s" % (author.author_name, author.book_name))


Reference:

沒有留言: