Hi everyone,
Just created my first django new feature ticket and wanted to get some
community opinion on the topic
- https://code.djangoproject.com/ticket/24218#ticket.
For convenience below is the ticket text:
This ticket is to propose a slight change in ORM - use subqueries when
querying a model where.distinct() and .order_by() (or .extra(order_by=()))
leftmost columns do not match. For example:
Model.objects.all().distinct('foo').order_by('bar')
The above generates the following SQL:
SELECT DISTINCT ON ("app_model"."foo") <lots of columns here>FROM
"app_model"ORDER BY "app_model"."bar" ASC;
I am not sure about all backends however the above syntax is not allowed in
PostgreSQL which produces the following error:
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Here are PostgreSQL docs
<http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT>
explaining
why that is not allowed:
DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of
rows where the given expressions evaluate to equal. [...] Note that the
"first row" of each set is unpredictable unless ORDER BY is used to ensure
that the desired row appears first. [...] The DISTINCT ON expression(s)
must match the leftmost ORDER BY expression(s).
This ticket proposes to use subqueries in such situations which would use
SQL:
SELECT *FROM (
SELECT DISTINCT ON ("app_model"."foo") <lots of columns here>
FROM "app_model") resultORDER BY "app_model"."bar" ASC;
The above is perfectly valid SQL and produces expected results (please note
that ORDER_BY is in the outer query to guarantee that distinct results are
correctly sorted).
I created a simple patch
<https://gist.github.com/miki725/ce26d8b6ee2f3075884a> by overwriting few
things in SQLCompiler.as_sql() which seems to work pretty well. The patch
only creates subquery when the above dilema is encountered which should not
have any negative side-effects on existing queries (since such queries were
not allowed by SQL). The patch also works on the .count()queries since
Django then strips any ordering hence the subquery is never created.
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/e47fb7ac-b657-4f48-8fa7-f18dfea5e791%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
