As the title suggests, how do I use the "exists clause" in Django's queryset? Survey notes about.
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.
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 ...
So, after investigating various things, there was a way to use the exists clause properly. (But not very smart)
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
raw methodThis 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.
cursor objectDirectly 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.
If you want to write a query that can't be expressed by Django's filter etc., there are the following three methods.
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