dj-querytools

A collection of functions to make it a little easier to build aggregation SQL queries for Django projects

Installation

pip install dj-querytools

Usage

Simple aggregation:

Assuming the following model:

class Todo(models.Model):
    owner = models.CharField(max_length=30)
    title = models.CharField(max_length=30)
    status = models.CharField(max_length=30, choices=TODO_STATUSES)
    size = models.PositiveIntegerField(default=1)
    due = models.DateField()
    due_time = models.DateTimeField()

group_by_and_aggregate

Example

group_by_and_aggregate(
    qs,
    'size',
    'Sum'
)
>> 20

group_by_and_annotate

Performs the aggregation and groups by the provided field:

Example

result = group_by_and_annotate(
    qs,
    'status',
    'Count'
)
>> {'D': 5, 'N': 1, 'P': 8}

as_timeseries

qs = Todo.objects.filter(owner="Jack")
as_timeseries(
    qs,
    'due_time',
    'id',
    'Count',
    '2018-11-01',
    '2018-11-10'
)
>> [{'x': '2018-11-01', 'y': 3.0}, {'x': '2018-11-02', 'y': 3.0}, {'x': '2018-11-03', 'y': 0}, {'x': '2018-11-04', 'y': 2.0}, {'x': '2018-11-05', 'y': 2.0}, {'x': '2018-11-06', 'y': 0}, {'x': '2018-11-07', 'y': 0}, {'x': '2018-11-08', 'y': 0}, {'x': '2018-11-09', 'y': 0}, {'x': '2018-11-10', 'y': 0}]

pivot_table

Given a flat queryset, group it by one or more fields.

• fields are comma seperated
• optionally provide a serializer function to serialize items (otherwise it will just append the queryset row)

result = pivot_table(qs, 'owner,status', serialize_todo)

>> {'Jack': {'N': [{'title': 'Day year outside whatever int'}], 'P': [{'title': 'Answer society close example '}, {'title': 'Raise real individual general'}], 'D': [{'title': 'Number organization particula'}, {'title': 'Least activity herself than c'}, {'title': 'Country local pretty yourself'}]}, 'Jane': {'P': [{'title': 'Prevent do still teacher grou'}, {'title': 'Include they management first'}, {'title': 'Number court few loss sort mu'}, {'title': 'That federal end local dream '}, {'title': 'Able ask listen. Cost ball me'}, {'title': 'Bad know meeting compare room'}], 'D': [{'title': 'Through town stay able car di'}, {'title': 'Tree design candidate because'}]}}

periodic_breakdown

from querytools.tools import periodic_breakdown

Provide an aggregation broken down by a period (Trun(kind=)).

See: https://docs.djangoproject.com/en/2.1/ref/models/database-functions/#django.db.models.functions.TruncMonth

Defaults:

• kind='month'
• aggregate_field='ok'
• aggregation = 'Count'

# simple case:
result = periodic_breakdown(qs, 'due')

# slighly more fancy:
result = periodic_breakdown(
    qs,
    'due',
    aggregate_field='size',
    aggregation = 'Sum'
)

Other stuff

• For full functionality list, see spec.txt
• For examples, check example_app/tests.py

Contributing

Update the spec:

python manage.py test --testrunner=testreporter.runner.BDDTestRunner