Skip to main content

Improved API for aggregating using Subquery

Project description

https://travis-ci.org/martsberger/django-sql-utils.svg?branch=master

Django SQL Utils

This package provides utilities for working with Django querysets so that you can generate the SQL that you want, with an API you enjoy.

Subquery Aggregates

The Count aggregation in Django:

Parent.objects.annotate(child_count=Count('child'))

generates SQL like the following:

SELECT parent.*, Count(child.id) as child_count
FROM parent
JOIN child on child.parent_id = parent.id
GROUP BY parent.id

In many cases, this is not as performant as doing the count in a SUBQUERY instead of with a JOIN:

SELECT parent.*,
       (SELECT Count(id)
        FROM child
        WHERE parent_id = parent.id) as child_count
FROM parent

Django allows us to generate this SQL using The Subquery and OuterRef classes:

subquery = Subquery(Child.objects.filter(parent_id=OuterRef('id')).order_by()
                    .values('parent').annotate(count=Count('pk'))
                    .values('count'), output_field=IntegerField())
Parent.objects.annotate(child_count=Coalesce(subquery, 0))

Holy cow! It’s not trivial to figure what everything is doing in the above code and it’s not particularly good for maintenance. SubqueryAggregates allow you to forget all that complexity and generate the subquery count like this:

Parent.objects.annotate(child_count=SubqueryCount('child'))

Phew! Much easier to read and understand. It’s the same API as the original Count just specifying the Subquery version.

Easier API for Exists

If you have a Parent/Child relationship (Child has a ForeignKey to Parent), you can annotate a queryset of Parent objects with a boolean indicating whether or not the parent has children:

from django.db.models import Exists

parents = Parent.objects.annotate(
    has_children=Exists(Child.objects.filter(parent=OuterRef('pk'))
)

That’s a bit more boilerplate than should be necessary, so we provide a simpler API for Exists:

from sql_util.utils import Exists

parents = Parent.objects.annotate(
    has_children=Exists('child')
)

The child queryset can be filtered with the keyword argument filter. E.g.,:

parents = Parent.objects.annotate(
    has_child_named_John = Exists('child', filter=Q(name='John'))
)

The sql_util version of Exists can also take a queryset as the first parameter and behave just like the Django Exists class, so you are able to use it everywhere without worrying about name confusion.

Installation and Usage

Install from PyPI:

pip install django-sql-utils

Then you can:

from sql_util.utils import SubqueryCount

And use that as shown above.

In addition to SubqueryCount, this package provides

  • SubqueryMin

  • SubqueryMax

  • SubquerySum

  • SubqueryAvg

If you want to use other aggregates, you can use the generic SubqueryAggregate class. For example, if you want to use Postgres’ ArrayAgg to get an array of Child.name for each Parent:

from django.contrib.postgres.aggregates import ArrayAgg

aggregate = SubqueryAggregate('child__name', aggregate=ArrayAgg)
Parent.objects.annotate(child_names=aggregate)

Or subclass SubqueryAggregate:

from django.contrib.postgres.aggregates import ArrayAgg

class SubqueryArrayAgg(SubqueryAggregate)
    aggregate = ArrayAgg
    unordered = True

Parent.objects.annotate(avg_child_age=SubqueryArrayAgg('child__age'))

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

django-sql-utils-0.2.1.tar.gz (8.7 kB view details)

Uploaded Source

Built Distribution

django_sql_utils-0.2.1-py3-none-any.whl (11.7 kB view details)

Uploaded Python 3

File details

Details for the file django-sql-utils-0.2.1.tar.gz.

File metadata

  • Download URL: django-sql-utils-0.2.1.tar.gz
  • Upload date:
  • Size: 8.7 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/1.13.0 pkginfo/1.5.0.1 requests/2.21.0 setuptools/40.6.2 requests-toolbelt/0.9.1 tqdm/4.31.1 CPython/3.7.2

File hashes

Hashes for django-sql-utils-0.2.1.tar.gz
Algorithm Hash digest
SHA256 f0a222d0eacf2bec62317f78a24d41d0208a7cb436a0276bd1b3b5d4dc571848
MD5 67c4b15dc514a0c7020b8caa44203f6e
BLAKE2b-256 f1ac8ea2b8202de85c59560e3f7f5f4b57de461c387e512eb40574de11d4fa38

See more details on using hashes here.

File details

Details for the file django_sql_utils-0.2.1-py3-none-any.whl.

File metadata

  • Download URL: django_sql_utils-0.2.1-py3-none-any.whl
  • Upload date:
  • Size: 11.7 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/1.13.0 pkginfo/1.5.0.1 requests/2.21.0 setuptools/40.6.2 requests-toolbelt/0.9.1 tqdm/4.31.1 CPython/3.7.2

File hashes

Hashes for django_sql_utils-0.2.1-py3-none-any.whl
Algorithm Hash digest
SHA256 7c468996bf34a00bebdb58f945a5527374f3ad180f860b8b2d14964042ff0efd
MD5 ffa2ddb9b288eb36ce5d5112d41a692e
BLAKE2b-256 452f3db39e75be819933300ba7139ed10d7c350c2095cfb6b4542eb1d53c5ab9

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page