Hey, I'm a new member to this group and I would like to suggest
implementing a custom filter for date_hiearchy.
The date_hierarchy filter is currently relying on the default filter
implementation by Django Admin.
*I think we can use the heirarchical nature of the date hierarchy to
generate a better filter condition.*
A URL generated by a the date hierarchy template tag might look like this:
admin/app/model?created__year=2017&created__month=12&created__day=16
The query generated by this URL will look like this (Postgresql):
where created between '2017-01-01' and '2017-31-12' and extract('month',
created) == 12 and extract('day', created) == 16;
The problem with this condition is that it *uses databse functions to
filter the date which makes it very difficult for the database to utilize
range based indexes (such as btree). *
I'm sure many developers (me included) are adding btree indexes on the date
hierarchy field to support such queries but the generated filter prevents
the database from using them.
There are solutions outside of Django for this problem such as function
based indexes but those come at a cost which can easily be avoided.
Another approach from within Django is to simplify the condition in a way
that the database can *better utilize range indexes:*
where created > '2017-12-16' and created < '2017-12-17';
I wrote about this problem in this blog post
<https://codeburst.io/django-admin-range-based-date-hierarchy-37955b12ea4e>
and implemented the above as a SimpleListFilter
<https://github.com/hakib/django-admin-lightweight-date-hierarchy/blob/master/django_admin_lightweight_date_hierarchy/admin.py#L47>
in this package.
<https://github.com/hakib/django-admin-lightweight-date-hierarchy>
To implement the simplified condition within Django I suggest adding the
following to ChangeList:
- Identify date_heirarchy fields using the following pattern:
re.compile(*r'^{}__(day|month|year)$'.format(self.date_hierarchy_field*))
- In ChangeList.get_filters
<https://github.com/django/django/blob/master/django/contrib/admin/views/main.py#L137>,
after applying the custom ListFilters and before applying the "default"
filtering on what's left; if date_hierarchy is defined for the model,
Identify the filters, apply a range based filter
<https://github.com/hakib/django-admin-lightweight-date-hierarchy/blob/master/django_admin_lightweight_date_hierarchy/admin.py#L10>
and remove the values from the parameter list.
*Other considerations*
- This change is backward compatible.
- Will most likely improve performace of large list views with
date_heirarchy.
- The custom filter is applied after the ListFilters so projects that
implemented their own filters on date hierarchy fields will not be effected.
Please let me know what you think and if there are other things I haven't
considered.
Haki Benita.
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/24ab9a20-cd8e-40c4-a257-3c8b4ee12fde%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
