Chain query of data and methods for restricting data return in Django framework

Chain query

Usually we need to perform filtering and sorting queries at the same time. So you can simply write this "chain" form:

>>> (country=".").order_by("-name")
[<Publisher: O'Reilly>, <Publisher: Apress>]

You should be right, converting it into SQL query is a combination of WHERE and ORDER BY:

SELECT id, name, address, city, state_province, country, website
FROM books_publisher
WHERE country = ''
ORDER BY name DESC;

Limit the returned data

Another commonly used requirement is to take out a fixed number of records. Imagine you have thousands of publishers in your database, but you just want to show the first one. You can use standard Python list cropping statements:

>>> .order_by('name')[0]
<Publisher: Apress>

This is equivalent to:

SELECT id, name, address, city, state_province, country, website
FROM books_publisher
ORDER BY name
LIMIT 1;

Similarly, you can use Python's range-slicing syntax to fetch a specific subset of the data:

>>> .order_by('name')[0:2]

This example returns two objects, equivalent to the following SQL statement:

SELECT id, name, address, city, state_province, country, website
FROM books_publisher
ORDER BY name
OFFSET 0 LIMIT 2;

Note that negative indexing in Python is not supported:

>>> .order_by('name')[-1]
Traceback (most recent call last):
 ...
AssertionError: Negative indexing is not supported.

Although negative indexing is not supported, we can use other methods. For example, slightly modify the order_by() statement to implement it:

>>> .order_by('-name')[0]