#lookup types

[katepdonahue]

Django: Querying the Database

Django: Querying the Database

Here are the two basic ways to query the database in Django. get and filter:

<ModelName>.objects.get(<unique_attribute>=<value>)

—> returns a <ModelName> object

<ModelName>.objects.filter(<nonunique_attribute>=<value>)

—> returns a Django QuerySet object

for example:

Article.objects.get(id=2)

—> returns the article with id=2

Article.objects.filter(author=“Kate”)

—> returns a QuerySet object with all the articles Kate has written

What can you do with a QuerySet object? It’s a bit like a Python list (a Ruby array).

You can access particular article objects by index

Article.objects.filter(author=“Kate”)[0]

—> returns the first article object from the QuerySet

You can chain further queries:

Article.objects.filter(author=“Kate”).filter(source=“New York Times”)

—> returns the articles written by Kate from the NY Times or just write the same thing like this:

Article.objects.filter(author=“Kate”, source=“New York Times”)

except…

Article.objects.filter(author=“Kate”).except(source=“New York Times”)

—> returns articles written by Kate not for the NY Times

You can order by an attribute:

Article.objects.filter(author=“Kate”).order_by(‘-pub_date’, ’source’)

—> QuerySet will be ordered from most recent to least recent (descending because of minus sign) and objects published on the same day will be sorted alphabetically by source (ascending because no minus)

Lookup Types: You can get more complex with your queries by using lookup types.

<ModelName>.objects.filter(<nonunique_attribute>__<lookup_type>=<value>)

Note: that’s a double underscore before the lookup type.

For example the ‘startswith' lookup:

Article.objects.filter(author__startswith=“K”)

—> returns the articles written by authors who start with “K"

A lookup type can also be an attribute: .objects.filter(__=)

For example, what if instead of just a name, the author field was a foreign key which pointed to an Author object.

Article.objects.filter(author__name=“Kate”)

—> grabs the articles written by the Author object with the name “Kate”

What if there’s a many-to-one field. The ‘contains' lookup:

<ModelName>.objects.filter(<has_many_attribute>__contains=<value>)

For example, if articles have many authors.

my_author = Author.get(id=3) Article.objects.filter(authors__contains=my_author)

—> returns the articles written by my_author (co-written, perhaps)

The ‘in’ lookup is the opposite: articles_i_want_to_read = a list or QuerySet object, for this example it’s a list of article names

Article.objects.filter(name__in=articles_i_want_to_read)

—> returns the article objects for the articles I want to read

More lookup types:

__contains   (case sensitive) __icontains  (case insensitive) __in __year          (for datetime attributes) __startswith __lte             (less than or equal to) __gte            (greater than or equal to) __exact        (assumed to be exact if no “lookup type”) __iexact       (case insensitive lookup, __exact is default lookup) __isnull=True/False

And pk = primary key interchangeable with id.

Django docs on queries.

Complete lookup types here.