如何在Django模型中执行此SQL？
SELECT *, SUM( cardtype.price - cardtype.cost ) AS profit
FROM user
LEFT OUTER JOIN card ON ( user.id = card.buyer_id )
LEFT OUTER JOIN cardtype ON ( card.cardtype_id = cardtype.id )
GROUP BY user.id
ORDER BY profit DESC
我试过这个：
User.objects.extra(select=dict(profit='SUM(cardtype.price-cardtype.cost)')).annotate(sum=Sum('card__cardtype__price')).order_by('-profit')
但是Django自动把 SUM( cardtype.price ) 加到了 GROUP BY 语句里，这样SQL就无法运行了。
有没有办法不使用原始SQL来实现这个？
提供一下模型，别在意这些中文字符 :)
class User(models.Model):
class Meta:
verbose_name = "用户"
verbose_name_plural = "用户"
ordering = ['-regtime']
user_status= (
("normal", "正常"),
("deregistered", "注销"),
("locked", "锁定"),
)
name = models.CharField("姓名", max_length=20, db_index=True)
spec_class = models.ForeignKey(SpecClass, verbose_name="专业班级")
idcard = models.CharField("身份证号", max_length=18)
mobileno = models.CharField("手机号", max_length=11)
password = models.CharField("密码", max_length=50) # plain
address = models.CharField("住址", max_length=100)
comment = models.TextField("备注")
certserial = models.CharField("客户证书序列号", max_length=100)
regtime = models.DateTimeField("注册时间", default=datetime.datetime.now)
lastpaytime = models.DateTimeField("上次付款时间", default=datetime.datetime.now)
credit = models.FloatField("信用额度", default=100)
money = models.FloatField("余额", default=0)
use_password = models.BooleanField("使用密码")
use_fetion = models.BooleanField("接收飞信提示")
status = models.CharField("账户状态", choices = user_status, default="normal", max_length=20, db_index=True)
def __unicode__(self):
return self.name
class CardType(models.Model):
class Meta:
verbose_name = "点卡类型"
verbose_name_plural = "点卡类型"
ordering = ['name']
name = models.CharField("类型名称", max_length=20, db_index=True)
note = models.CharField("说明", max_length=100)
offcial = models.BooleanField("官方卡", default=True)
available = models.BooleanField("可用", default=True, db_index=True)
payurl = models.CharField("充值地址", max_length=200)
price = models.FloatField("价格")
cost = models.FloatField("进货价格")
def __unicode__(self):
return u"%s(%.2f元%s)" % (self.name, self.price, u", 平台卡" if not self.offcial else "")
def profit(self):
return self.price - self.cost
profit.short_description = "利润"
class Card(models.Model):
class Meta:
verbose_name = "点卡"
verbose_name_plural = "点卡"
ordering = ['-createtime']
card_status = (
("instock", "未上架"),
("available", "可用"),
("sold", "已购买"),
("invalid", "作废"),
("returned", "退卡"), # sell to the same person !
("reselled", "退卡重新售出"),
)
cardtype = models.ForeignKey(CardType, verbose_name="点卡类型")
serial = models.CharField("卡号", max_length=40)
password = models.CharField("卡密", max_length=20)
status = models.CharField("状态", choices = card_status, default="instock", max_length=20, db_index=True)
createtime = models.DateTimeField("入库时间")
buytime = models.DateTimeField("购买时间", blank=True, null=True)
buyer = models.ForeignKey(User, blank=True, null=True, verbose_name="买家")
def __unicode__(self):
return u'%s[%s]' % (self.cardtype.name, self.serial)
2 个回答
-1
我找到这个了
在Django的QuerySet中求和计算列
现在必须使用原始SQL了...
谢谢你们两个!
1
首先，对于这种情况，使用外连接似乎不是个好主意。因为你没有提供关于你的模型的任何信息，我只能猜测。
你是在说可能不是每个用户都有一张卡吗？这听起来有点道理。
你还在说有些卡没有卡类型吗？这通常听起来不太合理。你没有提供任何细节。不过，如果一张卡没有卡类型，我敢打赌你在应用的其他地方有问题，或者你给这些东西起的名字太糟糕，根本无法让人明白它们的意思。你应该修正应用的其他部分，确保每张卡都有卡类型。或者你应该把名字改得更有意义。
很明显，你的ORM语句使用的是内连接，而你的SQL使用的是外连接。那真正的问题是什么呢？是如何正确使用外连接吗？如果你花时间搜索一下[Django]和左外连接，你会发现原始SQL是个糟糕的主意。
还是说真正的问题是如何正确计算总和？从你自己的回答来看，SQL是错的，你在总和上确实遇到了麻烦。如果是这样，请把SQL整理得正确一些。
如果外连接确实是问题的一部分——而不仅仅是视觉上的干扰——那么你需要像这样处理外连接和总和。
def user_profit():
for u in User.objects.all():
profit = sum[ t.price - t.cost
for c in u.card_set.all()
for t in c.cardtype_set.all() ]
yield user, profit
在你的视图函数中，你可以将这个函数的值提供给模板来渲染报告。因为它是一个生成器，所以不会在内存中创建巨大的列表。如果你需要分页，可以将生成器提供给分页器，这样一切都会顺利进行。
这通常和复杂的原始SQL查询（包含很多外连接）速度相当。
如果卡和卡类型之间的关系实际上不是可选的，那么你可以稍微简化一下。不过你仍然需要考虑外连接。
def user_profit():
for u in User.objects.all():
profit = sum[ c.cardtype.price - c.cardtype.cost
for c in u.card_set.all() ]
yield user, profit