A group of data from a database is called a query set.
A list of items is the foundation of a query set.
By enabling you to select and arrange the data early on, QuerySets makes it simpler to obtain the information you truly require.
We will be using the Member table to query for data in this tutorial.
| id | firstname | lastname | phone | joined_date |
| 1 | Emil | Refsnes | 5551234 | 2022-01-05 |
| 2 | Tobias | Refsnes | 5557777 | 2022-04-01 |
| 3 | Linus | Refsnes | 5554321 | 2021-12-24 |
| 4 | Lene | Refsnes | 5551234 | 2021-05-01 |
| 5 | Stalikken | Refsnes | 5559876 | 2022-09-29 |
Data Queries
There is a testing view in views.py where we will test all kinds of queries.
The .all() function is used in the example below to retrieve every record and field in the Member model:
View
View .py :
<pre class="language-python"><code>from django.http import HttpResponse
from django.template import loader
from .models import Member
def testing(request):
mydata = Member.objects.all()
template = loader.get_template('template.html')
context = {
'mymembers': mydata,
}
return HttpResponse(template.render(context, request))</code></pre>The object is sent to the template via the context object as mymembers and is stored in a variable named mydata. It appears as follows:
<QuerySet [
<Member: Member object (1)>,
<Member: Member object (2)>,
<Member: Member object (3)>,
<Member: Member object (4)>,
<Member: Member object (5)>
]>As you can see, there are five records in our Member model, which are represented as five objects in the QuerySet.
The mymembers object in the template can be used to create content:
Template
templates/template.html :
<table border='1'>
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
</tr>
{% for x in mymembers %}
<tr>
<td>{{ x.id }}</td>
<td>{{ x.firstname }}</td>
<td>{{ x.lastname }}</td>
</tr>
{% endfor %}
</table>QuerySet Data Access
Data from a model can be obtained into a QuerySet using a variety of techniques.
The methods for values()
Each object can be returned as a Python dictionary with names and values represented as key/value pairs using the values() method:
Example
view . py:
from django.http import HttpResponse
from django.template import loader
from .models import Member
def testing(request):
mydata = Member.objects.all().values()
template = loader.get_template('template.html')
context = {
'mymembers': mydata,
}
return HttpResponse(template.render(context, request))Retrieving Specific Columns
You can only return the columns you specify using the values_list() method.
view . py:
from django.http import HttpResponse
from django.template import loader
from .models import Member
def testing(request):
mydata = Member.objects.values_list('firstname')
template = loader.get_template('template.html')
context = {
'mymembers': mydata,
}
return HttpResponse(template.render(context, request))Retrieve Particular Rows
The filter() method allows you to limit the search to only return particular rows or records.
Example
Only return the records with “Emil” as the first name.
view . py:
from django.http import HttpResponse
from django.template import loader
from .models import Member
def testing(request):
mydata = Member.objects.filter(firstname='Emil').values()
template = loader.get_template('template.html')
context = {
'mymembers': mydata,
}
return HttpResponse(template.render(context, request))QuerySet Filtering
You can limit your search to just retrieve rows that include the search word by using the filter() method.
Filtering on field names like this is possible, as we discovered in the previous chapter:
Example
Only the records with “Emil” as the first name should be returned:
mydata = Member.objects.filter(firstname='Emil').value()The statement above would be expressed as follows in SQL
SELECT * FROM members WHERE firstname = 'Emil';AND
You can filter on several fields by separating them with a comma because the filter() method accepts the parameters as **kwargs (keyword arguments).
Example
Return entries with the last name “Refsnes” and the ID 2:
mydata = Member.objects.filter(lastname='Refsnes', id=2).values()The statement above would be expressed as follows in SQL
SELECT * FROM members WHERE lastname = 'Refsnes' AND id = 2;OR
It is more difficult to return results where the first name is Emil or Tobias (i.e., records that match either query, but not necessarily both) than it is in the AND example above.
Multiple filter() methods are available, separated by the pipe | character. The outcomes will be combined into a single model.
Example
Return entries with the first names “Emil” or “Tobias”:
mydata = Member.objects.filter(firstname='Emil').values() | Member.objects.filter(firstname='Tobias').values()Using Q expressions and importing them is another popular technique:
Example
Return entries with the first names “Emil” or “Tobias”:
from django.http import HttpResponse
from django.template import loader
from .models import Member
from django.db.models import Q
def testing(request):
mydata = Member.objects.filter(Q(firstname='Emil') | Q(firstname='Tobias')).values()
template = loader.get_template('template.html')
context = {
'mymembers': mydata,
}
return HttpResponse(template.render(context, request))The statement above would be expressed as follows in SQL
SELECT * FROM members WHERE firstname = 'Emil' OR firstname = 'Tobias';Database Field Lookups
Django specifies WHERE clauses and SQL statements in a unique way.
Use “Field lookups” in Django to create specific where clauses.
Specific SQL keywords are represented by field lookups.
Example
Make use of the keyword __startswith
.filter(firstname__startswith='L');The SQL statement is identical to this:
WHERE firstname LIKE 'L%'Records whose first name begins with “L” will be returned by the aforementioned statement.
Syntax Guide: Field Lookups
The fieldname must be followed by two (!) underscore characters and the keyword for all field lookups.
The statement would look like this in our Member model:
Example
Provide the entries where the initial letter “L” appears in the first name:
mydata = Member.objects.filter(firstname__startswith='L').values()Field Lookup Syntax Reference
All of the field lookup keywords are listed here:
| Keyword | Description |
|---|---|
| contains | Contains the phrase |
| icontains | Same as contains, but case-insensitive |
| date | Matches a date |
| day | Matches a date (day of month, 1-31) (for dates) |
| endswith | Ends with |
| iendswith | Same as endswidth, but case-insensitive |
| exact | An exact match |
| iexact | Same as exact, but case-insensitive |
| in | Matches one of the values |
| isnull | Matches NULL values |
| gt | Greater than |
| gte | Greater than, or equal to |
| hour | Matches an hour (for datetimes) |
| lt | Less than |
| lte | Less than, or equal to |
| minute | Matches a minute (for datetimes) |
| month | Matches a month (for dates) |
| quarter | Matches a quarter of the year (1-4) (for dates) |
| range | Match between |
| regex | Matches a regular expression |
| iregex | Same as regex, but case-insensitive |
| second | Matches a second (for datetimes) |
| startswith | Starts with |
| istartswith | Same as startswith, but case-insensitive |
| time | Matches a time (for datetimes) |
| week | Matches a week number (1-53) (for dates) |
| week_day | Matches a day of week (1-7) 1 is sunday |
| iso_week_day | Matches a ISO 8601 day of week (1-7) 1 is monday |
| year | Matches a year (for dates) |
| iso_year | Matches an ISO 8601 year (for dates) |
Ordering Data with Django QuerySets
The order_by() method in Django is used to sort QuerySets:
Example
Sort the results by first name in alphabetical order:
mydata = Member.objects.all().order_by('firstname').values()The statement above would be expressed as follows in SQL:
SELECT * FROM members ORDER BY firstname;Sorting in Descending Order
To change the sorting orientation to descending (highest value first), add the minus sign (NOT), – before the field name. By default, the results are sorted ascending (lowest value first).
Example
Sort the outcome by first name, descending:
mydata = Member.objects.all().order_by('-firstname').values()The statement above would be expressed as follows in SQL:
SELECT * FROM members ORDER BY firstname DESC;Specifying Multiple Sort Orders
Use a comma to separate the fieldnames in the order_by() method if you want to order by multiple fields:
Example
Sort the outcome by last name, descending:
mydata = Member.objects.all().order_by('lastname', '-id').values()The statement above would be expressed as follows in SQL:
SELECT * FROM members ORDER BY lastname ASC, id DESC;