django select_related(join) & prefetch_related(in)

# -*- encoding:utf-8 -*-
import sys
reload(sys)
sys.getdefaultencoding()
sys.getfilesystemencoding()
print '中国字'

from django.db import models

class Province(models.Model):
name = models.CharField(max_length=10)
def __unicode__(self):
return self.name

class City(models.Model):
name = models.CharField(max_length=5)
province = models.ForeignKey(Province)
def __unicode__(self):
return self.name

class Person(models.Model):
firstname = models.CharField(max_length=10)
lastname = models.CharField(max_length=10)
visitation = models.ManyToManyField(City, related_name = "visitor")
hometown = models.ForeignKey(City, related_name = "birth")
living = models.ForeignKey(City, related_name = "citizen")
def __unicode__(self):
return self.firstname + self.lastname

class Order(models.Model):
customer = models.ForeignKey(Person)
orderinfo = models.CharField(max_length=50)
time = models.DateTimeField(auto_now_add = True)
def __unicode__(self):
return self.orderinfo

# 如果要同时获得张三的故乡和现居地的省份。链式调用的select_related相当于使用可变长参数。Django < 1.7，链式调用会导致前边的select_related失效，只保留最后一个。
# 此处是横向链式调用
zhangs = Person.objects.select_related('hometown__province').select_related('living__province').get(firstname=u"张",lastname=u"三")
zhangs.hometown.province
zhangs.living.province

# 要注意的是，在使用QuerySet的时候，一旦在链式操作中改变了数据库请求，之前用prefetch_related缓存的数据将会被忽略掉。这会导致Django重新请求数据库来获得相应的数据，从而造成性能问题。这里提到的改变数据库请求指各种filter()、exclude()等等最终会改变SQL代码的操作。而all()并不会改变最终的数据库请求，因此是不会导致重新请求数据库的。
# 获取所有人访问过的城市中带有“市”字的城市，这样做会导致大量的SQL查询：
plist = Person.objects.prefetch_related('visitation')
[p.visitation.filter(name__icontains=u"市") for p in plist]
# 优化后
plist = Person.objects.prefetch_related('visitation')
[[city for city in p.visitation.all() if u"市" in city.name] for p in plist]

# 情形一：查询家乡是湖北省的人，以下是返回的数据
hb = Province.objects.prefetch_related("city_set__birth").objects.get(name__iexact=u'hubei')
people = []
for city in hb.city_set.all():
people.extend(city.birth.all())

people = list(People.objects.select_related("hometown__province").filter(hometown__province__name__iexact=u'hubei'))
+
----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
| id | firstname | lastname | hometown_id | living_id | id | name | province_id | id | name |
+
----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
| 1 | 张 | 三 | 3 | 1 | 3 | 十堰市 | 1 | 1 | 湖北省 |
| 2 | 李 | 四 | 1 | 3 | 1 | 武汉市 | 1 | 1 | 湖北省 |
| 3 | 王 | 麻子 | 3 | 2 | 3 | 十堰市 | 1 | 1 | 湖北省 |
+
----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
3 rows in set (0.00 sec)

# 情形二：如果我们拿到了一个订单的id 我们要知道这个订单的客户去过的省份。因为有ManyToManyField显然必须要用prefetch_related()。如果只用prefetch_related()会怎样呢？
plist = Order.objects.prefetch_related("customer__visitation__province").get(id=1)
for city in plist.customer.visitation.all():
print city.province.name

plist = Order.objects.select_related("customer").prefetch_related("customer__visitation__province").get(id=1)
for city in plist.customer.visitation.all():
print city.province.name
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
| id | customer_id | orderinfo | time | id | firstname | lastname | hometown_id | living_id |
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
| 1 | 1 | Info of Order | 2014-08-10 17:05:48 | 1 | 张 | 三 | 3 | 1 |
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
1 row in set (0.00 sec)

+-----------------------+----+--------+-------------+
| _prefetch_related_val | id | name | province_id |
+-----------------------+----+--------+-------------+
| 1 | 1 | 武汉市 | 1 |
| 1 | 2 | 广州市 | 2 |
| 1 | 3 | 十堰市 | 1 |
+-----------------------+----+--------+-------------+
3 rows in set (0.00 sec)

+----+--------+
| id | name |
+----+--------+
| 1 | 湖北省 |
| 2 | 广东省 |
+----+--------+
2 rows in set (0.00 sec)

注意：可以在调用prefetch_related之前调用select_related，并且Django会按照你想的去做：先select_related，然后利用缓存到的数据prefetch_related。然而一旦prefetch_related已经调用，select_related将不起作用。