Djangoでpostgresのdistinct onを実行する

django postgres

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を使う案件ってあんまり経験ない。