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

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

```python: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にすると以下のようなイメージ

```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日間くらいかけてたどり着いた方法がこれ（公式にあった。。。）

```python
        # 全角スペースを半角スペースに変換
        # 半角スペースで分割
        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
- [公式サイト](https://docs.djangoproject.com/en/3.2/ref/models/querysets/#annotate)
- 集計関数を使用する際に使うものと漠然と思っていたが、どうやら違った。
- `annotate`は`注釈をつける`という意味。
- 今回だと`this_day_views`という注釈（カラム）を結合先に追加する。追加する注釈（カラム）の内容は、`=`の後の処理次第。それが集計関数を使って算出した値であれ、別テーブルをfilterしたレコードであれ、問題なし。

### FilteredRelation
- [公式サイト](https://docs.djangoproject.com/en/3.2/ref/models/querysets/#filteredrelation-objects)
- フィルターした結果のテーブルを結合したい時に使用する。
- `FilteredRelation(relation_name, *, condition=Q())`
- `relation_name`は結合元。`LEFT JOIN 結合元`にあたる。
- `condition=Q()`はON句の条件。


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

```sql
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
```