{"id":6382,"date":"2023-01-12T03:18:02","date_gmt":"2023-01-12T03:18:02","guid":{"rendered":"https:\/\/www.pythontutorial.net\/?page_id=6382"},"modified":"2023-01-12T04:20:15","modified_gmt":"2023-01-12T04:20:15","slug":"django-orm-in","status":"publish","type":"page","link":"https:\/\/www.pythontutorial.net\/django-tutorial\/django-orm-in\/","title":{"rendered":"Django In"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use Django in<\/code> to check if a value is in a set of values.<\/p>\n\n\n\n

Introduction to the Django In #<\/a><\/h2>\n\n\n\n

We’ll use the Employee<\/code> model in the HR<\/code> application for the demonstration. The Employee<\/code> model maps to the hr_employee<\/code> table in the database:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The SQL IN<\/code> operator returns true if a value is in a set of values:<\/p>\n\n\n

field_name IN (v1, v2, ...)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, you can use the IN<\/code> operator to query the rows from the hr_employee<\/code> table whose department_id<\/code> is in a list like this:<\/p>\n\n\n
SELECT<\/span> *\nFROM<\/span> hr_employee\nWHERE<\/span> department_id IN<\/span> (1<\/span>,2<\/span>,3<\/span>)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In Django, you use the in<\/code> operator:<\/p>\n\n\n
>>> Employee.objects.filter(department_id__in=(1,2,3)) \nSELECT<\/span> \"hr_employee\"<\/span>.\"id\"<\/span>,\n       \"hr_employee\"<\/span>.\"first_name\"<\/span>,\n       \"hr_employee\"<\/span>.\"last_name\"<\/span>,\n       \"hr_employee\"<\/span>.\"contact_id\"<\/span>,\n       \"hr_employee\"<\/span>.\"department_id\"<\/span>\n  FROM<\/span> \"hr_employee\"<\/span>\n WHERE<\/span> \"hr_employee\"<\/span>.\"department_id\"<\/span> IN<\/span> (1<\/span>, 2<\/span>, 3<\/span>)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Typically, you use a subquery with the in<\/code> operator rather than a list of literal values. For example, you find all employees in the Sales<\/code> and Marketing<\/code> departments as follows:<\/p>\n\n\n
>>> departments = Department.objects.filter(Q(name='Sales') | Q(name='Marketing')) \n>>> Employee.objects.filter(department__in=departments)\nSELECT<\/span> \"hr_employee\"<\/span>.\"id\"<\/span>,\n       \"hr_employee\"<\/span>.\"first_name\"<\/span>,\n       \"hr_employee\"<\/span>.\"last_name\"<\/span>,\n       \"hr_employee\"<\/span>.\"contact_id\"<\/span>,\n       \"hr_employee\"<\/span>.\"department_id\"<\/span>\n  FROM<\/span> \"hr_employee\"<\/span>\n WHERE<\/span> \"hr_employee\"<\/span>.\"department_id\"<\/span> IN<\/span> (\n        SELECT<\/span> U0.\"id\"<\/span>\n          FROM<\/span> \"hr_department\"<\/span> U0\n         WHERE<\/span> (U0.\"name\"<\/span> = 'Sales'<\/span> OR<\/span> U0.\"name\"<\/span> = 'Marketing'<\/span>)\n       )<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, get the departments with the names Sales<\/code> or Marketing<\/code>:<\/p>\n\n\n
departments = Department.objects.filter(Q(name='Sales'<\/span>) | Q(name='Marketing'<\/span>))<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, pass the department<\/code> QuerySet<\/code> to the in<\/code> operator:<\/p>\n\n\n
Employee.objects.filter(department__in=departments)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Behind the scenes, Django executes a query with the IN<\/code> operator that matches the department id with a list of department id from a list:<\/p>\n\n\n
SELECT<\/span> \"hr_employee\"<\/span>.\"id\"<\/span>,\n       \"hr_employee\"<\/span>.\"first_name\"<\/span>,\n       \"hr_employee\"<\/span>.\"last_name\"<\/span>,\n       \"hr_employee\"<\/span>.\"contact_id\"<\/span>,\n       \"hr_employee\"<\/span>.\"department_id\"<\/span>\n  FROM<\/span> \"hr_employee\"<\/span>\n WHERE<\/span> \"hr_employee\"<\/span>.\"department_id\"<\/span> IN<\/span> (\n        SELECT<\/span> U0.\"id\"<\/span>\n          FROM<\/span> \"hr_department\"<\/span> U0\n         WHERE<\/span> (U0.\"name\"<\/span> = 'Sales'<\/span> OR<\/span> U0.\"name\"<\/span> = 'Marketing'<\/span>)\n       )<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
NOT IN #<\/a><\/h2>\n\n\n\n
The NOT<\/code> operator negates the IN<\/code> operator. The NOT IN<\/code> operator returns true if a value is not in a list of values:<\/p>\n\n\n
field_name NOT IN (v1, v2, ...)<\/code><\/span><\/pre>\n\n\n
To perform NOT IN<\/code> in Django, you can use the Q<\/code> object and ~<\/code> operator:<\/p>\n\n\n
~Q(field_name__in=(v1,v2,..))<\/code><\/span>Code language:<\/span> Protocol Buffers<\/span> (<\/span>protobuf<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following finds employees whose department id is not 1, 2, or 3:<\/p>\n\n\n
>>> Employee.objects.filter(~Q(department_id__in=(1,2,3))) \r\nSELECT<\/span> \"hr_employee\"<\/span>.\"id\"<\/span>,\r\n       \"hr_employee\"<\/span>.\"first_name\"<\/span>,\r\n       \"hr_employee\"<\/span>.\"last_name\"<\/span>,\r\n       \"hr_employee\"<\/span>.\"contact_id\"<\/span>,\r\n       \"hr_employee\"<\/span>.\"department_id\"<\/span>\r\n  FROM<\/span> \"hr_employee\"<\/span>\r\n WHERE<\/span> NOT<\/span> (\"hr_employee\"<\/span>.\"department_id\"<\/span> IN<\/span> (1<\/span>, 2<\/span>, 3<\/span>))<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Alternatively, you can use the exclude()<\/code> method instead of the filter()<\/code> method:<\/p>\n\n\n
>>> Employee.objects.exclude(department_id__in=(1<\/span>,2<\/span>,3<\/span>))      \r\nSELECT \"hr_employee\"<\/span>.\"id\"<\/span>,\r\n       \"hr_employee\"<\/span>.\"first_name\"<\/span>,\r\n       \"hr_employee\"<\/span>.\"last_name\"<\/span>,\r\n       \"hr_employee\"<\/span>.\"contact_id\"<\/span>,\r\n       \"hr_employee\"<\/span>.\"department_id\"<\/span>\r\n  FROM \"hr_employee\"<\/span>\r\n WHERE NOT (\"hr_employee\"<\/span>.\"department_id\"<\/span> IN (1<\/span>, 2<\/span>, 3<\/span>))<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the Django in<\/code> to check if a value is in a list of values.<\/li>\n<\/ul>\n\n\n\n
    Django ORM<\/th>SQL<\/th><\/tr><\/thead>
    Entity.objects.filter(id__in=(v1,v2,v3)<\/code><\/td>id IN (v1,v2,v3)<\/code><\/td><\/tr>
    Entity.objects.filter(~Q(id__in=(v1,v2,v3))<\/code><\/td>NOT (id IN (v1,v2,v3))<\/code><\/td><\/tr>
    Entity.objects.exclude(id__in=(v1,v2,v3)<\/code><\/td>NOT (id IN (v1,v2,v3))<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t