On Nov 21, 2012, at 3:53 AM, ?manu* wrote:
> 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.
Are you sure? This is such a common pattern that I suspect that it’s not slower
than making it into one query. I ran some tests on the slowest query I have,
and the two statements were faster than trying to combine them. 0.2 + 1.5 sec
vs. 1.9 sec.
> 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?
You don't. The problem is qs.query is constructed for your convenience, it’s
not what’s passed to the database. For a query like User.objects.fiter(id=5),
the underling DB call looks like cursor.execute("SELECT * FROM user WHERE id =
%s", (5,)). That is, the SQL has placeholders for the values, and the values
are passed separately. Django doesn't actually escape the values, it just lets
the DB backend do it. It’s much safer and a little faster to do it that way.
Look at qs.query.sql_with_params()
> 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...
sql, params = qs.query.sql_with_params()
s = ValuesQuerySet.objects.raw('SELECT COUNT(*) OVER() , subquery.* FROM
('+sql+') AS subquery', params);
see https://docs.djangoproject.com/en/1.4/topics/db/sql/
Let me know if that works.
Peter of the Norse
rahmc...@radio1190.org
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
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.
