DjangoのORMでサブクエリやrawを使う

Python Django

試したのはDjango1.4.1。
こういう親子関係のモデルがあったとする。

myapp/models.py

from django.db import models


class Parent(models.Model):
    name = models.CharField(max_length=30)

    def __unicode__(self):
        return '{}, {}'.format(self.name, getattr(self, 'child_name', None))

    class Meta:
        db_table = 'parent'


class Child(models.Model):
    name = models.CharField(max_length=30)
    parent = models.ForeignKey(Parent)

    class Meta:
        db_table = 'child'

Childのほうから、Parentの値をfilterに使うのは簡単なのでいいとして、逆方向の場合にちょっと悩む。
たとえば、こんな感じにデータが入っていたとする。

mysql> SELECT * FROM parent;
+----+---------+
| id | name    |
+----+---------+
|  1 | Parent1 |
|  2 | Parent2 |
|  3 | Parent3 |
+----+---------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM child;
+----+--------+-----------+
| id | name   | parent_id |
+----+--------+-----------+
|  1 | Child1 |         1 |
|  2 | Child2 |         1 |
|  3 | Child3 |         1 |
|  4 | Child4 |         2 |
|  5 | Child5 |         2 |
|  6 | Child6 |         2 |
|  7 | Child7 |         3 |
|  8 | Child8 |         3 |
|  9 | Child9 |         3 |
+----+--------+-----------+
9 rows in set (0.01 sec)

それに対して、こういうクエリを投げたい。

mysql> SELECT * FROM parent LEFT JOIN child ON parent.id = child.parent_id GROUP BY parent_id ORDER BY child.name DESC;
+----+---------+------+--------+-----------+
| id | name    | id   | name   | parent_id |
+----+---------+------+--------+-----------+
|  3 | Parent3 |    7 | Child7 |         3 |
|  2 | Parent2 |    4 | Child4 |         2 |
|  1 | Parent1 |    1 | Child1 |         1 |
+----+---------+------+--------+-----------+
3 rows in set (0.00 sec)

DjangoのQuerySet(ORM)のannotateやaggregateではこのクエリを作れないようなので、extraでサブクエリにしてみる。

>>> from myapp.models import Parent
>>> Parent.objects.extra(select={'child_name': 'SELECT child.name FROM child WHERE child.parent_id = parent.id GROUP BY parent.id'}).order_by('-child_name')
[<Parent: Parent3, Child7>, <Parent: Parent2, Child4>, <Parent: Parent1, Child1>]

これで欲しい結果は得られた。
やっぱりサブクエリを使いたくないってときは、rawメソッドでSQLを投げるのもあり。

>>> from myapp.models import Parent
>>> Parent.objects.raw('SELECT parent.*, child.name child_name FROM parent LEFT JOIN child ON parent.id = child.parent_id GROUP BY parent_id ORDER BY child.name DESC')
<RawQuerySet: 'SELECT parent.*, child.name child_name FROM parent LEFT JOIN child ON parent.id = child.parent_id GROUP BY parent_id ORDER BY child.name DESC'>
>>> list(Parent.objects.raw('SELECT parent.*, child.name child_name FROM parent LEFT JOIN child ON parent.id = child.parent_id GROUP BY parent_id ORDER BY child.name DESC'))
[<Parent: Parent3, Child7>, <Parent: Parent2, Child4>, <Parent: Parent1, Child1>]

他に良い方法があったら誰か教えて下さい。

2012/10/4追記

@mitszoさんから指摘もらいました。GROUP BYの指定があいまいで、SQLite3だと結果が変わるそうです。上記の例はMySQL5.5で動かしてます。