As part of some custom cursor-based pagination code in Python Django, I would like to have the below filtering and ordering on a generic Queryset (where I don’t know the table name up front)
WHERE (col_a, col_b) > (%s, %s) ORDER BY col_a, col_b
How can this be expressed in terms of the Django ORM?
Note I would like the SQL to keep the tuple comparison and not have this based on AND clauses. In some previous tests, it seemed more likely that PostgreSQL would be more likely to use multi-column indexes.
Advertisement
Answer
Below is a way that works in Django pre 3.2, using annotate (which also unfortunately needs the slight hack of setting an output_field, even though the value isn’t used).
from django.db.models import F, Func, TextField
col_a_col_b = Func(F('col_a'), F('col_b'), function='ROW', output_type=TextField())
col_a_col_b_from = Func(col_a_value, col_b_value, function='ROW')
filtered_queryset = queryset
.annotate(col_a_col_b=col_a_col_b)
.filter(col_a_col_b__gt=col_a_col_b_from)
.order_by('col_a', 'col_b')
In Django 3.2+ you can apparently use alias instead of annotate
This works since what is referred to as a tuple, is actually shorthand for a Row Constructor, i.e. (col_a, col_b) is the same thing as ROW(col_a, col_b)
The above is based on the information in this ticket