Филтриране на агрегиращ Count в Django

Публикувано на

ORM-ът на Django предлага възможност за лесно използване на агрегиращи SQL функции, като COUNT, SUM, MAX, MIN, AVG. Когато имаме две таблици с релация едно към много е много лесно да преброим всички "деца" на основният запис. Нека да разгледам следният пример. Имаме следните модели:

from django.db import models

class Category(models.Model):
title = models.CharField(max_length=128)
class Entry(models.Model):
category = models.ForeignKey(Category, related_name='entries')
title = models.CharField(max_length=128)
content = models.TextField()
is_published = models.BooleanField()

Имаме категории и към всяка категория може да имаме много статии. Ако искаме с една SQL заявка да вземем всички категории и броя на статиите във всяка категория можем да го направим така:

Category.objects.annotate(entries_count=Count('entries'))

Което от своя страна генерира следната заявка:

SELECT "blog_category"."id",
"blog_category"."title",
COUNT("blog_entry"."id") AS "entries_count"
FROM "blog_category"
LEFT OUTER JOIN "blog_entry" ON ("blog_category"."id" = "blog_entry"."category_id")
GROUP BY "blog_category"."id",
"blog_category"."title"

Какво обаче трябва да се направи ако искаме да вземем бройката само на публикуваните статии? Със SQL е лесно. Можем да използваме операторът CASE и заявката ще придобие следният вид:

SELECT "blog_category"."id",
"blog_category"."title",
COUNT(CASE WHEN "blog_entry"."is_published" THEN 1 END) AS "entries_count"
FROM "blog_category"
LEFT OUTER JOIN "blog_entry" ON ("blog_category"."id" = "blog_entry"."category_id")
GROUP BY "blog_category"."id",
"blog_category"."title"

Това обаче не е лесно за реализация с Django ORM-a, тъй като не можем да използваме CASE. Може да се използва extra метода за вмъкване на чист SQL, но пак е трудно слагането на GROUP BY, без django да знае, че ще има агрегираща функция. Намерих един снипет в djangosnippets, който дава възможност за генериране на CAUNT със CASE, но нещо мисля, че не функционира както трябва, а и задачата е твърде проста за да вмъквам цял такъв снипет.

Решението на проблема се оказа по-просто от колкото очаквах. След като разгледах отговорите на този въпрос в stackoverflow, ми направи впечатление, че един човек беше предложил решение със SUM вместо с COUNT. Това решение също имаше CASE, но това ме наведе на мисълта че BooleanField всъшност е integer в базата данни (както и в Python), от където True е 1-ца, a False e 0. Ако се съберат всички 1-ци, ще се получи точната бройка на публикуваните статии. За това заявката ми се променя по следният начин:

Category.objects.annotate(entries_count=Sum('entries__is_published'))

Което от своя страна автоматично се обръща в следният SQL:

SELECT "blog_category"."id",
"blog_category"."title",
SUM("blog_entry"."is_published") AS "entries_count"
FROM "blog_category"
LEFT OUTER JOIN "blog_entry" ON ("blog_category"."id" = "blog_entry"."category_id")
GROUP BY "blog_category"."id",
"blog_category"."title"

Това се оказва workaround, но в моя случай върши работа. Надявам се това да може свърши работа и на други хора, имащи същия проблем и разчитащи само на едно Boolean поле, за да разграничават записите, които им се налага да преброят. За по-сложни ситуации няма да свърши работа и трябва да се прибегне до снипет, като този към който дадох линк малко по-нагоре. Би било добре да има вградена функционалност в Django, поне за по-често срещаните случаи.

Ако някой е имал подобен проблем и е намерил друго решение, спокойно може да сподели в коментарите.

 

П.П.

Оказа се, че това не може да се ползва с PostgreSQL база данни, тъй като там си има boolean тип за разлика от MySQL и SQLite. Трябва да намеря друго решение.

 

П.П.

Намерих решение което да работи за всички бази данни. Проблемът е, че е недокументиран хак, но на този етап върши работа.

Category.objects.annotate(entries_count=Count('entries',
field='CASE WHEN blog_entries.is_published THEN 1 END'))

Подавам аргумент field на агрегиращата функиця Count, като стойността представлява моето CASE условие. Дори почти се получава елегантно решение. Причината това да работи може да бъде видяна в кода на Django. Ще копирам само съществената част

class Aggregate(object):
"""
Default SQL Aggregate.
"""
is_ordinal = False
is_computed = False
sql_template = '%(function)s(%(field)s)'

def __init__(self, col, source=None, is_summary=False, **extra):
"""Instantiate an SQL aggregate
...
"""
...
self.extra=extra
...

...

def as_sql(self, qn, connection):
"Return the aggregate, rendered as SQL."
...
params = {
'function': self.sql_function,
'field': field_name
}
params.update(self.extra)
return self.sql_template % params

Този клас се наследява от Count и тъй като никъде в аргументите на __init__ няма filed той автоматично отива в extra, а после в метода as_sql обновява params, които се използват за генериране на SQL от темплейта sql_template.

Така благодарение на този хак можем да сложим какъвто пожелаем SQL (стига да е валиден) във COUNT (или която и да е друга агрегираща функция). Същевременно се възползваме от Django ORM-а автоматично да направи необходимия JOIN както и да генерира GROUP BY вместо нас.

blog comments powered by Disqus