前提
- 以下のような２つのテーブルを用意した
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