Django ORM复杂查询

September 09, 2014 | 16:29 Django SQL

Django的ORM为我们提供了强大的数据库管理方式,不仅有极其方便的基于外键的跨表查询,并有filter()annotate()order_by()等非常实用的过滤器。不过,有时我们还是会遇到很奇葩的查询需求,在此就对最近这个项目中一些比较纠结的查询以及解决方案做个记录。

分组查询,并附加

具体描述

我们有两个模型。

供应商

class Supplier(models.Model):
    name = models.CharField()

采购

class Purchase(models.Model):
    price = models.FloatField()
    supplier = models.ForeignKey(Supplier)
    create_time = models.DateTimeField(auto_now_add=True)

现在,我们要输出一个供应商列表,包含每个供应商最近一次采购的价格。

解决方案

这一问题得分成两个查询来做。

suppliers = Supplier.objects.all().annotate(recent_purchase=Max('purchase__create_time'))
purchases = Purchase.objects.filter(create_time__in=[s.recent_purchase for s in suppliers])

最后,再合并起来:

for i, s in enumerate(suppliers):
    s.recent_price = purchases[i].price

参见

StackOverflow: Django Query That Get Most Recent Objects From Different Categories

按月度、季度分组

具体描述

对于一组数据,我们想提供按月度、季度的分组汇总信息。

还是以一个Purchase模型为例:

class Purchase(models.Model):
    name = models.CharField()
    price = models.FloatField()
    date = models.DateField()

解决方案

这里需要用到各类SQL自带的函数,下文以MySQL为例。

在月视图中,我们输出的是不重复的“年-月”组合,涵盖了所有Purchase实例出现的date

months = Purchase.objects.all() \
    .extra(select={'year': "YEAR(date)", 'month': "MONTH(date)"}) \
    .values('year', 'month').order_by('year', 'month').distinct()

这样出来的结果集可以显示成这样:

2014-01
2014-02
2014-03

季度的问题同样可以使用这个方案,稍微改下代码:

quarters = Purchase.objects.all() \
    .extra(select={'year': "YEAR(date)", 'quarter': "QUARTER(date)"}) \
    .values('year', 'quarter').distinct()

需要注意的是可能其他的SQL语言并没有提供如QUARTER()这样的函数的话,就得自己在SQL写筛选了。

拼音排序

具体描述

这个很好理解,就是将字段按照中文的拼音排序。

解决方案

本以为这是一个mission impossible,没想却是一个很好解决的问题,道具就是SQL提供的编码转换。

一般来说,我们的字段,如果是文本,尤其是还有中文文本的字段,用的都是UTF-8编码,字符集用的是utf8_general_ci,如果对于这个字段进行排序,你会发现文字是按照UTF-8编码顺序排序的。如果想以拼音排序,只需要临时将字段编码转换成GBK就可以了,GBK编码本身就是按照拼音顺序排序的。

books = Book.objects.all() \
    .extra(select={'gbk_title': 'convert(`book_book`.`title` using gbk)'}) \
    .order_by('gbk_title')

按条件统计

具体描述

Django中提供了annotate()函数用于SQL中的GROUP BY操作,不过,当我们的需求变得复杂时,就得自己写查询来操作了。

如我们有两个模型,MaterialPurchaseMaterial记录材料的物品的种类,Purchase记录每一次购买事件。它们的结构如下:

class Material(models.Model):
    name = models.CharField()

class Purchase(models.Model):
   material = models.ForeignKey(Material)
   is_active = models.BooleanField()

那么,怎样得出每个材料的活动的(is_active=True)购买次数呢?

解决方案

如果单纯要得到购买次数,不考虑is_active字段,那很好办,可以这样写:

materials = Material.objects.all() \
    .annotate(`purchase_count`=Count(`purchase`))

但如果要加入is_active这个条件,就不太好办了。因为这个字段不是在我们查询的主模型(即Material上),因此,针对这个筛选,我们需要手动写一段SQL。这里就用到extra()函数,把这段SQL添加到Django生成的语句中。如下:

materials = Material.objects.all() \
   .extra(select={'active_purchase_count': \
   'SELECT COUNT(`id`) FROM purchase_purchase AS p \ 
   WHERE p.is_active = 1'})

加上extra()之后,似乎就没有纯Django ORM那么优雅了,而且还要带上表名,所以最好使用some_queryset.query先看一下原始的SQL语句,再根据这个SQL加上额外的筛选。

Creative Commons BY-NC-ND 3.0