0

I've a queryset which I want to sort using one of the columns, say date1, and if the value of date1 is None then use another column date2 for sorting and if date2 is also None then use datetime.max. So, this is what I came up with :

queryset = sorted(queryset, key= lambda row: row.date1 if row.date1 else row.date2 if row.date2 else datetime.max)

and then for paginating (with say 20 in one page) I'm sending back the id of last record of previous request and using that to get the next 20 items (since after sorting ids are now in in random order). Also the entries can be deleted by the users so, using Paginator.page and just fetching the consecutive pages in requests won't work.

This is how I find the index of the lastRecord for getting the next 20:

lastRecordIndex = next(i for (i, d) in enumerate(queryset) if d.id == lastRecord)
queryset = queryset[lastRecordIndex+1: lastRecordIndex+20+1]

So, the problem is that this approach works, but is way too slow. Is there anything better I can do? using raw query or something else in django?

Ashish Ranjan
  • 5,523
  • 2
  • 18
  • 39

1 Answers1

1

You could create your query with conditional expression. Like this:

queryset = MyModel.objects.all()\
        .annotate(date3=Case(
                When(date1__isnull=False, then='date1'),
                When(date2__isnull=False, then='date2'),
                default=Value(datetime.date.max), 
                output_field=DateField()
            )
        )\
        .order_by('date3')

A word of caution, though; this is only going to work if date1 and date2 are set to null. It will not work with blank. And it's also not going to be super fast, because each object in the queryset will be annotated, but at least the code is cleaner :)

Borut
  • 3,304
  • 2
  • 12
  • 18
  • considering the performance, i think it's going be the same as in case of my solution, except the cleaner part. also this doesn't answer the 2nd part of the question. thanks anyways. – Ashish Ranjan Nov 27 '17 at 22:03
  • First hit will probably be same or maybe even slower, but it will most likely be faster with pagination because most databases are caching table data. You can further improve performance with query caching. – Borut Nov 27 '17 at 22:28