ដកស្រង់ពីឯកសារផ្លូវការរបស់មូលនិធិ Django
Field lookups are how you specify the meat of an SQL WHERE clause. They’re specified as keyword arguments to the QuerySet methods filter(), exclude() and 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:
SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';
The field specified in a lookup has to be the name of a model field. There’s one exception though, in case of a 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)
The database API supports about two dozen lookup types; a complete reference can be found in the field lookup reference. To give you a taste of what’s available, here’s some of the more common lookups you’ll probably use:
exact
An “exact” match. For example:
>>> Entry.objects.get(headline__exact="Cat bites dog")
Would generate SQL along these lines:
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
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".
contains
Case-sensitive containment test. For example:
Entry.objects.get(headline__contains='Lennon')
Roughly translates to this 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, icontains.
startswith, endswith
Starts-with and ends-with search, respectively. There are also case-insensitive versions called istartswith and iendswith.
Django offers a powerful and intuitive way to “follow” relationships in lookups, taking care of the SQL JOINs for you automatically, behind the scenes. To span a relationship, 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. While it can be customized, by default you refer to a “reverse” relationship in a lookup using 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 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)
When you are filtering an object based on a ManyToManyField or a reverse 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 ManyToManyField. For example, if an Entry has a 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 filter() calls. Everything inside a single filter() call is applied simultaneously to filter out items matching all those requirements. Successive 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 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.
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 F expressions 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(number_of_comments__gt=F('number_of_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(number_of_comments__gt=F('number_of_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('number_of_comments') + F('number_of_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 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(), .bitor(), .bitxor(), .bitrightshift(), and .bitleftshift(). For example:
>>> F('somefield').bitand(16)
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, 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:
SELECT ... WHERE headline LIKE '%\%%';
Same goes for underscores. Both percentage signs and underscores are handled for you transparently.
