Go to Qiita Advent Calendar Top
LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@shun198

DjangoのOuterRefとSubqueryを使ってサブクエリを実行しよう！

Posted at

概要

サブクエリを使ったデータの取得をDjangoのORMで実現するにはDjangoのOuterRefとSubqueryを使って実現できるのでその方法について解説していきたいと思います

前提

  • Djangoのプロジェクトを作成済み
  • 今回は著者ごとの最新の本のタイトルを取得するクエリを作成します

modelの作成

  • Author
  • Book

のModelを作成します

models.py
import uuid

from django.contrib.auth.models import AbstractUser
from django.contrib.auth.validators import UnicodeUsernameValidator
from django.core.validators import RegexValidator
from django.db import models


class User(AbstractUser):
    """システムユーザ"""

    username_validator = UnicodeUsernameValidator()

    class Role(models.IntegerChoices):
        """システムユーザのロール

        Args:
            MANAGEMENT(0): 管理者
            GENERAL(1):    一般
            PART_TIME(2):  アルバイト
        """

        MANAGEMENT = 0
        GENERAL = 1
        PART_TIME = 2

    # 不要なフィールドはNoneにすることができる
    first_name = None
    last_name = None
    date_joined = None
    groups = None
    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False,
        db_comment="システムユーザID",
    )
    employee_number = models.CharField(
        unique=True,
        validators=[RegexValidator(r"^[0-9]{8}$")],
        max_length=8,
        # 管理者のログイン画面で社員番号と表示される
        verbose_name="社員番号",
        db_comment="社員番号",
    )
    username = models.CharField(
        max_length=150,
        unique=True,
        validators=[username_validator],
        db_comment="ユーザ名",
    )
    email = models.EmailField(
        max_length=254,
        unique=True,
        db_comment="メールアドレス",
    )
    role = models.PositiveIntegerField(
        choices=Role.choices,
        default=Role.PART_TIME,
        db_comment="システムユーザのロール",
    )
    created_at = models.DateTimeField(
        auto_now_add=True,
        db_comment="作成日",
    )
    updated_at = models.DateTimeField(
        auto_now=True,
        db_comment="更新日",
    )

    USERNAME_FIELD = "employee_number"
    REQUIRED_FIELDS = ["email", "username"]

    class Meta:
        ordering = ["employee_number"]
        db_table = "User"
        db_table_comment = "システムユーザ"

    def __str__(self):
        return self.username


class Author(models.Model):
    """作者"""

    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False,
        db_comment="お客様ID",
    )
    name = models.CharField(max_length=255)
    created_at = models.DateTimeField(
        auto_now_add=True,
        db_comment="作成日時",
    )
    created_by = models.ForeignKey(
        User,
        null=True,
        on_delete=models.SET_NULL,
        related_name="%(class)s_created_by",
        db_comment="作成者",
    )

    class Meta:
        db_table = "Author"
        db_table_comment = "作者"


class Book(models.Model):
    """本"""

    id = models.UUIDField(
        primary_key=True,
        default=uuid.uuid4,
        editable=False,
        db_comment="お客様ID",
    )
    title = models.CharField(max_length=255)
    author = models.ForeignKey(
        Author,
        null=True,
        on_delete=models.CASCADE,
        related_name="%(class)s_author",
        db_comment="作者",
    )
    publication_date = models.DateField()
    created_at = models.DateTimeField(
        auto_now_add=True,
        db_comment="作成日時",
    )
    created_by = models.ForeignKey(
        User,
        null=True,
        on_delete=models.SET_NULL,
        related_name="%(class)s_created_by",
        db_comment="作成者",
    )

    class Meta:
        db_table = "Book"
        db_table_comment = "本"

fixtureの作成

  • Author
  • Book

のテストデータを作成します

fixtures.json
[
    {
        "model": "application.User",
        "pk": 1,
        "fields": {
            "employee_number": "00000001",
            "username": "test01",
            "password": "pbkdf2_sha256$390000$KF4YHJxvWjSODaXdxLBg6S$U5XDh8mR77kMMUtlRcBZS/bkaxdpjNR/P4zyy25g3/I=",
            "email": "test01@example.com",
            "role": 0,
            "is_superuser": 0,
            "created_at": "2022-07-28T00:31:09.732Z",
            "updated_at": "2022-07-28T00:31:09.732Z"
        }
    },
    {
        "model": "application.User",
        "pk": 2,
        "fields": {
            "employee_number": "00000002",
            "username": "test02",
            "password": "pbkdf2_sha256$390000$KF4YHJxvWjSODaXdxLBg6S$U5XDh8mR77kMMUtlRcBZS/bkaxdpjNR/P4zyy25g3/I=",
            "email": "test02@example.com",
            "role": 1,
            "is_superuser": 0,
            "created_at": "2022-07-28T00:31:09.732Z",
            "updated_at": "2022-07-28T00:31:09.732Z"
        }
    },
    {
        "model": "application.User",
        "pk": 3,
        "fields": {
            "employee_number": "00000003",
            "username": "test03",
            "password": "pbkdf2_sha256$390000$KF4YHJxvWjSODaXdxLBg6S$U5XDh8mR77kMMUtlRcBZS/bkaxdpjNR/P4zyy25g3/I=",
            "email": "test03@example.com",
            "role": 2,
            "is_superuser": 0,
            "created_at": "2022-07-28T00:31:09.732Z",
            "updated_at": "2022-07-28T00:31:09.732Z"
        }
    },
    {
        "model": "application.Author",
        "pk": 1,
        "fields": {
            "name": "J.K. Rowling",
            "created_at": "2022-01-01T00:00:00.000Z",
            "created_by": 1
        }
    },
    {
        "model": "application.Author",
        "pk": 2,
        "fields": {
            "name": "George R.R. Martin",
            "created_at": "2022-02-01T00:00:00.000Z",
            "created_by": 2
        }
    },
    {
        "model": "application.Author",
        "pk": 3,
        "fields": {
            "name": "Agatha Christie",
            "created_at": "2022-03-01T00:00:00.000Z",
            "created_by": 3
        }
    },
    {
        "model": "application.Book",
        "pk": 1,
        "fields": {
            "title": "Harry Potter and the Philosopher's Stone",
            "author": 1,
            "publication_date": "1997-06-26",
            "created_at": "2022-01-01T00:00:00.000Z",
            "created_by": 1
        }
    },
    {
        "model": "application.Book",
        "pk": 2,
        "fields": {
            "title": "Harry Potter and the Chamber of Secrets",
            "author": 1,
            "publication_date": "1998-07-02",
            "created_at": "2022-01-01T00:00:00.000Z",
            "created_by": 1
        }
    },
    {
        "model": "application.Book",
        "pk": 3,
        "fields": {
            "title": "Harry Potter and the Prisoner of Azkaban",
            "author": 1,
            "publication_date": "1999-07-08",
            "created_at": "2022-01-01T00:00:00.000Z",
            "created_by": 1
        }
    },
    {
        "model": "application.Book",
        "pk": 4,
        "fields": {
            "title": "A Game of Thrones",
            "author": 2,
            "publication_date": "1996-08-06",
            "created_at": "2022-02-01T00:00:00.000Z",
            "created_by": 2
        }
    },
    {
        "model": "application.Book",
        "pk": 5,
        "fields": {
            "title": "A Clash of Kings",
            "author": 2,
            "publication_date": "1998-11-16",
            "created_at": "2022-02-01T00:00:00.000Z",
            "created_by": 2
        }
    },
    {
        "model": "application.Book",
        "pk": 6,
        "fields": {
            "title": "A Storm of Swords",
            "author": 2,
            "publication_date": "2000-08-08",
            "created_at": "2022-02-01T00:00:00.000Z",
            "created_by": 2
        }
    },
    {
        "model": "application.Book",
        "pk": 7,
        "fields": {
            "title": "Murder on the Orient Express",
            "author": 3,
            "publication_date": "1934-01-01",
            "created_at": "2022-03-01T00:00:00.000Z",
            "created_by": 3
        }
    },
    {
        "model": "application.Book",
        "pk": 8,
        "fields": {
            "title": "The ABC Murders",
            "author": 3,
            "publication_date": "1936-01-06",
            "created_at": "2022-03-01T00:00:00.000Z",
            "created_by": 3
        }
    },
    {
        "model": "application.Book",
        "pk": 9,
        "fields": {
            "title": "Death on the Nile",
            "author": 3,
            "publication_date": "1937-11-01",
            "created_at": "2022-03-01T00:00:00.000Z",
            "created_by": 3
        }
    }
]

シェルの実行

まず、DjangoのModelとOuterRef、Subqueryをimportします

>>> from django.db.models import OuterRef, Subquery
>>> from application.models import Author, Book

特定の著書が執筆した本をpublication_dateの降順で取得するサブクエリを作成します
サブクエリを作成する際はOuterRefを使用して絞り込みを行います
今回はAuthorのpkを使用します

>>> latest_book = Book.objects.filter(author=OuterRef('pk')).order_by('-publication_date')

その後、annotateを使ってauthors_with_latest_bookのfieldを作成し、latest_bookのクエリセット内から最初の要素(最新の著書)を1つだけ取得します

>>> authors_with_latest_book = Author.objects.annotate(latest_book_title=Subquery(latest_book.values('title')[:1]))

以下のように著者ごとの最新の本を取得できれば成功です

>>> for author in authors_with_latest_book:
...     print(f"{author.name}: {author.latest_book_title}")
...
SELECT "Author"."id",
       "Author"."name",
       "Author"."created_at",
       "Author"."created_by_id",

  (SELECT U0."title"
   FROM "Book" U0
   WHERE U0."author_id" = ("Author"."id")
   ORDER BY U0."publication_date" DESC
   LIMIT 1) AS "latest_book_title"
FROM "Author" [19.69ms]
J.K. Rowling: Harry Potter and the Prisoner of Azkaban
George R.R. Martin: A Storm of Swords
Agatha Christie: Death on the Nile

参考

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?