[PYTHON] How to use the exists clause in Django's queryset

Introduction

As the title suggests, how do I use the "exists clause" in Django's queryset? Survey notes about.

Query set conditions in Django

When issuing a query in Django, specify a condition as a parameter in the Queryset object.

models.py


from django.db import models

class Author(models.Model):
    class Meta :
        db_table = 'AUTHOR'
    name     = models.CharField('Author name', max_length=100)
    birthday = models.DateField('Birthday')

class Book(models.Model):
    class Meta :
        db_table = 'BOOK'
    name   = models.CharField('Book title', max_length=100)
    price  = models.IntegerField('price')
    author = models.ForeignKey(Author, verbose_name='Author')

For example, for a model like ↑

Book.objects.filter(name__contains='Django', price__lte=3000)

In the case of the query set of ↑, it means that the conditions of "the book name contains the character string'Django'" and "the price is 3000 or less" are specified, and the query actually issued looks like ↓. become.

SELECT ... FROM BOOK WHERE NAME LIKE '%Django%' AND PRICE <= 3000

These conditions specify the conditions directly for the fields in the Book model, but if it is a ForeignKey field, you can follow the relation and write the conditions for the field to which you want to relate.

Book.objects.filter(author__name__contains='xxx')

In the case of ↑, the column with FK is used as the join condition, and the query is as shown in ↓.

SELECT ... FROM BOOK INNER JOIN AUTHOR ON (BOOK.AUTHOR_ID = AUTHOR.ID)
WHERE AUTHOR.NAME LIKE '%xxx%'

Note that the table join is automatically generated on the Django side by tracing the relation from the FK field, and you cannot perform the table join by yourself using the field without FK as the join condition.

exists clause syntax

The EXISTS clause in SQL is used in the Where clause as follows.

SELECT ... FROM AUTHOR
WHERE EXISTS(
  SELECT 1 FROM BOOK
  WHERE BOOK.AUTHOR_ID = AUTHOR.ID
  AND BOOK.PRICE >= 5000
)

In this example, the SQL is "Search for authors who have published books with a price of 5000 or more". If you try to express this in a Django query set, you can write something like ↓ using the destination field.

Author.objects.filter(book__price__gte=5000)

However, in this case, "authors who have published multiple books with prices of 5000 or more" will be hit by the number of books. To avoid duplication, use the distinct method,

Author.objects.filter(book__price__gte=5000).distinct()

That's fine, but performance is wasted, and above all, it's not cool. This kind of condition "if ◯◯ exists" is smart if you can use the exists clause ...

solution

So, after investigating various things, there was a way to use the exists clause properly. (But not very smart)

(1) Extend the query "a little" using the ʻextra` method

The extra method was mentioned in Django's official documentation. You can use this method to extend some of your queries, such as select, from, and where clauses.

This time we will add a condition to the where clause, so write as follows

Author.objects.extra(where=['exists(select 1 from BOOK where BOOK.author_id=AUTHOR.id and BOOK.price >= 5000)'])

The SQL issued with the contents of ↑ is as shown in ↓.

SELECT "AUTHOR"."id", "AUTHOR"."name", "AUTHOR"."birthday" FROM "AUTHOR" 
WHERE (exists(select 1 from BOOK where BOOK.author_id=AUTHOR.id and BOOK.price >= 5000))

The character string specified by extra is added as it is as a condition of the where clause. In addition, the where parameter added by extra is a list type, and if multiple conditions are specified, it will be automatically combined by AND.

By the way, it is also possible to use the from clause and where clause together to join tables without FK. Wide range of applications. Still, since the return value is a Queryset object, you can connect the method chain as it is. This is convenient.

Author.objects.extra(where=['exists(...abridgement...)']).order_by('birthday')

↓ It looks like this

SELECT "AUTHOR"."id", "AUTHOR"."name", "AUTHOR"."birthday" FROM "AUTHOR" 
WHERE (exists(select 1 from BOOK where BOOK.author_id=AUTHOR.id and BOOK.price >= 5000))
ORDER BY "AUTHOR"."birthday" ASC

(2) Execute plain SQL using the raw method

How to use raw method

This is a pattern of anything. As long as you can match the contents of the Select clause with the model field to be searched, you can write all the SQL by yourself.

If it is the condition of the example, write as ↓.

>>> Author.objects.raw('''select id,name,birthday from AUTHOR 
...                       where exists(
...                          select 1 from BOOK 
...                          where BOOK.author_id=AUTHOR.id 
...                          and BOOK.price >= 5000)''')
...
<RawQuerySet: 'select id,name,birthday from AUTHOR where exists(select 1 from BOOK where BOOK.author_id=AUTHOR.id and BOOK.price >= 5000)'>

Since the return of the raw method is a RawQuerySet object, the method chain of Queryset cannot be connected. Well, I wrote all the SQL myself, so I don't have to write it in the method chain.

(3) Execute custom SQL directly using the cursor object

Directly execute custom SQL (https://docs.djangoproject.com/en/1.7/topics/db/sql/#executing-custom-sql-directly)

This is a pattern that is really anything. It is not something that the exists clause is used at the level of anything, but it is used when you want to issue DDL or DML directly. Or is it about issuing a query that does not have a corresponding model class (such as calling VIEW or FUNCTION)?

If you dare to execute the example query, you can write like ↓

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute('select id,name,birthday from AUTHOR where exists(...abridgement...)')
>>> cursor.fetchall()
[(1,'xxx',datetime.date(yyyy,mm,dd)), (2,'xxx',datetime.date(yyyy,mm,dd)),,,]

The result is a list of tuples. It is not a dict, so be careful when fetching via cursor.

Summary

If you want to write a query that can't be expressed by Django's filter etc., there are the following three methods.

  1. extra method (extend "select, from, where clause" a little ")
  2. raw method (write all the queries you want to execute by yourself)
  3. cursor object (direct execution of DML, DDL, etc., or issuing a query for which the corresponding model does not exist)

For the theme of this time, "Write an exists clause", the extra method is sufficient. If you want to build a complex query, you may need to use the raw method. I don't think there is a chance to use the cursor object for making an application normally. It may come into play when creating APIs or developing apps that generate complex data flows. (I used it when I made a command to generate table comments in Django)

For the time being, there was also a way to execute complex SQL. Yes.

Recommended Posts

How to use the exists clause in Django's queryset
How to use the generator
How to use Django's GeoIp2
How to use the decorator
How to use the model learned in Lobe in Python
How to use the zip function
How to use the optparse module
How to use classes in Theano
How to use SQLite in Python
How to use Mysql in python
How to use ChemSpider in Python
How to use PubChem in Python
How to use the ConfigParser module
[python] How to check if the Key exists in the dictionary
How to use the __call__ method in a Python class
How to set the html class attribute in Django's forms.py
Notes on how to use marshmallow in the schema library
[Introduction to Python] How to use class in Python?
How to use the Spark ML pipeline
How to use Google Test in C
[Linux] How to use the echo command
How to use the Linux grep command
How to use the asterisk (*) in Python. Maybe this is all? ..
[Introduction to Python] How to use the in operator in a for statement?
How to use Anaconda interpreter in PyCharm
How to use __slots__ in Python class
How to use the IPython debugger (ipdb)
How to use regular expressions in Python
How to use Map in Android ViewPager
How to use the render function defined in .mako (.html) directly in mako
How to use is and == in Python
How to view images in Django's Admin
How to use MkDocs for the first time
Use the LIKE clause in golang x SQLite3
How to use Python Image Library in python3 series
How to use the graph drawing library Bokeh
Summary of how to use MNIST in Python
How to use the Google Cloud Translation API
How to use the NHK program guide API
[Algorithm x Python] How to use the list
How to get the files in the [Python] folder
How to use tkinter with python in pyenv
Use pygogo to get the log in json.
How to use xml.etree.ElementTree
How to use Python-shell
How to use tf.data
How to use virtualenv
How to use Seaboan
How to use image-match
How to use shogun
How to use Pandas 2
How to use Virtualenv
How to use numpy.vectorize
How to use partial
How to use Bio.Phylo
How to use SymPy
How to use WikiExtractor.py
How to use IPython
How to use virtualenv
How to use Matplotlib
How to use iptables