django pagination and RawQuerySet

Question

is there a way to paginate a rawqueryset using django's inbuilt pagination? when i cast it to a list , it throws an error in my face ...TypeError: expected string or Unicode object, NoneType found. Is there a way around this?

score 16 · Accepted Answer · edited Apr 19 '11 at 11:43

16

I managed to achieve it using the following:

paginator = Paginator(files, 12)
paginator._count = len(list(files))

The code in django.core.paginator.py:

checks for whether _count is set
if not then tries to run .count() which doesn't exist
if not then tries plain len

len on a raw_queryset doesn't work but converting the actual paginator object to a list works find for me in Django 1.3

edited Apr 19 '11 at 11:43

Andrew Dalke

14,889
4
39
54

answered Mar 28 '11 at 21:57

Chris

2,447
1
21
27

7

Obviously len(list(files)) is very memory inefficient for large raw query sets. Given you know the query that was run you could run another query with COUNT(*) instead and assign that to the paginator._count as long as the number of results isn't going to change between each query. Alternatively different DBMS have ways you could embed the total number of rows in every row of the query if the query results are constantly changing. – Chris Apr 07 '11 at 13:17
Unfortunately, [RawQuerySet.__getitem__()](https://code.djangoproject.com/browser/django/trunk/django/db/models/query.py?rev=17381#L1517) calls list(self) anyway - so it's going to be totally loaded into memory as soon as you call `paginator.get_page()`. To avoid that, I think you'd have to subclass RawQuerySet, and make sure your raw SQL has LIMIT/OFFSET - and even then, you're going to lose the result cache of a normal queryset, so accessing qs[0] twice will hit the DB twice. – AdamKG Jan 20 '12 at 18:59
Yes it will hit twice. I just discovered it for myself too. So its best you write VERY efficient sql for that raw queryset and instead of passing raw queryset to len(list()) and to paginator you first evaluate the queryset by looping over it. Like l = [item for item in queryset] and then pass that l to both paginator and len(l). This gives you only one database call. – Odif Yltsaeb Dec 18 '13 at 13:06

Andrei Kaigorodov · Answer 2 · 2014-02-26T18:56:33.590

5

You can set the attribute count manually for your RawQuerySet object:

items = Item.objects.raw("select * from appitem_item")

def items_count():
    cursor = connection.cursor()
    cursor.execute("select count(*) from appitem_item")
    row = cursor.fetchone()
    return row[0]

items.count = items_count

for @Rockallite

>>> class A():
...    def b(self):
...        print 'from b'
... 
>>> 
>>> (A()).b()
from b
>>> def c():
...    print 'from c'
... 
>>> a = A()
>>> a.b = c
>>> a.b()
from c

edited Feb 26 '14 at 18:56

answered Mar 14 '12 at 06:53

Andrei Kaigorodov

2,145
17
17

django.core.paginator.Paginator looks for `count()` method. So setting the `count` property doesn't work. – Rockallite Dec 03 '13 at 08:51
1

Why, did you test it? Of course, it's a hack but I used it. I mean that in python you can replace one method by another. – Andrei Kaigorodov Feb 26 '14 at 18:55
Rockallite, I added the code in order to illustrate how this hack works – Andrei Kaigorodov Feb 26 '14 at 18:57

score -1 · Answer 3 · answered May 26 '14 at 15:56

-1

qs.filter(**pfilter).distinct().extra(select={'test': 'COALESCE(`psearch_program`.`eu_price`, 999999999)'}).extra(order_by=['test'])

answered May 26 '14 at 15:56

user2732686

464
4
6

-1: explain what is being done, with what is compatibility to, etc. – Jorge Leitao Jul 14 '14 at 12:33

django pagination and RawQuerySet

3 Answers3

Linked