-
-
Notifications
You must be signed in to change notification settings - Fork 31.6k
/
queries.txt
1356 lines (979 loc) · 52.8 KB
/
queries.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
==============
Making queries
==============
.. currentmodule:: django.db.models
Once you've created your :doc:`data models </topics/db/models>`, Django
automatically gives you a database-abstraction API that lets you create,
retrieve, update and delete objects. This document explains how to use this
API. Refer to the :doc:`data model reference </ref/models/index>` for full
details of all the various model lookup options.
Throughout this guide (and in the reference), we'll refer to the following
models, which comprise a Weblog application:
.. _queryset-model-example:
.. code-block:: python
from django.db import models
class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()
def __str__(self): # __unicode__ on Python 2
return self.name
class Author(models.Model):
name = models.CharField(max_length=50)
email = models.EmailField()
def __str__(self): # __unicode__ on Python 2
return self.name
class Entry(models.Model):
blog = models.ForeignKey(Blog)
headline = models.CharField(max_length=255)
body_text = models.TextField()
pub_date = models.DateField()
mod_date = models.DateField()
authors = models.ManyToManyField(Author)
n_comments = models.IntegerField()
n_pingbacks = models.IntegerField()
rating = models.IntegerField()
def __str__(self): # __unicode__ on Python 2
return self.headline
Creating objects
================
To represent database-table data in Python objects, Django uses an intuitive
system: A model class represents a database table, and an instance of that
class represents a particular record in the database table.
To create an object, instantiate it using keyword arguments to the model class,
then call :meth:`~django.db.models.Model.save` to save it to the database.
Assuming models live in a file ``mysite/blog/models.py``, here's an example::
>>> from blog.models import Blog
>>> b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
>>> b.save()
This performs an ``INSERT`` SQL statement behind the scenes. Django doesn't hit
the database until you explicitly call :meth:`~django.db.models.Model.save`.
The :meth:`~django.db.models.Model.save` method has no return value.
.. seealso::
:meth:`~django.db.models.Model.save` takes a number of advanced options not
described here. See the documentation for
:meth:`~django.db.models.Model.save` for complete details.
To create and save an object in a single step, use the
:meth:`~django.db.models.query.QuerySet.create()` method.
Saving changes to objects
=========================
To save changes to an object that's already in the database, use
:meth:`~django.db.models.Model.save`.
Given a ``Blog`` instance ``b5`` that has already been saved to the database,
this example changes its name and updates its record in the database::
>>> b5.name = 'New name'
>>> b5.save()
This performs an ``UPDATE`` SQL statement behind the scenes. Django doesn't hit
the database until you explicitly call :meth:`~django.db.models.Model.save`.
Saving ``ForeignKey`` and ``ManyToManyField`` fields
----------------------------------------------------
Updating a :class:`~django.db.models.ForeignKey` field works exactly the same
way as saving a normal field -- simply assign an object of the right type to
the field in question. This example updates the ``blog`` attribute of an
``Entry`` instance ``entry``, assuming appropriate instances of ``Entry`` and
``Blog`` are already saved to the database (so we can retrieve them below)::
>>> from blog.models import Entry
>>> entry = Entry.objects.get(pk=1)
>>> cheese_blog = Blog.objects.get(name="Cheddar Talk")
>>> entry.blog = cheese_blog
>>> entry.save()
Updating a :class:`~django.db.models.ManyToManyField` works a little
differently -- use the
:meth:`~django.db.models.fields.related.RelatedManager.add` method on the field
to add a record to the relation. This example adds the ``Author`` instance
``joe`` to the ``entry`` object::
>>> from blog.models import Author
>>> joe = Author.objects.create(name="Joe")
>>> entry.authors.add(joe)
To add multiple records to a :class:`~django.db.models.ManyToManyField` in one
go, include multiple arguments in the call to
:meth:`~django.db.models.fields.related.RelatedManager.add`, like this::
>>> john = Author.objects.create(name="John")
>>> paul = Author.objects.create(name="Paul")
>>> george = Author.objects.create(name="George")
>>> ringo = Author.objects.create(name="Ringo")
>>> entry.authors.add(john, paul, george, ringo)
Django will complain if you try to assign or add an object of the wrong type.
.. _retrieving-objects:
Retrieving objects
==================
To retrieve objects from your database, construct a
:class:`~django.db.models.query.QuerySet` via a
:class:`~django.db.models.Manager` on your model class.
A :class:`~django.db.models.query.QuerySet` represents a collection of objects
from your database. It can have zero, one or many *filters*. Filters narrow
down the query results based on the given parameters. In SQL terms, a
:class:`~django.db.models.query.QuerySet` equates to a ``SELECT`` statement,
and a filter is a limiting clause such as ``WHERE`` or ``LIMIT``.
You get a :class:`~django.db.models.query.QuerySet` by using your model's
:class:`~django.db.models.Manager`. Each model has at least one
:class:`~django.db.models.Manager`, and it's called
:attr:`~django.db.models.Model.objects` by default. Access it directly via the
model class, like so::
>>> Blog.objects
<django.db.models.manager.Manager object at ...>
>>> b = Blog(name='Foo', tagline='Bar')
>>> b.objects
Traceback:
...
AttributeError: "Manager isn't accessible via Blog instances."
.. note::
``Managers`` are accessible only via model classes, rather than from model
instances, to enforce a separation between "table-level" operations and
"record-level" operations.
The :class:`~django.db.models.Manager` is the main source of ``QuerySets`` for
a model. For example, ``Blog.objects.all()`` returns a
:class:`~django.db.models.query.QuerySet` that contains all ``Blog`` objects in
the database.
Retrieving all objects
----------------------
The simplest way to retrieve objects from a table is to get all of them. To do
this, use the :meth:`~django.db.models.query.QuerySet.all` method on a
:class:`~django.db.models.Manager`::
>>> all_entries = Entry.objects.all()
The :meth:`~django.db.models.query.QuerySet.all` method returns a
:class:`~django.db.models.query.QuerySet` of all the objects in the database.
Retrieving specific objects with filters
----------------------------------------
The :class:`~django.db.models.query.QuerySet` returned by
:meth:`~django.db.models.query.QuerySet.all` describes all objects in the
database table. Usually, though, you'll need to select only a subset of the
complete set of objects.
To create such a subset, you refine the initial
:class:`~django.db.models.query.QuerySet`, adding filter conditions. The two
most common ways to refine a :class:`~django.db.models.query.QuerySet` are:
``filter(**kwargs)``
Returns a new :class:`~django.db.models.query.QuerySet` containing objects
that match the given lookup parameters.
``exclude(**kwargs)``
Returns a new :class:`~django.db.models.query.QuerySet` containing objects
that do *not* match the given lookup parameters.
The lookup parameters (``**kwargs`` in the above function definitions) should
be in the format described in `Field lookups`_ below.
For example, to get a :class:`~django.db.models.query.QuerySet` of blog entries
from the year 2006, use :meth:`~django.db.models.query.QuerySet.filter` like
so::
Entry.objects.filter(pub_date__year=2006)
With the default manager class, it is the same as::
Entry.objects.all().filter(pub_date__year=2006)
.. _chaining-filters:
Chaining filters
~~~~~~~~~~~~~~~~
The result of refining a :class:`~django.db.models.query.QuerySet` is itself a
:class:`~django.db.models.query.QuerySet`, so it's possible to chain
refinements together. For example::
>>> Entry.objects.filter(
... headline__startswith='What'
... ).exclude(
... pub_date__gte=datetime.date.today()
... ).filter(
... pub_date__gte=datetime(2005, 1, 30)
... )
This takes the initial :class:`~django.db.models.query.QuerySet` of all entries
in the database, adds a filter, then an exclusion, then another filter. The
final result is a :class:`~django.db.models.query.QuerySet` containing all
entries with a headline that starts with "What", that were published between
January 30, 2005, and the current day.
.. _filtered-querysets-are-unique:
Filtered QuerySets are unique
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Each time you refine a :class:`~django.db.models.query.QuerySet`, you get a
brand-new :class:`~django.db.models.query.QuerySet` that is in no way bound to
the previous :class:`~django.db.models.query.QuerySet`. Each refinement creates
a separate and distinct :class:`~django.db.models.query.QuerySet` that can be
stored, used and reused.
Example::
>>> q1 = Entry.objects.filter(headline__startswith="What")
>>> q2 = q1.exclude(pub_date__gte=datetime.date.today())
>>> q3 = q1.filter(pub_date__gte=datetime.date.today())
These three ``QuerySets`` are separate. The first is a base
:class:`~django.db.models.query.QuerySet` containing all entries that contain a
headline starting with "What". The second is a subset of the first, with an
additional criteria that excludes records whose ``pub_date`` is today or in the
future. The third is a subset of the first, with an additional criteria that
selects only the records whose ``pub_date`` is today or in the future. The
initial :class:`~django.db.models.query.QuerySet` (``q1``) is unaffected by the
refinement process.
.. _querysets-are-lazy:
QuerySets are lazy
~~~~~~~~~~~~~~~~~~
``QuerySets`` are lazy -- the act of creating a
:class:`~django.db.models.query.QuerySet` doesn't involve any database
activity. You can stack filters together all day long, and Django won't
actually run the query until the :class:`~django.db.models.query.QuerySet` is
*evaluated*. Take a look at this example::
>>> q = Entry.objects.filter(headline__startswith="What")
>>> q = q.filter(pub_date__lte=datetime.date.today())
>>> q = q.exclude(body_text__icontains="food")
>>> print(q)
Though this looks like three database hits, in fact it hits the database only
once, at the last line (``print(q)``). In general, the results of a
:class:`~django.db.models.query.QuerySet` aren't fetched from the database
until you "ask" for them. When you do, the
:class:`~django.db.models.query.QuerySet` is *evaluated* by accessing the
database. For more details on exactly when evaluation takes place, see
:ref:`when-querysets-are-evaluated`.
.. _retrieving-single-object-with-get:
Retrieving a single object with get
-----------------------------------
:meth:`~django.db.models.query.QuerySet.filter` will always give you a
:class:`~django.db.models.query.QuerySet`, even if only a single object matches
the query - in this case, it will be a
:class:`~django.db.models.query.QuerySet` containing a single element.
If you know there is only one object that matches your query, you can use the
:meth:`~django.db.models.query.QuerySet.get` method on a
:class:`~django.db.models.Manager` which returns the object directly::
>>> one_entry = Entry.objects.get(pk=1)
You can use any query expression with
:meth:`~django.db.models.query.QuerySet.get`, just like with
:meth:`~django.db.models.query.QuerySet.filter` - again, see `Field lookups`_
below.
Note that there is a difference between using
:meth:`~django.db.models.query.QuerySet.get`, and using
:meth:`~django.db.models.query.QuerySet.filter` with a slice of ``[0]``. If
there are no results that match the query,
:meth:`~django.db.models.query.QuerySet.get` will raise a ``DoesNotExist``
exception. This exception is an attribute of the model class that the query is
being performed on - so in the code above, if there is no ``Entry`` object with
a primary key of 1, Django will raise ``Entry.DoesNotExist``.
Similarly, Django will complain if more than one item matches the
:meth:`~django.db.models.query.QuerySet.get` query. In this case, it will raise
:exc:`~django.core.exceptions.MultipleObjectsReturned`, which again is an
attribute of the model class itself.
Other QuerySet methods
----------------------
Most of the time you'll use :meth:`~django.db.models.query.QuerySet.all`,
:meth:`~django.db.models.query.QuerySet.get`,
:meth:`~django.db.models.query.QuerySet.filter` and
:meth:`~django.db.models.query.QuerySet.exclude` when you need to look up
objects from the database. However, that's far from all there is; see the
:ref:`QuerySet API Reference <queryset-api>` for a complete list of all the
various :class:`~django.db.models.query.QuerySet` methods.
.. _limiting-querysets:
Limiting QuerySets
------------------
Use a subset of Python's array-slicing syntax to limit your
:class:`~django.db.models.query.QuerySet` to a certain number of results. This
is the equivalent of SQL's ``LIMIT`` and ``OFFSET`` clauses.
For example, this returns the first 5 objects (``LIMIT 5``)::
>>> Entry.objects.all()[:5]
This returns the sixth through tenth objects (``OFFSET 5 LIMIT 5``)::
>>> Entry.objects.all()[5:10]
Negative indexing (i.e. ``Entry.objects.all()[-1]``) is not supported.
Generally, slicing a :class:`~django.db.models.query.QuerySet` returns a new
:class:`~django.db.models.query.QuerySet` -- it doesn't evaluate the query. An
exception is if you use the "step" parameter of Python slice syntax. For
example, this would actually execute the query in order to return a list of
every *second* object of the first 10::
>>> Entry.objects.all()[:10:2]
To retrieve a *single* object rather than a list
(e.g. ``SELECT foo FROM bar LIMIT 1``), use a simple index instead of a
slice. For example, this returns the first ``Entry`` in the database, after
ordering entries alphabetically by headline::
>>> Entry.objects.order_by('headline')[0]
This is roughly equivalent to::
>>> Entry.objects.order_by('headline')[0:1].get()
Note, however, that the first of these will raise ``IndexError`` while the
second will raise ``DoesNotExist`` if no objects match the given criteria. See
:meth:`~django.db.models.query.QuerySet.get` for more details.
.. _field-lookups-intro:
Field lookups
-------------
Field lookups are how you specify the meat of an SQL ``WHERE`` clause. They're
specified as keyword arguments to the :class:`~django.db.models.query.QuerySet`
methods :meth:`~django.db.models.query.QuerySet.filter`,
:meth:`~django.db.models.query.QuerySet.exclude` and
:meth:`~django.db.models.query.QuerySet.get`.
Basic lookups keyword arguments take the form ``field__lookuptype=value``.
(That's a double-underscore). For example::
>>> Entry.objects.filter(pub_date__lte='2006-01-01')
translates (roughly) into the following SQL:
.. code-block:: sql
SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';
.. admonition:: How this is possible
Python has the ability to define functions that accept arbitrary name-value
arguments whose names and values are evaluated at runtime. For more
information, see `Keyword Arguments`_ in the official Python tutorial.
.. _`Keyword Arguments`: https://docs.python.org/tutorial/controlflow.html#keyword-arguments
The field specified in a lookup has to be the name of a model field. There's
one exception though, in case of a :class:`~django.db.models.ForeignKey` you
can specify the field name suffixed with ``_id``. In this case, the value
parameter is expected to contain the raw value of the foreign model's primary
key. For example:
>>> Entry.objects.filter(blog_id=4)
If you pass an invalid keyword argument, a lookup function will raise
``TypeError``.
The database API supports about two dozen lookup types; a complete reference
can be found in the :ref:`field lookup reference <field-lookups>`. To give you
a taste of what's available, here's some of the more common lookups you'll
probably use:
:lookup:`exact`
An "exact" match. For example::
>>> Entry.objects.get(headline__exact="Cat bites dog")
Would generate SQL along these lines:
.. code-block:: sql
SELECT ... WHERE headline = 'Cat bites dog';
If you don't provide a lookup type -- that is, if your keyword argument
doesn't contain a double underscore -- the lookup type is assumed to be
``exact``.
For example, the following two statements are equivalent::
>>> Blog.objects.get(id__exact=14) # Explicit form
>>> Blog.objects.get(id=14) # __exact is implied
This is for convenience, because ``exact`` lookups are the common case.
:lookup:`iexact`
A case-insensitive match. So, the query::
>>> Blog.objects.get(name__iexact="beatles blog")
Would match a ``Blog`` titled ``"Beatles Blog"``, ``"beatles blog"``, or
even ``"BeAtlES blOG"``.
:lookup:`contains`
Case-sensitive containment test. For example::
Entry.objects.get(headline__contains='Lennon')
Roughly translates to this SQL:
.. code-block:: sql
SELECT ... WHERE headline LIKE '%Lennon%';
Note this will match the headline ``'Today Lennon honored'`` but not
``'today lennon honored'``.
There's also a case-insensitive version, :lookup:`icontains`.
:lookup:`startswith`, :lookup:`endswith`
Starts-with and ends-with search, respectively. There are also
case-insensitive versions called :lookup:`istartswith` and
:lookup:`iendswith`.
Again, this only scratches the surface. A complete reference can be found in the
:ref:`field lookup reference <field-lookups>`.
.. _lookups-that-span-relationships:
Lookups that span relationships
-------------------------------
Django offers a powerful and intuitive way to "follow" relationships in
lookups, taking care of the SQL ``JOIN``\s for you automatically, behind the
scenes. To span a relationship, just use the field name of related fields
across models, separated by double underscores, until you get to the field you
want.
This example retrieves all ``Entry`` objects with a ``Blog`` whose ``name``
is ``'Beatles Blog'``::
>>> Entry.objects.filter(blog__name='Beatles Blog')
This spanning can be as deep as you'd like.
It works backwards, too. To refer to a "reverse" relationship, just use the
lowercase name of the model.
This example retrieves all ``Blog`` objects which have at least one ``Entry``
whose ``headline`` contains ``'Lennon'``::
>>> Blog.objects.filter(entry__headline__contains='Lennon')
If you are filtering across multiple relationships and one of the intermediate
models doesn't have a value that meets the filter condition, Django will treat
it as if there is an empty (all values are ``NULL``), but valid, object there.
All this means is that no error will be raised. For example, in this filter::
Blog.objects.filter(entry__authors__name='Lennon')
(if there was a related ``Author`` model), if there was no ``author``
associated with an entry, it would be treated as if there was also no ``name``
attached, rather than raising an error because of the missing ``author``.
Usually this is exactly what you want to have happen. The only case where it
might be confusing is if you are using :lookup:`isnull`. Thus::
Blog.objects.filter(entry__authors__name__isnull=True)
will return ``Blog`` objects that have an empty ``name`` on the ``author`` and
also those which have an empty ``author`` on the ``entry``. If you don't want
those latter objects, you could write::
Blog.objects.filter(entry__authors__isnull=False,
entry__authors__name__isnull=True)
Spanning multi-valued relationships
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When you are filtering an object based on a
:class:`~django.db.models.ManyToManyField` or a reverse
:class:`~django.db.models.ForeignKey`, there are two different sorts of filter
you may be interested in. Consider the ``Blog``/``Entry`` relationship
(``Blog`` to ``Entry`` is a one-to-many relation). We might be interested in
finding blogs that have an entry which has both *"Lennon"* in the headline and
was published in 2008. Or we might want to find blogs that have an entry with
*"Lennon"* in the headline as well as an entry that was published
in 2008. Since there are multiple entries associated with a single ``Blog``,
both of these queries are possible and make sense in some situations.
The same type of situation arises with a
:class:`~django.db.models.ManyToManyField`. For example, if an ``Entry`` has a
:class:`~django.db.models.ManyToManyField` called ``tags``, we might want to
find entries linked to tags called *"music"* and *"bands"* or we might want an
entry that contains a tag with a name of *"music"* and a status of *"public"*.
To handle both of these situations, Django has a consistent way of processing
:meth:`~django.db.models.query.QuerySet.filter` calls. Everything inside a
single :meth:`~django.db.models.query.QuerySet.filter` call is applied
simultaneously to filter out items matching all those requirements. Successive
:meth:`~django.db.models.query.QuerySet.filter` calls further restrict the set
of objects, but for multi-valued relations, they apply to any object linked to
the primary model, not necessarily those objects that were selected by an
earlier :meth:`~django.db.models.query.QuerySet.filter` call.
That may sound a bit confusing, so hopefully an example will clarify. To
select all blogs that contain entries with both *"Lennon"* in the headline
and that were published in 2008 (the same entry satisfying both conditions),
we would write::
Blog.objects.filter(entry__headline__contains='Lennon',
entry__pub_date__year=2008)
To select all blogs that contain an entry with *"Lennon"* in the headline
**as well as** an entry that was published in 2008, we would write::
Blog.objects.filter(entry__headline__contains='Lennon').filter(
entry__pub_date__year=2008)
Suppose there is only one blog that had both entries containing *"Lennon"* and
entries from 2008, but that none of the entries from 2008 contained *"Lennon"*.
The first query would not return any blogs, but the second query would return
that one blog.
In the second example, the first filter restricts the queryset to all those
blogs linked to entries with *"Lennon"* in the headline. The second filter
restricts the set of blogs *further* to those that are also linked to entries
that were published in 2008. The entries selected by the second filter may or
may not be the same as the entries in the first filter. We are filtering the
``Blog`` items with each filter statement, not the ``Entry`` items.
.. note::
The behavior of :meth:`~django.db.models.query.QuerySet.filter` for queries
that span multi-value relationships, as described above, is not implemented
equivalently for :meth:`~django.db.models.query.QuerySet.exclude`. Instead,
the conditions in a single :meth:`~django.db.models.query.QuerySet.exclude`
call will not necessarily refer to the same item.
For example, the following query would exclude blogs that contain *both*
entries with *"Lennon"* in the headline *and* entries published in 2008::
Blog.objects.exclude(
entry__headline__contains='Lennon',
entry__pub_date__year=2008,
)
However, unlike the behavior when using
:meth:`~django.db.models.query.QuerySet.filter`, this will not limit blogs
based on entries that satisfy both conditions. In order to do that, i.e.
to select all blogs that do not contain entries published with *"Lennon"*
that were published in 2008, you need to make two queries::
Blog.objects.exclude(
entry=Entry.objects.filter(
headline__contains='Lennon',
pub_date__year=2008,
),
)
.. _using-f-expressions-in-filters:
Filters can reference fields on the model
-----------------------------------------
In the examples given so far, we have constructed filters that compare
the value of a model field with a constant. But what if you want to compare
the value of a model field with another field on the same model?
Django provides :class:`F expressions <django.db.models.F>` to allow such
comparisons. Instances of ``F()`` act as a reference to a model field within a
query. These references can then be used in query filters to compare the values
of two different fields on the same model instance.
For example, to find a list of all blog entries that have had more comments
than pingbacks, we construct an ``F()`` object to reference the pingback count,
and use that ``F()`` object in the query::
>>> from django.db.models import F
>>> Entry.objects.filter(n_comments__gt=F('n_pingbacks'))
Django supports the use of addition, subtraction, multiplication,
division, modulo, and power arithmetic with ``F()`` objects, both with constants
and with other ``F()`` objects. To find all the blog entries with more than
*twice* as many comments as pingbacks, we modify the query::
>>> Entry.objects.filter(n_comments__gt=F('n_pingbacks') * 2)
To find all the entries where the rating of the entry is less than the
sum of the pingback count and comment count, we would issue the
query::
>>> Entry.objects.filter(rating__lt=F('n_comments') + F('n_pingbacks'))
You can also use the double underscore notation to span relationships in
an ``F()`` object. An ``F()`` object with a double underscore will introduce
any joins needed to access the related object. For example, to retrieve all
the entries where the author's name is the same as the blog name, we could
issue the query::
>>> Entry.objects.filter(authors__name=F('blog__name'))
For date and date/time fields, you can add or subtract a
:class:`~datetime.timedelta` object. The following would return all entries
that were modified more than 3 days after they were published::
>>> from datetime import timedelta
>>> Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))
The ``F()`` objects support bitwise operations by ``.bitand()`` and
``.bitor()``, for example::
>>> F('somefield').bitand(16)
The pk lookup shortcut
----------------------
For convenience, Django provides a ``pk`` lookup shortcut, which stands for
"primary key".
In the example ``Blog`` model, the primary key is the ``id`` field, so these
three statements are equivalent::
>>> Blog.objects.get(id__exact=14) # Explicit form
>>> Blog.objects.get(id=14) # __exact is implied
>>> Blog.objects.get(pk=14) # pk implies id__exact
The use of ``pk`` isn't limited to ``__exact`` queries -- any query term
can be combined with ``pk`` to perform a query on the primary key of a model::
# Get blogs entries with id 1, 4 and 7
>>> Blog.objects.filter(pk__in=[1,4,7])
# Get all blog entries with id > 14
>>> Blog.objects.filter(pk__gt=14)
``pk`` lookups also work across joins. For example, these three statements are
equivalent::
>>> Entry.objects.filter(blog__id__exact=3) # Explicit form
>>> Entry.objects.filter(blog__id=3) # __exact is implied
>>> Entry.objects.filter(blog__pk=3) # __pk implies __id__exact
Escaping percent signs and underscores in LIKE statements
---------------------------------------------------------
The field lookups that equate to ``LIKE`` SQL statements (``iexact``,
``contains``, ``icontains``, ``startswith``, ``istartswith``, ``endswith``
and ``iendswith``) will automatically escape the two special characters used in
``LIKE`` statements -- the percent sign and the underscore. (In a ``LIKE``
statement, the percent sign signifies a multiple-character wildcard and the
underscore signifies a single-character wildcard.)
This means things should work intuitively, so the abstraction doesn't leak.
For example, to retrieve all the entries that contain a percent sign, just use
the percent sign as any other character::
>>> Entry.objects.filter(headline__contains='%')
Django takes care of the quoting for you; the resulting SQL will look something
like this:
.. code-block:: sql
SELECT ... WHERE headline LIKE '%\%%';
Same goes for underscores. Both percentage signs and underscores are handled
for you transparently.
.. _caching-and-querysets:
Caching and QuerySets
---------------------
Each :class:`~django.db.models.query.QuerySet` contains a cache to minimize
database access. Understanding how it works will allow you to write the most
efficient code.
In a newly created :class:`~django.db.models.query.QuerySet`, the cache is
empty. The first time a :class:`~django.db.models.query.QuerySet` is evaluated
-- and, hence, a database query happens -- Django saves the query results in
the :class:`~django.db.models.query.QuerySet`’s cache and returns the results
that have been explicitly requested (e.g., the next element, if the
:class:`~django.db.models.query.QuerySet` is being iterated over). Subsequent
evaluations of the :class:`~django.db.models.query.QuerySet` reuse the cached
results.
Keep this caching behavior in mind, because it may bite you if you don't use
your :class:`~django.db.models.query.QuerySet`\s correctly. For example, the
following will create two :class:`~django.db.models.query.QuerySet`\s, evaluate
them, and throw them away::
>>> print([e.headline for e in Entry.objects.all()])
>>> print([e.pub_date for e in Entry.objects.all()])
That means the same database query will be executed twice, effectively doubling
your database load. Also, there's a possibility the two lists may not include
the same database records, because an ``Entry`` may have been added or deleted
in the split second between the two requests.
To avoid this problem, simply save the
:class:`~django.db.models.query.QuerySet` and reuse it::
>>> queryset = Entry.objects.all()
>>> print([p.headline for p in queryset]) # Evaluate the query set.
>>> print([p.pub_date for p in queryset]) # Re-use the cache from the evaluation.
When querysets are not cached
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Querysets do not always cache their results. When evaluating only *part* of
the queryset, the cache is checked, but if it is not populated then the items
returned by the subsequent query are not cached. Specifically, this means that
:ref:`limiting the queryset <limiting-querysets>` using an array slice or an
index will not populate the cache.
For example, repeatedly getting a certain index in a queryset object will query
the database each time::
>>> queryset = Entry.objects.all()
>>> print queryset[5] # Queries the database
>>> print queryset[5] # Queries the database again
However, if the entire queryset has already been evaluated, the cache will be
checked instead::
>>> queryset = Entry.objects.all()
>>> [entry for entry in queryset] # Queries the database
>>> print queryset[5] # Uses cache
>>> print queryset[5] # Uses cache
Here are some examples of other actions that will result in the entire queryset
being evaluated and therefore populate the cache::
>>> [entry for entry in queryset]
>>> bool(queryset)
>>> entry in queryset
>>> list(queryset)
.. note::
Simply printing the queryset will not populate the cache. This is because
the call to ``__repr__()`` only returns a slice of the entire queryset.
.. _complex-lookups-with-q:
Complex lookups with Q objects
==============================
Keyword argument queries -- in :meth:`~django.db.models.query.QuerySet.filter`,
etc. -- are "AND"ed together. If you need to execute more complex queries (for
example, queries with ``OR`` statements), you can use :class:`Q objects <django.db.models.Q>`.
A :class:`Q object <django.db.models.Q>` (``django.db.models.Q``) is an object
used to encapsulate a collection of keyword arguments. These keyword arguments
are specified as in "Field lookups" above.
For example, this ``Q`` object encapsulates a single ``LIKE`` query::
from django.db.models import Q
Q(question__startswith='What')
``Q`` objects can be combined using the ``&`` and ``|`` operators. When an
operator is used on two ``Q`` objects, it yields a new ``Q`` object.
For example, this statement yields a single ``Q`` object that represents the
"OR" of two ``"question__startswith"`` queries::
Q(question__startswith='Who') | Q(question__startswith='What')
This is equivalent to the following SQL ``WHERE`` clause::
WHERE question LIKE 'Who%' OR question LIKE 'What%'
You can compose statements of arbitrary complexity by combining ``Q`` objects
with the ``&`` and ``|`` operators and use parenthetical grouping. Also, ``Q``
objects can be negated using the ``~`` operator, allowing for combined lookups
that combine both a normal query and a negated (``NOT``) query::
Q(question__startswith='Who') | ~Q(pub_date__year=2005)
Each lookup function that takes keyword-arguments
(e.g. :meth:`~django.db.models.query.QuerySet.filter`,
:meth:`~django.db.models.query.QuerySet.exclude`,
:meth:`~django.db.models.query.QuerySet.get`) can also be passed one or more
``Q`` objects as positional (not-named) arguments. If you provide multiple
``Q`` object arguments to a lookup function, the arguments will be "AND"ed
together. For example::
Poll.objects.get(
Q(question__startswith='Who'),
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
... roughly translates into the SQL::
SELECT * from polls WHERE question LIKE 'Who%'
AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')
Lookup functions can mix the use of ``Q`` objects and keyword arguments. All
arguments provided to a lookup function (be they keyword arguments or ``Q``
objects) are "AND"ed together. However, if a ``Q`` object is provided, it must
precede the definition of any keyword arguments. For example::
Poll.objects.get(
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
question__startswith='Who')
... would be a valid query, equivalent to the previous example; but::
# INVALID QUERY
Poll.objects.get(
question__startswith='Who',
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)))
... would not be valid.
.. seealso::
The `OR lookups examples`_ in the Django unit tests show some possible uses
of ``Q``.
.. _OR lookups examples: https://github.com/django/django/blob/master/tests/or_lookups/tests.py
Comparing objects
=================
To compare two model instances, just use the standard Python comparison operator,
the double equals sign: ``==``. Behind the scenes, that compares the primary
key values of two models.
Using the ``Entry`` example above, the following two statements are equivalent::
>>> some_entry == other_entry
>>> some_entry.id == other_entry.id
If a model's primary key isn't called ``id``, no problem. Comparisons will
always use the primary key, whatever it's called. For example, if a model's
primary key field is called ``name``, these two statements are equivalent::
>>> some_obj == other_obj
>>> some_obj.name == other_obj.name
.. _topics-db-queries-delete:
Deleting objects
================
The delete method, conveniently, is named
:meth:`~django.db.models.Model.delete`. This method immediately deletes the
object and returns the number of objects deleted and a dictionary with
the number of deletions per object type. Example::
>>> e.delete()
(1, {'weblog.Entry': 1})
.. versionchanged:: 1.9
The return value describing the number of objects deleted was added.
You can also delete objects in bulk. Every
:class:`~django.db.models.query.QuerySet` has a
:meth:`~django.db.models.query.QuerySet.delete` method, which deletes all
members of that :class:`~django.db.models.query.QuerySet`.
For example, this deletes all ``Entry`` objects with a ``pub_date`` year of
2005::
>>> Entry.objects.filter(pub_date__year=2005).delete()
(5, {'webapp.Entry': 5})
Keep in mind that this will, whenever possible, be executed purely in SQL, and
so the ``delete()`` methods of individual object instances will not necessarily
be called during the process. If you've provided a custom ``delete()`` method
on a model class and want to ensure that it is called, you will need to
"manually" delete instances of that model (e.g., by iterating over a
:class:`~django.db.models.query.QuerySet` and calling ``delete()`` on each
object individually) rather than using the bulk
:meth:`~django.db.models.query.QuerySet.delete` method of a
:class:`~django.db.models.query.QuerySet`.
.. versionchanged:: 1.9
The return value describing the number of objects deleted was added.
When Django deletes an object, by default it emulates the behavior of the SQL
constraint ``ON DELETE CASCADE`` -- in other words, any objects which had
foreign keys pointing at the object to be deleted will be deleted along with
it. For example::
b = Blog.objects.get(pk=1)
# This will delete the Blog and all of its Entry objects.
b.delete()
This cascade behavior is customizable via the
:attr:`~django.db.models.ForeignKey.on_delete` argument to the
:class:`~django.db.models.ForeignKey`.
Note that :meth:`~django.db.models.query.QuerySet.delete` is the only
:class:`~django.db.models.query.QuerySet` method that is not exposed on a
:class:`~django.db.models.Manager` itself. This is a safety mechanism to
prevent you from accidentally requesting ``Entry.objects.delete()``, and
deleting *all* the entries. If you *do* want to delete all the objects, then
you have to explicitly request a complete query set::
Entry.objects.all().delete()
.. _topics-db-queries-copy:
Copying model instances
=======================
Although there is no built-in method for copying model instances, it is
possible to easily create new instance with all fields' values copied. In the
simplest case, you can just set ``pk`` to ``None``. Using our blog example::
blog = Blog(name='My blog', tagline='Blogging is easy')
blog.save() # blog.pk == 1
blog.pk = None
blog.save() # blog.pk == 2
Things get more complicated if you use inheritance. Consider a subclass of
``Blog``::
class ThemeBlog(Blog):
theme = models.CharField(max_length=200)
django_blog = ThemeBlog(name='Django', tagline='Django is easy', theme='python')
django_blog.save() # django_blog.pk == 3
Due to how inheritance works, you have to set both ``pk`` and ``id`` to None::
django_blog.pk = None
django_blog.id = None
django_blog.save() # django_blog.pk == 4
This process does not copy related objects. If you want to copy relations,
you have to write a little bit more code. In our example, ``Entry`` has a many to many
field to ``Author``::
entry = Entry.objects.all()[0] # some previous entry
old_authors = entry.authors.all()
entry.pk = None
entry.save()
entry.authors = old_authors # saves new many2many relations
.. _topics-db-queries-update: