You have learnd how to use Django model manager’s filter or exclude method to get query result data in article Django Simple CRUD Operation Example. To use filter method is so easy as below.
Department.objects.filter(dept_desc__contains='dev2qa') <QuerySet [<Department: Development,Develop dev2qa.com website use Django>]>
We can see that the filter function’s argument is dept_desc__contains=’dev2qa’, i think this is not difficult to undersatnd, it means the result set data’s dept_desc table column value should contains string ‘dev2qa’.
1. Field Lookups.
In Django, above argument is called field lookups argument, the field lookups argument’s format should be fieldname__lookuptype=value. Please note the double underscore ( __ ) between the field name(depe_desc) and lookup type keyword contains. When Django execute the filter method with above field lookup argument, it will translate it to below sql statements. So use field lookup argument, you do not need to care about low level sql command at all, you just need to know the field lookup type name and it’s meaning.
Department.objects.filter(dept_desc__contains='dev2qa') will be translated to below sql. The table name format is django-app-name_model-name (dept_emp_department). SELECT "dept_emp_department"."id", "dept_emp_department"."dept_name", "dept_emp_department"."dept_desc" FROM "dept_emp_department" WHERE "dept_emp_department"."dept_desc" LIKE %dev2qa% ESCAPE '\'
2. Built-in Field Lookup Types.
The keyword after the double underscore ( __ ) in field lookup argument is so called lookup types, there are a lot of built-in lookup types that you can use, of cause you can define custom lookup types when you need, we will tell you how to create custom lookup types in other article. Now we will first demo built-in lookup types examples.
2.1 exact
Get the result match the condition exactly.
dept = Department.objects.filter(dept_name__exact='Develop') is same as dept = Department.objects.filter(dept_name='Develop') will be translated to below sql SELECT "dept_emp_department"."id", "dept_emp_department"."dept_name", "dept_emp_department"."dept_desc" FROM "dept_emp_department" WHERE "dept_emp_department"."dept_name" = 'Develop' dept = Department.objects.filter(dept_name__exact=None) will be translated to SELECT "dept_emp_department"."id", "dept_emp_department"."dept_name", "dept_emp_department"."dept_desc" FROM "dept_emp_department" WHERE "dept_emp_department"."dept_name" IS NULL
2.2 iexact
Exact match case insensitively, that means exact match ignore the charactor upper or lower case.
dept = Department.objects.filter(dept_name__iexact='Develop') will return result that dept_name column value like 'Develop', 'DEVELOP', 'DeVelop' etc. dept = Department.objects.filter(dept_name_iexact=None) return record which dept_name is null.
2.3 contains
Return result contains value case sensitively.
dept = Department.objects.filter(dept_name__contains='Develop') will not return 'develoPment', only return 'Development'
2.4 icontains
Return result data that case insensitive contains column value.
dept = Department.objects.filter(dept_name__icontains='Develop') will return 'Development', 'develop', 'DevElop' etc
2.5 in
- In a list or tuple.
Department.objects.filter(id__in=[1,3,5]) translate to sql SELECT "dept_emp_department"."id", "dept_emp_department"."dept_name", "dept_emp_department"."dept_desc" FROM "dept_emp_department" WHERE "dept_emp_department"."id" IN (1, 3, 5)
- In character list (string).
Department.objects.filter(dept_name__in='Dev') translate to sql SELECT "dept_emp_department"."id", "dept_emp_department"."dept_name", "dept_emp_department"."dept_desc" FROM "dept_emp_department" WHERE "dept_emp_department"."dept_name" IN ('D', 'e', 'v') - In other query set.
dept_qs = Department.objects.filter(dept_name__exact='Develop') emp_qs = Employee.objects.filter(dept__in=dept_qs)
Below is the example from article Django Bootstrap3 Example
from dept_emp.models import Department, Employee dept_qs = Department.objects.filter(dept_name__exact='Develop') dept_qs <QuerySet [<Department: Develop,Develop web site use Python Django>]> emp_qs = Employee.objects.filter(dept__in=dept_qs) emp_qs <QuerySet [<Employee: jack,100000,13901234567>, <Employee: jerry,80000,1369090909>, <Employee: tom,10000,13901234568>]>
2.6 gt, gte, lt, lte
- gt : > , greater than.
- gte : >= , greater than or equal.
- lt : < , less than.
- lte : <=, less than or equal.
2.7 startswith
Get record startswith string case-sensitive.
>>> Department.objects.filter(dept_name__startswith='D') <QuerySet [<Department: Develop,Develop web site use Python Django>]> translate to sql SELECT "dept_emp_department"."id", "dept_emp_department"."dept_name", "dept_emp_department"."dept_desc" FROM "dept_emp_department" WHERE "dept_emp_department"."dept_name" LIKE 'D%' ESCAPE '\'
2.8 istartswith
Case-insensitive starts with.
>>> Department.objects.filter(dept_name__istartswith='D') <QuerySet [<Department: Develop,Develop web site use Python Django>, <Department: defence,defence department>]>
2.9 endswith
Field value ends with.
>>> Department.objects.filter(dept_name__endswith='p') <QuerySet [<Department: Develop,Develop web site use Python Django>]> translate to sql SELECT "dept_emp_department"."id", "dept_emp_department"."dept_name", "dept_emp_department"."dept_desc" FROM "dept_emp_department" WHERE "dept_emp_department"."dept_name" LIKE '%p' ESCAPE '\'
2.10 iendswith
Similar with endswith, but case-insensitive.
>>> Department.objects.filter(dept_name__iendswith='e') <QuerySet [<Department: Quality Assurance,Responsible for company website quality and test.>, <Department: defence,defence department>]>
2.11 range
Range between two values.
>>> import datetime >>> from dept_emp.models import Employee >>> start_date = datetime.date(2018,1,1) >>> end_date = datetime.date(2020,1,1) >>> Employee.objects.filter(emp_onboard_date__range=(start_date, end_date)) <QuerySet [<Employee: jack,100000,13901234567>, <Employee: jerry,80000,1369090909>, <Employee: richard,100000,1369090908>, <Employee: jack,100000,13690909099>, <Employee: tom,10000,13901234568>]> translate to sql SELECT "dept_emp_employee"."id", "dept_emp_employee"."user_id", "dept_emp_employee"."emp_mobile", "dept_emp_employee"."emp_salary", "dept_emp_employee"."emp_onboard_date", "dept_emp_employee"."sex" FROM "dept_emp_employee" WHERE "dept_emp_employee"."emp_onboard_date" BETWEEN '2018-01-01 00:00:00' AND '2020-01-01 00:00:00'
2.12 date
This lookup type can convert your model class’s models.DateTimeField field valut to datetime.date object.
>>> import datetime >>> from dept_emp.models import Employee >>> compare_date = datetime.date(2018,1,1) >>> Employee.objects.filter(emp_onboard_date__date=compare_date) <QuerySet []>
You can also add other field lookup type after the date lookup type, separated by double underscore ( __ ). Below example chain two field lookup type, __date and __gt
>>> Employee.objects.filter(emp_onboard_date__date__gt=compare_date) <QuerySet [<Employee: jack,100000,13901234567>, <Employee: jerry,80000,1369090909>, <Employee: richard,100000,1369090908>, <Employee: jack,100000,13690909099>, <Employee: tom,10000,13901234568>]>
2.13 year
Similar with date, compare an exact year match for Django date or datetime field.
>>> Employee.objects.filter(emp_onboard_date__year=2019) <QuerySet [<Employee: jack,100000,13901234567>, <Employee: jerry,80000,1369090909>, <Employee: richard,100000,1369090908>, <Employee: jack,100000,13690909099>, <Employee: tom,10000,13901234568>]>
You can chain multiple lookup type separated by double underscore ( __ )
>>> Employee.objects.filter(emp_onboard_date__year__gt=2019) <QuerySet []>
2.14 month
Compare month match.
>>> Employee.objects.filter(emp_onboard_date__month=2) <QuerySet [<Employee: jack,100000,13901234567>, <Employee: jerry,80000,1369090909>, <Employee: richard,100000,1369090908>, <Employee: jack,100000,13690909099>, <Employee: tom,10000,13901234568>]> # below command chain month and gt lookup types. >>> Employee.objects.filter(emp_onboard_date__month__gt=2) <QuerySet []>
2.15 day
Match date or datetime model field value excatly.
>>> Employee.objects.filter(emp_onboard_date__day=21) <QuerySet []>
Can chain multiple lookup types with day.
>>> Employee.objects.filter(emp_onboard_date__day__gt=21) <QuerySet [<Employee: jack,100000,13901234567>, <Employee: jerry,80000,1369090909>, <Employee: richard,100000,1369090908>, <Employee: jack,100000,13690909099>, <Employee: tom,10000,13901234568>]>
2.16 week
Return the date or datetime field value’s week number to compare.
>>> Employee.objects.filter(emp_onboard_date__week=8) <QuerySet [<Employee: jack,100000,13901234567>, <Employee: jerry,80000,1369090909>, <Employee: richard,100000,1369090908>, <Employee: jack,100000,13690909099>, <Employee: tom,10000,13901234568>]>
For more lookup field types, please visit the django official field lookups website.
3. User Double Underscore ( __ ) To Reference Foreign Key Model Class’s Attribute.
Suppose Employee model class has a foreign key variable user, user is an instance of django.contrib.auth.models.User class. User class has a username attribute.
class Employee(models.Model):
......
user = models.ForeignKey(User, on_delete=models.CASCADE,)
......
So you can use double underscore to separate the foreign key user and it’s attribute username in the filter method.
>>> Employee.objects.filter(user__username = 'tom') <QuerySet [<Employee: tom,10000,13901234568>]>