What Does Double Underscore ( __ ) Means In Django Model QuerySet Objects Filter Method

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

  1. 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)
    
  2. 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')
  3. 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

  1. gt :  > , greater than.
  2. gte : >= , greater than or equal.
  3. lt : < , less than.
  4. lte : <=, less than or equal.
READ :   How To Add Http Headers In Django Response

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.

READ :   What Is The Difference Between *args and **kwargs In Python

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>]>

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.