Django: Ticket Queryhttps://code.djangoproject.com/query?status=!closed&keywords=~queryset&desc=1&order=id
The Web framework for perfectionists with deadlines.en-USDjangohttps://www.djangoproject.com/s/img/site/hdr_logo.gifhttps://code.djangoproject.com/query?status=!closed&keywords=~queryset&desc=1&order=id
Trac 1.2.2https://code.djangoproject.com/ticket/28919
https://code.djangoproject.com/ticket/28919#28919: Add support for Common Table Expression (CTE) queriesTue, 12 Dec 2017 21:18:25 GMTDaniel Miller<p>
<a class="ext-link" href="http://django-cte-trees.readthedocs.io/en/latest/"><span class="icon">​</span>django-cte-trees</a> (also <a class="ext-link" href="https://github.com/matthiask/django-cte-forest"><span class="icon">​</span>django-cte-forest</a>) provides specialized uses of CTE queries for managing hierarchical data with recursive queries. To accomplish this, it uses the <code>QuerySet.extra()</code> API. This is my specific use case for CTE queries at the moment, however it leverages only one small part of what is possible with CTE queries: <a class="ext-link" href="https://www.postgresql.org/docs/9.6/static/queries-with.html"><span class="icon">​</span>PostgreSQL</a>, <a class="ext-link" href="https://dev.mysql.com/doc/refman/8.0/en/with.html"><span class="icon">​</span>MySQL</a>, <a class="ext-link" href="https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql"><span class="icon">​</span>SQL Server</a>.
</p>
<p>
Another implementation supporting more general uses of CTE queries was <a class="ext-link" href="https://groups.google.com/forum/#!topic/django-developers/b370mxfKCHg"><span class="icon">​</span>presented on the developers mailing list</a>, although I'm not sure it has ever made it any further than that. The code can be found <a class="ext-link" href="https://github.com/django/django/compare/master...ashleywaite:cte-dev"><span class="icon">​</span>on github</a>. It appears to do its magic by mutating <code>base_query.extra_tables</code>, which seems to be a private/internal part of the ORM.
</p>
Resultshttps://code.djangoproject.com/ticket/28919#changeloghttps://code.djangoproject.com/ticket/28621
https://code.djangoproject.com/ticket/28621#28621: Crash in QuerySet.annotate() with OuterRefWed, 20 Sep 2017 15:58:36 GMTДилян Палаузов<pre class="wiki">class A(models.Model):
tag = models.CharField()
class B(models.Model):
pattern = models.CharField()
</pre><p>
A single query that retrieves all A.tag, not matching B.pattern:
</p>
<pre class="wiki">SELECT a.tag FROM app_a as a WHERE NOT EXISTS (SELECT 1 FROM app_b as b WHERE a.tag ~ b.pattern);
</pre><p>
Is it possibe to write a queryset for this, without .extra and .raw?
</p>
Resultshttps://code.djangoproject.com/ticket/28621#changeloghttps://code.djangoproject.com/ticket/28107
https://code.djangoproject.com/ticket/28107#28107: Can't perform annotation on related table when un-managed model is backed by a DB viewFri, 21 Apr 2017 01:20:51 GMTpowderflask<p>
I'm working with a legacy DB (ArcSDE database -- really ugly) -- have been able to accommodate its oddities without resorting much to raw SQL, but django upgrade (1.8 --&gt; 1.11) caused a previously working annotation to fail:
<code> psycopg2.ProgrammingError: :column ... must appear in the GROUP BY clause or be used in an aggregate function </code>
</p>
<p>
<strong>DB: PostgreSQL 9.3.16 </strong> (i.e., this is not same issue as <a class="closed ticket" href="https://code.djangoproject.com/ticket/26758" title="#26758: Uncategorized: Annotate appears broken with Postgres in recent releases (closed: needsinfo)">#26758</a> ) Python3.6, Django1.11
</p>
<p>
The annotation simply counts the number of related records from a related 'attachments' table:
<code> qs.annotate(num_attachments=Count('attachments')) </code>
</p>
<p>
The root cause appears to be that the relation between an unmanaged model (backed by a DB View) and attachments tables uses a unique field <em>other than the main model's primary key</em> (I know -- told you it was ugly - ArcSDE does not really support relations, except they implement attachments with this odd ball <strong> sigh </strong>).
The change in behaviour seems to manifest from <a class="closed ticket" href="https://code.djangoproject.com/ticket/19259" title="#19259: Cleanup/optimization: Annotations generating inefficient SQL on PostgreSQL (closed: fixed)">#19259</a> (I believe django1.8 added all fields to the groupby clause).
Since django now includes only the primary key in the groupby clause, postgresql won't do this aggregation across a relation that uses a non-pk field.
</p>
<p>
I suspect there is a general issue here that aggregations on a related-table won't work in postgresql unless the relation is on the primary key field (so, yeah, basically this issue applies to almost no one, right...).
UPDATE: The root cause is actually that Postgresql treats Views differently than Tables w.r.t. what is required in the group by clause.
</p>
<p>
Seems likely there is a better solution to this, but after a half-day of search / effort, I resorted to the following:
</p>
<pre class="wiki">qs.extra(select={'num_attachments':
'SELECT COUNT("attachmentid") FROM {attach_table} WHERE {attach_table}.rel_globalid = {model_table}.globalid'.format(
model_table = qs.model._meta.db_table,
attach_table = qs.model.AttachmentModel._meta.db_table,
)},)
</pre><p>
This works and achieves my goal -- to annotate model with the number of related attachments.
Since the <code> model.attachments.count() </code> query works just fine, I'm considering eliminating this annotation and replacing with a property on the model class, what's one more query... I'm sure there must be an smoother way, but it eludes me...
</p>
<p>
Since the docs suggested to open a ticket for queries that could not be resolved without resorting to extra(), here it is, for whatever its worth. Hope this hasn't been a complete waste of time for you.
</p>
Resultshttps://code.djangoproject.com/ticket/28107#changeloghttps://code.djangoproject.com/ticket/26602
https://code.djangoproject.com/ticket/26602#26602: Provide a way to manage grouping with RawSQLTue, 10 May 2016 11:17:35 GMTJamie Cockburn<p>
I wanted to annotate my objects with a running total:
</p>
<div class="wiki-code"><div class="code"><pre><span class="k">class</span> <span class="nc">A</span><span class="p">(</span>models<span class="o">.</span>Model<span class="p">):</span>
amount <span class="o">=</span> models<span class="o">.</span>IntegerField<span class="p">()</span>
created <span class="o">=</span> models<span class="o">.</span>DateTimeField<span class="p">(</span>auto_now_add<span class="o">=</span><span class="bp">True</span><span class="p">)</span>
qs <span class="o">=</span> A<span class="o">.</span>objects<span class="o">.</span>annotate<span class="p">(</span>total<span class="o">=</span>RawSQL<span class="p">(</span><span class="s2">"SUM(amount) OVER (ORDER BY created)"</span><span class="p">,</span> <span class="p">[]))</span>
</pre></div></div><p>
That works fine, and I get a running total for each object, but I cannot call <code>count()</code> on that queryset:
</p>
<pre class="wiki">&gt;&gt;&gt; qs.count()
Traceback...
ProgrammingError: window functions not allowed in GROUP BY clause
</pre><p>
Using <code>extra()</code>, I can get the same annotation behaviour as well being able to call <code>count()</code>:
</p>
<pre class="wiki">&gt;&gt;&gt; qs = A.objects.extra(select={'total': 'SUM(amount) OVER (ORDER BY created)'})
&gt;&gt;&gt; qs.count()
8
</pre>Resultshttps://code.djangoproject.com/ticket/26602#changeloghttps://code.djangoproject.com/ticket/26434
https://code.djangoproject.com/ticket/26434#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend prior and post the QuerySet evaluationThu, 31 Mar 2016 13:57:29 GMTkamandol<p>
Using a PostgreSQL backend database, in some cases with QuerySets involving <strong>annotations</strong>, <strong>values</strong> or <strong>values_list</strong> and ordering(<strong>order_by</strong>) clauses the resulting QuerySet <strong>count()</strong> method fails to predict the real row result unless that QuerySet is evaluated.
</p>
<p>
For instance:
</p>
<div class="wiki-code"><div class="code"><pre>q <span class="o">=</span> BundleVersion<span class="o">.</span>objects<span class="o">.</span>order_by<span class="p">(</span><span class="s1">'-id'</span><span class="p">)</span><span class="o">.</span>values_list<span class="p">(</span><span class="s2">"port__id"</span><span class="p">,</span> <span class="s2">"asset_bundle__id"</span><span class="p">)</span><span class="o">.</span>annotate<span class="p">(</span><span class="nb">max</span><span class="o">=</span>models<span class="o">.</span>Max<span class="p">(</span><span class="s2">"id"</span><span class="p">))</span>
</pre></div></div><p>
This QuerySet using PostgreSQL would, in fact, group the results by <strong>BundleVersion.id</strong> instead of the tuple ("port__id", "asset_bundle__id") due to the <strong>order_by</strong> clause using a column not in the <strong>values_list</strong>. This is a documented behavior.
</p>
<p>
The problem is that if <strong>q</strong> is not evaluated yet, calling <strong>q.count()</strong> will return an amount of rows as if the grouping was done on the tuple ("port__id", "asset_bundle__id"). <strong>After</strong> evaluating <strong>q</strong>, or calling <strong>len(q)</strong>, the result of <strong>q.count()</strong> will be the expected, as if the grouping was done using <strong>'BundleVersion.id</strong>'.
</p>
Resultshttps://code.djangoproject.com/ticket/26434#changeloghttps://code.djangoproject.com/ticket/26430
https://code.djangoproject.com/ticket/26430#26430: Coalesce in Aggregations ignored when EmptyResultSet returnedWed, 30 Mar 2016 22:25:12 GMTRyan Prater<p>
Using an empty list when using the <code>__in=</code> returns an EmptyResultSet and prevents an Aggregate Coalesce from working properly. See below:
</p>
<p>
# Test with matched Queryset. Sum will return 50
<code>OrderItem.objects.filter(pk__in=[1]).aggregate(test=Coalesce(Sum('quantity'), Value(0)))</code>
<code>&gt;&gt;&gt;{'test': 50</code>}
# Test with unmatched Queryset. Sum will return 0
<code>OrderItem.objects.filter(pk__in=[-1]).aggregate(test=Coalesce(Sum('quantity'), Value(0)))</code>
<code>&gt;&gt;&gt; {'test':0</code>}
# Test with unmatched Queryset (using empty list). EmptyResultSet returned because of empty list. Sum will return NONE
<code>OrderItem.objects.filter(pk__in=[]).aggregate(test=Coalesce(Sum('quantity'), Value(0)))</code>
<code>&gt;&gt;&gt; {'test': None</code>}
</p>
<p>
Simon Charette on django-users suggested the following:
</p>
<p>
<em>From what I understand the ORM simply doesn't perform any query in this case
as the <code>pk__in</code> lookup cannot match any <code>OrderItem</code> and result in an
<code>EmptyResultSet</code> exception<a class="changeset" href="https://code.djangoproject.com/changeset/1/">[1]</a>.</em>
<em>This exception is caught in the <code>Query.get_aggregation()</code> method where all
aggregates are converted to <code>None</code><a class="changeset" href="https://code.djangoproject.com/changeset/2/">[2]</a>.</em>
<em>I suppose we should alter the <code>except EmptyResultSet</code> clause to account for
<code>outer_query.annotation_select</code> items that are <code>Coalesce()</code> instances used with
<code>Value()</code> but I'm unsure about how it should be done.</em>
</p>
<p>
<a class="changeset" href="https://code.djangoproject.com/changeset/1/">[1]</a> <a class="ext-link" href="https://github.com/django/django/blob/2e0cd26ffb29189add1e0435913fd1490f52b20d/django/db/models/lookups.py#L221-L223"><span class="icon">​</span>https://github.com/django/django/blob/2e0cd26ffb29189add1e0435913fd1490f52b20d/django/db/models/lookups.py#L221-L223</a>
<a class="changeset" href="https://code.djangoproject.com/changeset/2/">[2]</a> <a class="ext-link" href="https://github.com/django/django/blob/2e0cd26ffb29189add1e0435913fd1490f52b20d/django/db/models/sql/query.py#L439-L445"><span class="icon">​</span>https://github.com/django/django/blob/2e0cd26ffb29189add1e0435913fd1490f52b20d/django/db/models/sql/query.py#L439-L445</a>
</p>
<p>
See full discussion here:
<a class="ext-link" href="https://groups.google.com/forum/#!topic/django-users/HGD3Vv3IerA"><span class="icon">​</span>https://groups.google.com/forum/#!topic/django-users/HGD3Vv3IerA</a>
</p>
Resultshttps://code.djangoproject.com/ticket/26430#changeloghttps://code.djangoproject.com/ticket/26426
https://code.djangoproject.com/ticket/26426#26426: Add a way to customize a QuerySet's joinsWed, 30 Mar 2016 15:59:02 GMTCharlie DeTar<p>
This ticket is just to document a use case for QuerySet.extra as requested by the docs: <a class="ext-link" href="https://docs.djangoproject.com/en/1.9/ref/models/querysets/#extra"><span class="icon">​</span>https://docs.djangoproject.com/en/1.9/ref/models/querysets/#extra</a>
</p>
<p>
I have a Category model like this:
</p>
<pre class="wiki">class Category(models.Model):
name = models.CharField(max_length=200)
followers = models.ManyToManyField(User)
</pre><p>
I want to get a list of all categories, but to annotate each category with whether the currently logged in user is a "follower" of the category. Neither <code>prefetch_related</code> nor <code>annotate</code> work here, because I don't want to fetch nor aggregate over <em>all</em> "followers" (potentially many), I just want the presence of the current user. The extra query looks like this:
</p>
<pre class="wiki">Category.objects.filter(...).extra(
select={'is_following': '''EXISTS(
SELECT "id" FROM "projects_category_followers" WHERE
"projects_category_followers"."category_id"="projects_category"."id" AND
"projects_category_followers"."user_id"=%s
)'''},
select_params=(request.user.id,)
)
</pre>Resultshttps://code.djangoproject.com/ticket/26426#changeloghttps://code.djangoproject.com/ticket/26318
https://code.djangoproject.com/ticket/26318#26318: Unexpected / duplicated queries on nested Prefetch queryset with repeated modelThu, 03 Mar 2016 22:06:48 GMTAnthony Leontiev<p>
We discovered an issue in <code>django.db.models.query.Prefetch</code> logic that results in duplicate queries made when the leaves of certain prefetch trees are accessed.
</p>
<p>
With these models:
</p>
<pre class="wiki">from django.db import models
class Publisher(models.Model):
name = models.CharField(max_length=128)
class Author(models.Model):
name = models.CharField(max_length=128)
publishers = models.ManyToManyField('Publisher', related_name='authors')
class Book(models.Model):
name = models.CharField(max_length=128)
publisher = models.ForeignKey('Publisher', related_name='books')
</pre><p>
The following test fails:
</p>
<pre class="wiki">from django.db.models.query import Prefetch
from django.test import TestCase
from .models import Author, Book, Publisher
def flatten(ls):
return list([i for s in ls for i in s])
class PrefetchTestCase(TestCase):
def setUp(self):
publisher = Publisher.objects.create(name='Publisher0')
Book.objects.create(name='Book0', publisher=publisher)
author = Author.objects.create(name='Author0')
author.publishers.add(publisher)
def test_prefetch_nested(self):
publishers = Publisher.objects.prefetch_related(
Prefetch(
'books',
Book.objects.all().prefetch_related(
Prefetch(
'publisher',
Publisher.objects.all().prefetch_related('authors')
)
)
)
)
with self.assertNumQueries(4):
publishers = list(publishers)
with self.assertNumQueries(0):
books = flatten([p.books.all() for p in publishers])
with self.assertNumQueries(0):
publishers = [b.publisher for b in books]
with self.assertNumQueries(0):
authors = flatten([p.authors.all() for p in publishers])
</pre><p>
For more details (comments, queries executed) and an analogous green test-case that uses the flat prefetch form to prefetch the same tree, see the attached test package.
</p>
<p>
To run the tests:
</p>
<pre class="wiki"> tar -zxvf prefetch-bug-test.tar.gz
cd prefetch-bug-test
make test
</pre><p>
This issue seemed very similar to <a href="https://code.djangoproject.com/ticket/25546">https://code.djangoproject.com/ticket/25546</a>, but unfortunately the patch for that ticket (<a href="https://code.djangoproject.com/changeset/bdbe50a491ca41e7d4ebace47bfe8abe50a58211">https://code.djangoproject.com/changeset/bdbe50a491ca41e7d4ebace47bfe8abe50a58211</a>) did not fix this problem.
</p>
<p>
Tested in Django 1.8, 1.9, and master.
</p>
Resultshttps://code.djangoproject.com/ticket/26318#changeloghttps://code.djangoproject.com/ticket/25789
https://code.djangoproject.com/ticket/25789#25789: Inefficient Queries Generated due to not using WHERE EXISTSFri, 20 Nov 2015 21:52:43 GMTAlex Rothberg<p>
<a class="ext-link" href="http://stackoverflow.com/questions/33764737/django-equivalent-of-sqlalchemy-any-to-filter-where-exists/33765323"><span class="icon">​</span>Reposting question from SO</a> with some more details.
</p>
<p>
I believe that the Django ORM is generating seriously inefficient SQL due to it not using <code>WHERE EXISTS</code> but instead using a <code>DISTINCT</code> with a <code>LEFT JOIN</code>. by comparison, SQLAlchemy will use <code>WHERE EXISTS</code>.
</p>
<p>
I have two models, <code>Exam</code> and <code>Series</code>. <code>Series</code> objects have a foreign key to an <code>Exam</code> object. Both of the models contain a field <code>description_user</code>. I am trying to search for all <code>Exam</code>s that have a search term in <code>description_user</code> or have a child <code>Series</code> with that term in its <code>description_user</code>. I want to do this for a number of search terms (requiring all of them). I also want to de-duplicate the results (ie not get the same Exam multiple times).
</p>
<p>
This is roughly what the filter looks like:
</p>
<pre class="wiki">a = (Q(**{'series__description_user__icontains': 'bar'}) | Q(**{'description_user__icontains': 'bar'}))
b = (Q(**{'series__description_user__icontains': 'foo'}) | Q(**{'description_user__icontains': 'foo'}))
c = (Q(**{'series__description_user__icontains': 'baz'}) | Q(**{'description_user__icontains': 'baz'}))
Exam.objects.filter(a &amp; b &amp; c).distinct()
</pre><p>
with corresponding SQL:
</p>
<pre class="wiki">SELECT DISTINCT
"exam_storage_exam"."id",
"exam_storage_exam"."description_user"
FROM "exam_storage_exam"
LEFT OUTER JOIN "exam_storage_series"
ON (
"exam_storage_exam"."id" = "exam_storage_series"."exam_id"
AND (
"exam_storage_series"."removed" IS NULL) )
WHERE (
"exam_storage_exam"."removed" IS NULL
AND (
"exam_storage_series"."description_user" LIKE %s ESCAPE \'\\\'
OR "exam_storage_exam"."description_user" LIKE %s ESCAPE \'\\\')
AND (
"exam_storage_series"."description_user" LIKE %s ESCAPE \'\\\'
OR "exam_storage_exam"."description_user" LIKE %s ESCAPE \'\\\')
AND (
"exam_storage_series"."description_user" LIKE %s ESCAPE \'\\\'
OR "exam_storage_exam"."description_user" LIKE %s ESCAPE \'\\\'))
</pre><p>
The issue is that as the number of search terms grows, the size of the intermediate data set before the DISTINCT operation grows as well.
</p>
<p>
Ideally the SQL would look like:
</p>
<pre class="wiki">SELECT *
FROM exam
WHERE (EXISTS (SELECT 1
FROM exam_storage_series
WHERE exam.id = series.exam_id AND (
series.description_user LIKE '%foo%'
)) or exam.description_user LIKE '%foo%') AND
(EXISTS (SELECT 1
FROM exam_storage_series
WHERE exam.id = series.exam_id AND (
series.description_user LIKE '%bar%'
)) or exam.description_user LIKE '%bar%') AND
(EXISTS (SELECT 1
FROM exam_storage_series
WHERE exam.id = series.exam_id AND (
series.description_user LIKE '%baz%'
)) or exam.description_user LIKE '%baz%')
</pre><p>
Currently the performance of Django query is terrible. This style searching comes up for example in how <a class="ext-link" href="https://github.com/tomchristie/django-rest-framework/blob/43c45cc9391ec2bed9481a8b309990dec35b6ac8/rest_framework/filters.py#L132-L180"><span class="icon">​</span>DRF generates search queries</a>.
</p>
Resultshttps://code.djangoproject.com/ticket/25789#changeloghttps://code.djangoproject.com/ticket/24638
https://code.djangoproject.com/ticket/24638#24638: New feature: support adding an SQL comment in queriesMon, 13 Apr 2015 21:11:06 GMTAdam (Chainz) Johnson<p>
This feature gives QuerySet a <code>.comment(text)</code> method that inserts text into a comment in the output SQL query, right after the <code>SELECT [DISTINCT]</code> part. This has two uses:
</p>
<ol><li>For all database backends, this can be used for feature-to-query tracking. For example, you might override <code>def queryset</code> on your admin classes to use <code>comment()</code> to insert the name of the admin class generating the query, making query analysis (processlist, slow query logs, etc.) easier to track back to the point in the code generating the queries. This is not trivial.
</li></ol><p>
I've seen Facebook have nice tools that automatically insert file/line number summaries into queries as comments to make analysis easy. Adding <code>comment()</code> would making this easy for custom QuerySet subclasses to do this however they want, with e.g. caller inspection at <code>__init__</code> time.
</p>
<ol start="2"><li>For the MySQL and Oracle backends, flags and optimizer hints can be added. Both have a number of options for queries that are otherwise unsupported on django, and they can be inserted with special comments.
</li></ol><p>
This is my current motivation - I had a pretty complex 3-join/5-subquery MySQL query today that could only be made good on the ORM by adding MySQL's "STRAIGHT_JOIN" hint, and the only way I found to do this in Django at current was to monkey-patch the MySQL backend CursorWrapper's execute() to rewrite the generated query with regexes... :( And it couldn't use raw() since it was being passed to the admin with the ability to filter it.
</p>
<p>
I couldn't find any historical tickets on optimizer hints.
</p>
Resultshttps://code.djangoproject.com/ticket/24638#changeloghttps://code.djangoproject.com/ticket/24048
https://code.djangoproject.com/ticket/24048#24048: only() doesn't override fields included in defer() as documentedWed, 24 Dec 2014 09:27:24 GMTWill Earp<p>
The docs give the following example for the <code>QuerySet</code> API's <code>only</code>:
</p>
<pre class="wiki"># Final result loads headline and body immediately (only() replaces any
# existing set of fields).
Entry.objects.defer("body").only("headline", "body")
</pre><p>
<a class="ext-link" href="https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.only"><span class="icon">​</span>https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.only</a>
</p>
<p>
I understand this to mean that "body" will be loaded i.e. not a deferred field. When testing this, however, "body" is still considered a <code>DeferredAttribute</code>. Is this a possible bug or does the documentation need changing?
</p>
Resultshttps://code.djangoproject.com/ticket/24048#changeloghttps://code.djangoproject.com/ticket/23870
https://code.djangoproject.com/ticket/23870#23870: Sliced QuerySets in ModelChoiceFieldWed, 19 Nov 2014 15:57:23 GMTKamil Śliwak<h2 id="Theissue">The issue</h2>
<p>
<code>ModelChoiceField</code> raises an exception if its <code>queryset</code> is a query that has been sliced (i.e. resolves to a SQL <code>SELECT</code> with <code>LIMIT</code>). The failure occurs only after form submission - during validation - so it's not obvious to the user if it's unsupported or if he's just using it incorrectly.
</p>
<p>
This should either be fixed or documented as an unsupported use case. In the latter case the error should appear earlier (in field constructor?) and the message should tell the user explicitly that it's not supported.
</p>
<h2 id="Example">Example</h2>
<p>
Let's say you have a model called Book:
</p>
<div class="wiki-code"><div class="code"><pre><span class="k">class</span> <span class="nc">Book</span><span class="p">(</span>models<span class="o">.</span>Model<span class="p">):</span>
rating <span class="o">=</span> models<span class="o">.</span>IntegerField<span class="p">()</span>
</pre></div></div><p>
You want to create a form that lets user select one of the top rated books. So you try:
</p>
<div class="wiki-code"><div class="code"><pre><span class="k">class</span> <span class="nc">BookForm</span><span class="p">(</span>forms<span class="o">.</span>Form<span class="p">):</span>
book <span class="o">=</span> forms<span class="o">.</span>ModelChoiceField<span class="p">(</span>queryset <span class="o">=</span> Book<span class="o">.</span>objects<span class="o">.</span>order_by<span class="p">(</span><span class="s1">'-rating'</span><span class="p">)[:</span><span class="mi">100</span><span class="p">])</span>
</pre></div></div><p>
It appears to work - the form can be rendered and you can choose one of a hundered top-rated books. But when you submit, you get the following error:
</p>
<pre class="wiki">AssertionError: Cannot filter a query once a slice has been taken.
</pre><p>
The error is caused by <a class="ext-link" href="https://github.com/django/django/blob/bfb11b95626f39e2f5e18d97d7761c6f93dcc1a9/django/forms/models.py#L1195-L1203"><span class="icon">​</span>`ModelChoiceField.to_python()`</a> validating the existence of the selected item by calling <code>get()</code> on the queryset:
</p>
<div class="wiki-code"><div class="code"><pre>value <span class="o">=</span> <span class="bp">self</span><span class="o">.</span>queryset<span class="o">.</span>get<span class="p">(</span><span class="o">**</span><span class="p">{</span>key<span class="p">:</span> value<span class="p">})</span>
</pre></div></div><p>
And this is not supported for sliced querysets as the error above states.
</p>
<h2 id="Workarounds">Workarounds</h2>
<p>
To work around the problem one can make the sliced query a subquery:
</p>
<div class="wiki-code"><div class="code"><pre><span class="k">class</span> <span class="nc">BookForm</span><span class="p">(</span>forms<span class="o">.</span>Form<span class="p">):</span>
book <span class="o">=</span> forms<span class="o">.</span>ModelChoiceField<span class="p">(</span>queryset <span class="o">=</span> Book<span class="o">.</span>objects<span class="o">.</span>filter<span class="p">(</span>pk__in <span class="o">=</span> Book<span class="o">.</span>objects<span class="o">.</span>order_by<span class="p">(</span><span class="s1">'-rating'</span><span class="p">)[:</span><span class="mi">100</span><span class="p">]</span><span class="o">.</span>values_list<span class="p">(</span><span class="s1">'pk'</span><span class="p">)))</span>
</pre></div></div><p>
On my machine this is about 4 times slower than a simple query with <code>LIMIT</code> (see the discussion thread linked below) but seems to work without any adverse effects.
</p>
<p>
One nice feature of this workaround is that the outer query can have different ordering than the one used for slicing which might be useful in some cases. E.g. select top rated books but sort them by title.
</p>
<p>
I think that it would be a good idea to mention this workaround in <a class="ext-link" href="https://docs.djangoproject.com/en/dev/ref/forms/fields/#django.forms.ModelChoiceField.queryset"><span class="icon">​</span>the docs</a>.
</p>
<h2 id="Discussion">Discussion</h2>
<p>
Here's the discussion thread regarding the issue on django-developers mailing list: <a class="ext-link" href="https://groups.google.com/forum/#!topic/django-developers/ELqU2xt_Qo0"><span class="icon">​</span>Why doesn't ModelChoiceField.queryset support slicing?</a>
</p>
Resultshttps://code.djangoproject.com/ticket/23870#changeloghttps://code.djangoproject.com/ticket/22503
https://code.djangoproject.com/ticket/22503#22503: Inconsistent behavior when a QuerySet is slicedThu, 24 Apr 2014 12:49:46 GMTLuis A. Arce<p>
I think that the current behavior of the QuerySet.slice method is not consistent. Just an example:
</p>
<pre class="wiki">
def limited_filtering(max_results=10, *args, **kwargs){
"""
Returns a Queryset
"""
returns SomeModel.filter(*args, **kwargs)[:max_results]
}
# ...
def other_method_somewhere(queryset){
"""
Expects a Queryset of SomeModel and returns the same Queryset filtered again
"""
return queryset.filter(some_other_criteria)
}
other_method_somewhere(limited_filtering(*args, **kwargs)); # raises "AssertionError: Cannot filter a query once a slice has been taken."
</pre><p>
Given the previous code, it is legit and fair that <code>other_method_somewhere</code> expects, as in most situations, a filtrable <code>QuerySet</code>. Not allowing a sliced <code>QuerySet</code> to be filtered after being sliced adds an edge case difficult to handle when methods using a <code>QuerySet</code> type are somehow semantically far away from each other (and thus, they don't know if the other is returning an sliced QuerySet).
</p>
<p>
Without knowing the exact reasons why this was implemented this way, the possible solutions I can come with are:
</p>
<ul><li>The most natural one, to make the sliced <code>QuerySet</code>s filtrable.
</li><li>To make all the returned query sets in which <code>can_filter == False</code> to return a new type of <code>QuerySet</code>, something like <code>InmutableQuerySet</code> in order to take advantage of other methods related to them (<code>values_list</code>, etc.).
</li><li>To make <code>QuerySet</code>'s slices return list or other type of collection (I guess this is impossible without breaking backwards compatibility).
</li></ul><p>
Actually, the last case is supposedly the way one would expect it to work according to the documentation:
</p>
<p>
<a class="ext-link" href="https://docs.djangoproject.com/en/dev/ref/models/querysets/#when-querysets-are-evaluated"><span class="icon">​</span>https://docs.djangoproject.com/en/dev/ref/models/querysets/#when-querysets-are-evaluated</a>
</p>
<blockquote class="citation">
<p>
<strong>Slicing</strong>. As explained in Limiting QuerySets, a QuerySet can be sliced, using Python’s array-slicing syntax. Slicing an unevaluated QuerySet usually returns another unevaluated QuerySet, but Django will execute the database query if you use the “step” parameter of slice syntax, and will return a list. Slicing a QuerySet that has been evaluated (partially or fully) also returns a list.
</p>
</blockquote>
<p>
By the way, there is also no reference in the documentation(or at least I could not find it) to the public method <code>QuerySet.can_filter</code>.
</p>
Resultshttps://code.djangoproject.com/ticket/22503#changeloghttps://code.djangoproject.com/ticket/22492
https://code.djangoproject.com/ticket/22492#22492: provide a way to prevent database queries on model objectsTue, 22 Apr 2014 23:48:47 GMTChris Jerdonek<p>
This is a feature request to provide a way to prevent database queries from happening in a block of code, especially on model objects (e.g. using a context manager, or an internal flag on model objects).
</p>
<p>
The motivation is for use with the QuerySet method <a class="ext-link" href="https://docs.djangoproject.com/en/dev/ref/models/querysets/#only"><span class="icon">​</span>`only()`</a>, for example. (The <code>only()</code> method is used to prevent unnecessary fields from being loaded from a database when querying.) Consider the case of using <code>only()</code> to retrieve a list of many model objects, and then subsequently displaying them. If one executes some Django code after obtaining this list (e.g. by looping through the list of objects), it would be bad if this later code accidentally accessed some other field on each object. This could trigger the unintentional execution of many individual database queries (e.g. on a production database), with potentially bad consequences. I don't currently know an easy way to prevent this.
</p>
<p>
It would be good to have such a way. For example, Django could provide some sort of <code>noQuery()</code> context manager which would raise an exception if the database were queried inside it. Code after the <code>only()</code> line could be included in such a context manager. This could prevent accidentally hammering a database.
</p>
<p>
Alternatively, the QuerySet API could expose a way to return objects with some sort of <code>no_query</code> flag set. If attribute access on such a model object required a database query, objects with such a flag set could instead raise an exception. This would also suffice to prevent accidental queries.
</p>
Resultshttps://code.djangoproject.com/ticket/22492#changeloghttps://code.djangoproject.com/ticket/22115
https://code.djangoproject.com/ticket/22115#22115: Related Querysets from Inlines not getting cachedFri, 21 Feb 2014 21:58:24 GMTjohn.parton@…<p>
Here's an overview of my code:
</p>
<pre class="wiki"> # models.py
from django.db import models
class ExampleParent(models.Model):
def __unicode__(self):
return u'Example Parent: %s' % self.id
class ExampleInline(models.Model):
parent = models.ForeignKey('ExampleParent')
child = models.ForeignKey('ExampleChild')
def __unicode__(self):
return u'Example Inline: %s' % self.id
class ExampleChild(models.Model):
def __unicode__(self):
return u'Example Child: %s' % self.id
# admin.py
from django.contrib import admin
from admin_issue.example_problem.models import (ExampleParent, ExampleInline)
class ExampleInlineInline(admin.TabularInline):
model = ExampleInline
class ExampleParentAdmin(admin.ModelAdmin):
inlines = [
ExampleInlineInline
]
admin.site.register(ExampleParent, ExampleParentAdmin)
</pre><p>
If you go to the admin details for an ExampleParent instance, a query will be executed to fetch all of ExampleChild for as many ExampleInlines are associated with that ExampleParent. These are exactly identical queries. I would expect each queryset to be cached and for the ORM.
</p>
<p>
I have a few models in production that produces hundreds (sometimes over a thousand) queries. Most of them are exactly identical queries that could be cached.
</p>
<p>
I could probably patch my code to work around this, but it seems like the expected default behavior would be to cache these.
</p>
<p>
I can generate a small project with a sqlite db if necessary.
</p>
<p>
John P.
</p>
Resultshttps://code.djangoproject.com/ticket/22115#changeloghttps://code.djangoproject.com/ticket/19527
https://code.djangoproject.com/ticket/19527#19527: Allow QuerySet.bulk_create() to set the primary key of its objectsThu, 27 Dec 2012 12:42:00 GMTVlada Macek<p>
(Sorry if I missed a pre-existing ticket for this.)
</p>
<p>
There is an IMO big caveat of bulk_create(): It does not set primary key of objects it creates.
</p>
<p>
In my current project, using bulk_create would improve performance, but as I often need an id of the newly created objects, I have to save them one by one.
</p>
<p>
I think it would be perfect if bulk_create starts to set the primary keys of the objects in the given sequence. At least postgresql supports it ("RETURNING id" clause gives us a set of id's).
</p>
Resultshttps://code.djangoproject.com/ticket/19527#changeloghttps://code.djangoproject.com/ticket/17930
https://code.djangoproject.com/ticket/17930#17930: Error in Queryset with operator | (union queryset) + sliceSun, 18 Mar 2012 15:31:04 GMTvini.gracindo@…<pre class="wiki">class Example:
name = models.CharField(max_length = 30)
public = models.BooleanField()
</pre><pre class="wiki">&gt;&gt;&gt; Example.objects.create('example 1', False)
&lt;Example: Example object&gt;
&gt;&gt;&gt; Example.objects.create('example 2', False)
&lt;Example: Example object&gt;
&gt;&gt;&gt; Example.objects.create('example 3', True)
&lt;Example: Example object&gt;
&gt;&gt;&gt; Example.objects.create('example 4', True)
&lt;Example: Example object&gt;
&gt;&gt;&gt; Example.objects.create('example 5', False)
&lt;Example: Example object&gt;
&gt;&gt;&gt; query = Example.objects.filter(public = True)
&gt;&gt;&gt; if(query.count() &lt; 3):
... query = query | Example.objects.filter(public = False).order_by('?')[:1]
...
&gt;&gt;&gt; query.count()
5
&gt;&gt;&gt;
</pre><p>
When using the union of querysets to slice it "ignores" the slice and takes all objects where <code>public = false</code>.
</p>
Resultshttps://code.djangoproject.com/ticket/17930#changeloghttps://code.djangoproject.com/ticket/17664
https://code.djangoproject.com/ticket/17664#17664: {% if %} template tag silences exceptions inconsistentlyThu, 09 Feb 2012 08:48:31 GMTTai Lee<p>
This buggy behaviour took me a while to track down. I hope I can explain it clearly.
</p>
<p>
Given a <code>QuerySet</code> with invalid ordering (and possibly other conditions) that should raise an exception when evaluated, <code>{% if qs %}</code> will raise the exception while <code>{% if not qs %}</code> will silence it and leave <code>qs</code> as if it were simply empty on subsequent access in the template.
</p>
<p>
First, the exception is silenced and the queryset becomes empty:
</p>
<pre class="wiki">&gt;&gt;&gt; from django.contrib.auth.models import User
&gt;&gt;&gt; from django.template import Template, Context
&gt;&gt;&gt; Template('count: {{ qs.count }}, empty: {% if not qs %}yes{% else %}no{% endif %}, qs: {{ qs }}, count: {{ qs.count }}').render(Context({'qs': User.objects.order_by('invalid_field')}))
u'count: 98, empty: no, qs: [], count: 0'
</pre><p>
And now if we swap the <code>{% if %}</code> around a bit, we get an exception:
</p>
<pre class="wiki">&gt;&gt;&gt; Template('count: {{ qs.count }}, empty: {% if qs %}no{% else %}yes{% endif %}, qs: {{ qs }}, count: {{ qs.count }}').render(Context({'qs': User.objects.order_by('invalid_field')}))
Traceback (most recent call last):
File "&lt;console&gt;", line 1, in &lt;module&gt;
File "/Users/mrmachine/django/template/base.py", line 139, in render
return self._render(context)
File "/Users/mrmachine/django/template/base.py", line 133, in _render
return self.nodelist.render(context)
File "/Users/mrmachine/django/template/base.py", line 819, in render
bits = []
File "/Users/mrmachine/django/template/debug.py", line 73, in render_node
return node.render(context)
File "/Users/mrmachine/django/template/defaulttags.py", line 273, in render
if var:
File "/Users/mrmachine/django/db/models/query.py", line 129, in __nonzero__
iter(self).next()
File "/Users/mrmachine/django/db/models/query.py", line 117, in _result_iter
self._fill_cache()
File "/Users/mrmachine/django/db/models/query.py", line 855, in _fill_cache
self._result_cache.append(self._iter.next())
File "/Users/mrmachine/django/db/models/query.py", line 288, in iterator
for row in compiler.results_iter():
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 704, in results_iter
for rows in self.execute_sql(MULTI):
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 750, in execute_sql
sql, params = self.as_sql()
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 64, in as_sql
ordering, ordering_group_by = self.get_ordering()
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 364, in get_ordering
self.query.model._meta, default_order=asc):
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 393, in find_ordering_name
opts, alias, False)
File "/Users/mrmachine/django/db/models/sql/query.py", line 1289, in setup_joins
"Choices are: %s" % (name, ", ".join(names)))
FieldError: Cannot resolve keyword 'invalid_field' into field. Choices are: date_joined, email, first_name, groups, id, is_active, is_staff, is_superuser, last_login, last_name, logentry, password, user_permissions, username
</pre><p>
I think the <code>{% if %}</code> tag needs to be a little less quick to silence exceptions here, but there is also a problem with the way a <code>QuerySet</code> will appear to be empty after an exception is raised.
</p>
<p>
First, we get an exception:
</p>
<pre class="wiki">&gt;&gt;&gt; qs = User.objects.order_by('abc')
&gt;&gt;&gt; list(qs)
Traceback (most recent call last):
File "&lt;console&gt;", line 1, in &lt;module&gt;
File "/Users/mrmachine/django/db/models/query.py", line 86, in __len__
self._result_cache.extend(self._iter)
File "/Users/mrmachine/django/db/models/query.py", line 288, in iterator
for row in compiler.results_iter():
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 704, in results_iter
for rows in self.execute_sql(MULTI):
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 750, in execute_sql
sql, params = self.as_sql()
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 64, in as_sql
ordering, ordering_group_by = self.get_ordering()
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 364, in get_ordering
self.query.model._meta, default_order=asc):
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 393, in find_ordering_name
opts, alias, False)
File "/Users/mrmachine/django/db/models/sql/query.py", line 1289, in setup_joins
"Choices are: %s" % (name, ", ".join(names)))
FieldError: Cannot resolve keyword 'abc' into field. Choices are: date_joined, email, first_name, groups, id, is_active, is_staff, is_superuser, last_login, last_name, logentry, password, user_permissions, username
</pre><p>
Then, we appear to have an empty queryset:
</p>
<pre class="wiki">&gt;&gt;&gt; list(qs)
[]
</pre><p>
Even though the <code>Query</code> is still invalid:
</p>
<pre class="wiki">&gt;&gt;&gt; print qs.query
Traceback (most recent call last):
File "&lt;console&gt;", line 1, in &lt;module&gt;
File "/Users/mrmachine/django/db/models/sql/query.py", line 167, in __str__
sql, params = self.sql_with_params()
File "/Users/mrmachine/django/db/models/sql/query.py", line 175, in sql_with_params
return self.get_compiler(DEFAULT_DB_ALIAS).as_sql()
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 64, in as_sql
ordering, ordering_group_by = self.get_ordering()
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 364, in get_ordering
self.query.model._meta, default_order=asc):
File "/Users/mrmachine/django/db/models/sql/compiler.py", line 393, in find_ordering_name
opts, alias, False)
File "/Users/mrmachine/django/db/models/sql/query.py", line 1289, in setup_joins
"Choices are: %s" % (name, ", ".join(names)))
FieldError: Cannot resolve keyword 'abc' into field. Choices are: date_joined, email, first_name, groups, id, is_active, is_staff, is_superuser, last_login, last_name, logentry, password, user_permissions, username
</pre><p>
I think that this only becomes a problem when the exception is mistakenly silenced or not handled correctly, as in the example with the <code>{% if %}</code> tag. In most circumstances, the exception would be raised and further processing would not occur.
</p>
<p>
However, I think we should still look at the possibility of NOT caching results when a queryset fails to evaluate in this way. I do not think it is appropriate to equate an invalid queryset with an empty one. If an exception is raised once when trying to evaluate a queryset, the exception should be cached or the queryset should be re-evaluated when it is accessed again.
</p>
Resultshttps://code.djangoproject.com/ticket/17664#changeloghttps://code.djangoproject.com/ticket/9519
https://code.djangoproject.com/ticket/9519#9519: Add QuerySet.bulk_delete() that issues only a single SQL queryTue, 04 Nov 2008 19:53:07 GMTJoey Wilhelm<p>
Example:
</p>
<div class="wiki-code"><div class="code"><pre><span class="kn">from</span> <span class="nn">django.db</span> <span class="kn">import</span> models
<span class="k">class</span> <span class="nc">MyModel</span><span class="p">(</span>models<span class="o">.</span>Model<span class="p">):</span>
my_id <span class="o">=</span> models<span class="o">.</span>IntegerField<span class="p">(</span>primary_key<span class="o">=</span><span class="bp">True</span><span class="p">)</span>
col1 <span class="o">=</span> models<span class="o">.</span>IntegerField<span class="p">()</span>
col2 <span class="o">=</span> models<span class="o">.</span>CharField<span class="p">(</span>max_length<span class="o">=</span><span class="mi">1</span><span class="p">)</span>
col3 <span class="o">=</span> models<span class="o">.</span>TextField<span class="p">()</span>
<span class="k">class</span> <span class="nc">Meta</span><span class="p">:</span>
unique_together <span class="o">=</span> <span class="p">(</span><span class="s1">'my_id'</span><span class="p">,</span> <span class="s1">'col1'</span><span class="p">,</span> <span class="s1">'col2'</span><span class="p">)</span> <span class="c1"># "Fake" a multi-part primary key</span>
<span class="c1"># This works for creating all 3, as force_insert is used in create()</span>
MyModel<span class="o">.</span>objects<span class="o">.</span>create<span class="p">(</span>my_id<span class="o">=</span><span class="mi">1</span><span class="p">,</span> col1<span class="o">=</span><span class="mi">5</span><span class="p">,</span> col2<span class="o">=</span><span class="s1">'a'</span><span class="p">,</span> col3<span class="o">=</span><span class="s1">'foo'</span><span class="p">)</span>
MyModel<span class="o">.</span>objects<span class="o">.</span>create<span class="p">(</span>my_id<span class="o">=</span><span class="mi">1</span><span class="p">,</span> col1<span class="o">=</span><span class="mi">5</span><span class="p">,</span> col2<span class="o">=</span><span class="s1">'b'</span><span class="p">,</span> col3<span class="o">=</span><span class="s1">'bar'</span><span class="p">)</span>
MyModel<span class="o">.</span>objects<span class="o">.</span>create<span class="p">(</span>my_id<span class="o">=</span><span class="mi">1</span><span class="p">,</span> col1<span class="o">=</span><span class="mi">10</span><span class="p">,</span> col2<span class="o">=</span><span class="s1">'a'</span><span class="p">,</span> col3<span class="o">=</span><span class="s1">'baz'</span><span class="p">)</span>
MyModel<span class="o">.</span>objects<span class="o">.</span>filter<span class="p">(</span>my_id<span class="o">=</span><span class="mi">1</span><span class="p">,</span> col1<span class="o">=</span><span class="mi">5</span><span class="p">)</span><span class="o">.</span>delete<span class="p">()</span>
</pre></div></div><p>
This deletes all of the objects created above, since deletion is done only based on model._meta.pk. This is fine, except when you are using multi-part primary keys and emulating that pkey as suggested in <a class="assigned ticket" href="https://code.djangoproject.com/ticket/373" title="#373: New feature: Add support for multiple-column primary keys (assigned)">#373</a>.
</p>
<p>
In my opinion, the delete operation should be executed with respect to the filter(), extra(), etc which were previously performed on the QuerySet.
</p>
<p>
This is, in a way, a part of ticket <a class="assigned ticket" href="https://code.djangoproject.com/ticket/373" title="#373: New feature: Add support for multiple-column primary keys (assigned)">#373</a>, but I believe it can be viewed as a completely separate issue. In some cases, you do want to delete based on columns other than the primary key. Why build a list of all of those primary keys when you've already specified all of the qualifiers in a filter()?
</p>
Resultshttps://code.djangoproject.com/ticket/9519#changelog