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))
1 則留言:
張貼留言