Paging Django Model Instances Queried with Complex Filters Using Paginator
class Content(models.Model):
content_name = models.CharField(...)
group_id = models.PositiveIntegerField(...)
volume_number = models.PositiveIntegerField(...)
...
Assuming we have a typical Django model class, we want to search for its instances using a complex SQL query in a single shot. The results should be displayed on a web page, but since many rows are expected, we want to display a paginator.
For instance, in the above model, we might want to list only the instances with the maximum volume_number within the same group_id.
We will use the general django.core.paginator.Paginator for pagination.
Review of Basic Paginator Usage
contents = Content.objects.filter(...) paginator = Paginator(contents, 100)
By putting a queryset into the first argument, you can paginate it. A list would also work.
Looking at the Paginator code, you can see that it works if slicing and .count() are implemented. .count can also be __len__.
Therefore, you can wrap the list of instances extracted using complex SQL (which cannot be made into a queryset) in a custom class instance that implements slicing and .count(). Slicing has a "step" feature, but it's cumbersome so we won't implement the step. (Step: hoge_sequence[0:100:2] ← the third number in this slice)
Implementation Code
Model Class
class Content(models.Model):
content_name = models.CharField(...)
group_id = models.PositiveIntegerField(...)
volume_number = models.PositiveIntegerField(...)
...
@classmethod
def last_volumes(cls, limit=100, offset=0):
"""
Method for searching with raw SQL if absolutely necessary
Uses objects.raw() to convert to model instances.
The SQL is a bit peculiar, but don't mind it.
"""
sql = """SELECT t1.*
FROM (
SELECT *
FROM content_content
ORDER BY volume_number DESC
) t1
GROUP BY group_id
LIMIT %s OFFSET %s
"""
return cls.objects.raw(sql, params=[limit, offset])
Class for Paginator
class LastContents(object):
"""
Class to be used with the Paginator.
Implements slicing and count()
"""
def __getitem__(self, key):
if isinstance(key, slice):
# In case of slicing (e.g., instance[0:100])
# Ignore the step!
limit = key.stop - key.start
return Content.last_volumes(
limit=limit, offset=key.start)
# In case of getting a single element (e.g., instance[50])
# Not used this time, but
return Content.last_volumes(limit=1, offset=key)[0]
def count(self):
# Implement a method to get the total count
return Content.objects.filter(volume_number=1).count()
View Function
# Put the instance of LastContents we just created! paginator = Paginator(LastContents(), 100)You can paginate.
page = paginator.page(page_number)
page.object_list ...
We look forward to discussing your development needs.