【Django】ORMでLEFT JOINのON句の条件を指定したい #Django

前提

以下のような２つのテーブルを用意した

models.py

class Movie(models.Model)
  """動画"""
  title = models.CharField()
  created_at = models.DateTimeField(auto_now_add=True)

  class Meta:
    db_table = 'movie'


class DailyViews(models.Model)
  """日間再生数"""
  movie = models.ForeignKey(Movie)
  views = models.IntegerField()
  created_at = models.DateTimeField(auto_now_add=True)

  class Meta:
    db_table = 'daily_views'

DailyViewsには、日毎に動画が再生された回数を記録する
- その日に初めて再生された動画であればレコードを作成する
- 既に再生されていればviewsを +1 カウントアップして更新する
- 一度も再生されなければ、レコードは作成されない

目的

特定の文言で動画タイトルを検索しつつ、その日の動画再生数の降順と、動画の登録日の降順でデータを取得したい
SQLにすると以下のようなイメージ

SELECT * FROM movie as m
LEFT JOIN daily_views as dv ON m.id = dv.movie_id and dv.created_at = 検索日
WHERE m.title LIKE "%検索したい文言%"
ORDER BY dv.views DESC, m.created_at DESC

表にすると以下のようなイメージ

【Movie】

id	title	created_at
1	猫丸のほのぼの日和	2021-10-01 00:00:00
2	猫瓦三平さんのチャオチュールに物申す	2021-10-02 00:00:00
3	野良猫24時	2021-10-03 00:00:00
4	犬吉のマーキング講座	2021-10-03 00:00:00
5	猫について語りあう	2021-10-03 00:00:00

【DailyViews】

id	movie_id	views	created_at
1	1	1000	2021-10-02 00:00:00
2	1	2000	2021-10-03 00:00:00
3	2	1	2021-10-02 00:00:00
4	3	500	2021-10-03 00:00:00
5	4	100	2021-10-03 00:00:00

検索する文言を猫、検索日を2021/10/3にした場合、以下のような表になってほしい

movie_id	title	movie_created_at	daily_views_id	views	daily_views_created_at
1	猫丸のほのぼの日和	2021-10-01 00:00:00	2	2000	2021-10-03 00:00:00
3	野良猫24時	2021-10-03 00:00:00	4	500	2021-10-03 00:00:00
5	猫について語りあう	2021-10-03 00:00:00	null	null	null
2	猫瓦三平さんのチャオチュールに物申す	2021-10-02 00:00:00	null	null	null

方法

色々検索し、試行錯誤し、prefetch_relatedやselect_relatedを使ってみたが実現できなかった
5日間くらいかけてたどり着いた方法がこれ（公式にあった。。。）

        # 全角スペースを半角スペースに変換
        # 半角スペースで分割
        keywords = keyword_serializer.validated_data.get('keyword').replace('　', ' ').split(' ')
        # 動画一覧取得
        queryset = Movie.object.all()
        # タイトルをキーワードで絞り込む
        for keyword in keywords:
            queryset = queryset.filter(Q(title__icontains=keyword))
        # LEFT JOINを担ってる箇所
        # 結合先.annotate(結合元の別名= FilteredRelation(結合元, condition=Q(ON句に当たる条件))).orderby(-'結合元の別名__フィルド名', '結合先のフィールド名')
        queryset = queryset.annotate(this_day_views=FilteredRelation('dailyviews', condition=Q(dailyviews__created_at='2021-10-03 00:00:00'))).order_by('-this_day_views__views', '-created_at')
        # 重複削除
        queryset = queryset.distinct()

解説

annotate

公式サイト
集計関数を使用する際に使うものと漠然と思っていたが、どうやら違った。
annotateは注釈をつけるという意味。
今回だとthis_day_viewsという注釈（カラム）を結合先に追加する。追加する注釈（カラム）の内容は、=の後の処理次第。それが集計関数を使って算出した値であれ、別テーブルをfilterしたレコードであれ、問題なし。

FilteredRelation

公式サイト
フィルターした結果のテーブルを結合したい時に使用する。
FilteredRelation(relation_name, *, condition=Q())
relation_nameは結合元。LEFT JOIN 結合元にあたる。
condition=Q()はON句の条件。

備考

print(queryset.query)で出力したクエリはこんな感じ

SELECT 省略 FROM `movie`
LEFT OUTER JOIN `daily_views` this_day_views ON (`movie`.`id` = this_day_views.`movie_id` AND (this_day_views.`week` = 2021-10-03 00:00:00))
WHERE (`movie`.`title` LIKE %猫%) ORDER BY this_day_views.`views` DESC, `movie`.`created_at` DESC