python - 如何在 django 模型中执行此 sql？

Question

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)

Answer 1

首先，其中一个外连接对于这种事情来说似乎不是一个好主意。由于您没有提供有关模型的信息，我只能猜测。

您是说您可能不会为每个用户准备一张 CARD？这有点道理。

你是说有些卡没有卡类型？这通常没有意义。你没有提供任何细节。但是，如果卡片没有卡片类型，我敢打赌您要么在应用程序的其他地方遇到了问题，要么您选择了非常糟糕的名称，这些名称对于这些东西的含义没有提供任何线索。您应该修复应用程序的其他部分，以确保每张卡片实际上都有一个卡片类型。或者你应该修改你的名字是有意义的。

很明显，ORM 语句使用内部连接，而您的 SQL 使用外部连接。真正的问题是什么？如何正确进行外连接？如果您花时间搜索 [Django] 和 Left Outer Join，您会发现 Raw 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

在您的 View 函数中，您可以将函数的值提供给模板以呈现报告。由于它是一个生成器，因此不会在内存中创建庞大的列表。如果您需要分页，您可以将生成器提供给分页器，一切都会运行良好。

这通常具有与具有大量外部连接的复杂原始 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