Suppose I have a queryset qs. For paginating purposes I need to do
something like:
count = qs.count()
qs = qs[0:100]
Unfortunately this executes the query twice, which I don't want.
I can use postgresql windowing function like that:
qs = q.extra(select = {'count': 'COUNT(*) OVER()'})
to get the 'count' attribute on every object. So far this is very nice. The
problem is that my queryset qs is actually a ValuesQueryset, i.e. a
queryset build with .values(...).annotate(...). In this case the extra
select field is ignored (as reported in the documentation). This makes
sense because even in postgres it seems not possible to mix window
functions with "group by" clauses. The solution with raw SQL is the use of
a subquery. I can get the correct results with the following query, where I
enclose the sql produced by django in a subquery:
sql = 'SELECT COUNT(*) OVER() , subquery.* FROM (%s) AS subquery' % qs.query
Now the questions:
1. in this snippet qs.query is converted to string. The documentation says
that parameters in the resulting query string are not correctly escaped, so
maybe I should take care of SQL injection? How do I get correct escaping of
parameters?
2. Is it possible to re-attach the sql statement to my ValuesQuerySet? To
keep the rest of the code unchanged, I need to modify qs so that it
executes the new raw query. However I don't see how to construct a
ValuesQueryset with a raw SQL code...
E.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-users/-/sqVpNtxw4-YJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
