Django Admin date_hierarchy with zero queries
======================================= Django Admin lightweight date hierarchy =======================================
.. image:: https://badge.fury.io/py/django-admin-lightweight-date-hierarchy.svg :target: https://badge.fury.io/py/django-admin-lightweight-date-hierarchy
.. image:: https://github.com/hakib/django-admin-lightweight-date-hierarchy/actions/workflows/main.yml/badge.svg?branch=master :target: https://github.com/hakib/django-admin-lightweight-date-hierarchy/actions/workflows/main.yml?query=branch%3Amaster
.. image:: https://codecov.io/gh/hakib/django-admin-lightweight-date-hierarchy/branch/master/graph/badge.svg :target: https://codecov.io/gh/hakib/django-admin-lightweight-date-hierarchy
Django Admin date_hierarchy with zero queries
NOTE: Some of the functionality provided by this extension is included as part of Django <https://github.com/django/django/pull/9469>_ starting at version 2.1.
The built-in datehierarchy tag performs a query to find the dates for which there is data. On large tables this query can be very expensive.
To prevent additional queries, set `datehierarchydrilldown = False on the ModelAdmin. When drill-down is disabled the tag will generate a default range of dates based solely on the selected hierarchy level - without performing a query.
Default options for hierarchy levels:
- None - +-3 years from current year.
- Year - all months of the selected year.
- Month - all days of the selected month.
datehierarchydrilldown = True or when not set the default behaviour is preserved.
..
datehierarchy: https://docs.djangoproject.com/en/1.11/ref/contrib/admin/#django.contrib.admin.ModelAdmin.date_hierarchy
Support
Python>=3.7
Django 3.2, 4.2, >=5
Quickstart
Install django-admin-lightweight-date-hierarchy::
pip install django-admin-lightweight-date-hierarchy
Add it to your
INSTALLED_APPS:
.. code-block:: python
INSTALLED_APPS = ( ... 'djangoadminlightweightdatehierarchy', ... )
Add the following to any
ModelAdmin with date_hierarchy to prevent the default drill-down behaviour:
.. code-block:: python
@admin.register(MyModel) class MyModelAdmin(admin.ModelAdmin): date_hierarchy = 'created' datehierarchydrilldown = False
To change the default dates generated by the template tag for any level in the hierarchy, implement a function called
getdatehierarchydrilldown(self, yearlookup=None, month_lookup=None) on the ModelAdmin. The function receives the date hierarchy filter and is expected to return a list of dates to offer for drill-down.
For example, a custom drill-down that offers only past dates:
.. code-block:: python
import datetime import calendar
from django.utils import timezone from django.contrib import admin
@admin.register(MyModel) class MyModelAdmin(admin.ModelAdmin): date_hierarchy = 'created' datehierarchydrilldown = False
def getdatehierarchydrilldown(self, yearlookup, month_lookup): """Drill-down only on past dates."""
today = timezone.now().date()
if yearlookup is None and monthlookup is None: # Past 3 years. return ( datetime.date(y, 1, 1) for y in range(today.year - 2, today.year + 1) )
elif yearlookup is not None and monthlookup is None: # Past months of selected year. this_month = today.replace(day=1) return ( month for month in ( datetime.date(int(year_lookup), month, 1) for month in range(1, 13) ) if month <= this_month )
elif yearlookup is not None and monthlookup is not None: # Past days of selected month. daysinmonth = calendar.monthrange(yearlookup, monthlookup)[1] return ( day for day in ( datetime.date(yearlookup, monthlookup, i + 1) for i in range(daysinmonth) ) if day <= today )
Blog Post
More about the process of developing date hierarchy drill-down in this blog post
scaling django admin date hierarchy_.
.. _
scaling django admin date hierarchy: https://hakibenita.com/scaling-django-admin-date-hierarchy
RangeBasedDateHierarchyListFilter
Django filters the queryset for a given level in the date hierarchy using a database function to extract the relevent date part. For example, when filtering a queryset on a
created date field for November 2017, Django will execute the following query:
.. code-block:: sql
SELECT ... FROM app_model WHERE created BETWEEN '2017-01-01 00:00:00' AND '2017-12-31 23:59:59.999999' AND EXTRACT('month', created) = 11
A function is opaque to the database optimizer. If you have a range-based (btree) index on the field, using EXTRACT does not limit the range at all, and so the index is not utilized properly which might lead to a sub optimal execution plan.
There are several approaches to tackle this issue. For example, in databases that support function based indexes the developer can add an index on the specific function to try and improve the performace of the query. The downside to this approach is having to maintain additional indexes for each level of the hierarchy. Additional indexes slow down insert and update operations, and take up space.
Another approach is to simplify the condition used by Django to filter the queryset for any given level in the hierarchy:
.. code-block:: sql
SELECT ... FROM app_model WHERE created >= '2017-11-01 00:00:00' AND created < '2017-12-01 00:00:00'
This is what
RangeBasedDateHierarchyListFilter does.
To achieve the above query, add the following to your ModelAdmin:
.. code-block:: python
from django.contrib import admin from djangoadminlightweightdatehierarchy.admin import RangeBasedDateHierarchyListFilter
@admin.register(MyModel) class MyModelAdmin(admin.ModelAdmin): date_hierarchy = 'created'
list_filter = ( RangeBasedDateHierarchyListFilter, )
Blog Post
More about the motivation and the performace of
RangeBasedDateHierarchyListFilter in this blog post Django Admin Range-Based Date Hierarchy_.
.. _
Django Admin Range-Based Date Hierarchy`: https://codeburst.io/django-admin-range-based-date-hierarchy-37955b12ea4e
Running Tests
::
source
