9 Django Tips for Working with Databases

Small changes that make all the difference

ORMs offer great utility for developers but abstracting access to the database has its costs. Developers who are willing to poke around the database and change some defaults often find that great improvements can be made.

Aggregation with Filter

Prior to Django 2.0 if we wanted to get something like the total number of users and the total number of active users we had to resort to conditional expressions:

```python from django.contrib.auth.models import User from django.db.models import ( Count, Sum, Case, When, Value, IntegerField, )

User.objects.aggregate( totalusers=Count('id'), totalactiveusers=Sum(Case( When(isactive=True, then=Value(1)), default=Value(0), output_field=IntegerField(), )), ) ```

In Django 2.0 a filter argument to aggregate functions was added to make this a lot easier:

```python from django.contrib.auth.models import User from django.db.models import Count, F

User.objects.aggregate( totalusers=Count('id'), totalactiveusers=Count('id', filter=F('isactive')), ) ```

Nice, short and sweet.

If you are using PostgreSQL, the two queries will look like this: