Djangoでpostgresのdistinct onを実行する
postgresにはdistinct on という構文があるそうです。知らなかったです。
djangoのドキュメントで知りました。
postgres distinct on でググるとこちらのエントリが一番上にきます。
PostgreSQL の DISTINCT ON
http://www.odin.hyork.net/write/write0228.html
要するに、重複しているかどうかを判断するカラムを指定しつつ、それ以外のカラムも取得する、というわけですね。
distinctなんで重複行は省かれるんですが、distinct onに指定したカラム以外については、一番最初に見つかった行の値を返します。
なのでorder byも同時に指定します。
さてdjangoでは1.4からquerysetのdistinctメソッドにフィールド名を渡すことによってdistinct on が使えるようになりました。
https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.distinct
例えばこのようなモデル構造で
from django.db import models class Blog(models.Model): name = models.CharField(max_length=100) def __str__(self): return self.name class Author(models.Model): name = models.CharField(max_length=100) def __str__(self): return self.name class Entry(models.Model): blog = models.ForeignKey(Blog) title = models.CharField(max_length=100) author = models.ForeignKey(Author) pub_date = models.DateTimeField() mod_date = models.DateTimeField() def __str__(self): return self.title
entryに以下のデータがある場合
id | blog_id | title | author_id | pub_date | mod_date ----+---------+---------+-----------+-------------------------------+------------------------------- 1 | 1 | テスト1 | 1 | 2012-09-13 20:35:59.888802+09 | 2012-09-13 20:35:59.888812+09 2 | 2 | テスト2 | 1 | 2012-09-13 20:48:44.643521+09 | 2012-09-13 20:48:44.643529+09 3 | 1 | テスト3 | 2 | 2012-09-14 15:03:29.382672+09 | 2012-09-14 15:03:29.382664+09 4 | 2 | テスト4 | 2 | 2012-09-14 15:03:38.606609+09 | 2012-09-14 15:03:38.6066+09 (4 rows)
普通にdistinct()を使ったら、全行返ってきます。
idが一意ですから当たり前ですね。
>>> Entry.objects.all().distinct() [<Entry: テスト1>, <Entry: テスト2>, <Entry: テスト4>, <Entry: テスト3>] >>>
distinctに'blog'を渡してみます。
>>> Entry.objects.all().distinct('blog') [<Entry: テスト3>, <Entry: テスト4>]
特にorder byを指定していないので、更新日時が新しいものが取得されたみたいです。
sqlはこのように発行されました。
SELECT DISTINCT ON ("app_entry"."blog_id") "app_entry"."id", "app_entry"."blog_id", "app_entry"."title", "app_entry"."author_id", "app_entry"."pub_date", "app_entry"."mod_date" FROM "app_entry"
じゃあ、order_byをつけてみます
>>> Entry.objects.all().distinct('blog').order_by('id') DatabaseError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT DISTINCT ON ("app_entry"."blog_id") "app_entry"."id",...
これはエラーになりました。
order_byに指定するカラムは、distinctに指定するカラムが最初にこないとダメなんですね
ちょっと修正してorder_byにblogとidを指定
>>> Entry.objects.all().distinct('blog').order_by('blog', 'id') [<Entry: テスト1>, <Entry: テスト2>]
これならOKです。
blogの重複は省いて、idの小さい順に取得できました。
発行されるsqlはこんなのです
SELECT DISTINCT ON ("app_entry"."blog_id") "app_entry"."id", "app_entry"."blog_id", "app_entry"."title", "app_entry"."author_id", "app_entry"."pub_date", "app_entry"."mod_date" FROM "app_entry" ORDER BY "app_entry"."blog_id" ASC, "app_entry"."id" ASC
では全く同じモデル構造、データで、DBがmysqlだったらどうでしょうか?
>>> Entry.objects.all().distinct('blog').order_by('blog', 'id') NotImplementedError: DISTINCT ON fields is not supported by this database backend
この通りエラーになりますね。
まあ特定のDBでしか使えない機能をばりばり使ってあとでdbが変わったらどうなるのって思わなくもないですが、dbが変わる予定がない場合は、どこかで使えるかもしれません。ので覚えておこう。
それにしてもpostgresを使う案件ってあんまり経験ない。