Django - Paginating over n tables sorted values

Question

I've set up a little history audit trail (django-simple-history) for a few of my key models and an admin view to paginate the (aggregated) entries which are sorted by descending date. The problem is, the method I'm using is suboptimal...

historical_foo = Foo.history.all()
historical_bar = Bar.history.all()
historical_qux = Qux.history.all()

#sort the aggregate by modified date
result_list = sorted(chain(historical_foo, historical_bar, historical_qux), key=attrgetter('history_date'), reverse=True)

paginator = Paginator(result_list, 100)

try:
    result = paginator.page(page_num)
    #...

This surely won't scale well as this these tables gets large. Is there a way to push the aggregating and sorting logic down into Django / DB or an alternative approach for the same outcome?

score 2 · Answer 1 · answered Apr 23 '13 at 16:59

You could hold everything in one table with contenttypes:

from django.db import models
from django.contrib.contenttypes.models import ContentType
from django.contrib.contenttypes import generic

class HistoryRecord(models.Model):
    history_date = models.DateTimeField()
    history_id = models.PositiveIntegerField()
    content_type = models.ForeignKey(ContentType)
    content = generic.GenericForeignKey('content_type', 'history_id')

then you would need to create those:

poll = Poll.history.all()[0]
record = HistoryRecord(content=poll, history_date=poll.history_date)
record.save()

or you could subclass HistoricalRecords:

class IndexedHistoricalRecords(HistoricalRecords):
    def create_historical_record(self, instance, type):
        history_user = getattr(instance, '_history_user', None)
        manager = getattr(instance, self.manager_name)
        attrs = {}
        for field in instance._meta.fields:
            attrs[field.attname] = getattr(instance, field.attname)
        content = manager.create(history_type=type, history_user=history_user, **attrs)
        record = HistoryRecord(content=poll, history_date=poll.history_date)
        record.save()

then you can query one table:

result_list = HistoryRecord.objects.all()
paginator = Paginator(result_list, 100)
...

score 1 · Answer 2 · edited May 23 '17 at 12:27

1

All models could inherit from one table (via one-to-one key).

That way you can sort by django ORM on this field using base table and later on get proper instances.

See that discussion for help with getting final instances.

edited May 23 '17 at 12:27

Community

1
1

answered Oct 30 '12 at 08:55

Krzysztof Szularz

5,151
24
35

Django - Paginating over n tables sorted values

2 Answers2