Django ORM将两列求和以填充第三列 [英] Django ORM to sum two columns to fill a third one
问题描述
可以说，我有一个这样的SQLite表:
Lets say, I have a SQLite table like this:
A | B | SUM
-------------
AA | BA |
AB | BB |
AC | BC |
，现在我想给它们设置数值，以填写第三列.例如:
and now I'd like to set numeric values to them, to fill in a third column. For example:
AA = 1 BA = 1
AB = 3 BB = 3
AC = 2 BC = 2
因此数据库中的表将如下所示:
So the table in database would be like this:
A | B | SUM
-------------
AA | BA | 2
AB | BB | 6
AC | BC | 4
我这样做是因为，我在项目中添加了django-tables2表，并且希望对表进行自定义排序，但是就我搜索而言，没有办法做到这一点.我只能按A列或B列对表格进行排序.但是它按字母顺序对它们进行排序，而且我的值也不同.
I'm doing this because, I added a django-tables2 table to my project and I'd like to custom sort my table, but as far as I searched, there is no way to do this. I can only sort table by column A or B. But it orders them in alphabetical order and my values are different.
另一种可能性是使用SQL查询对表进行排序，如下所示:
The other possibility would be to use SQL query to sort my table like this:
ORDER BY CASE WHEN A = 'AA' THEN 1
WHEN A = 'AB' THEN 3
WHEN A = 'AC' THEN 2 END
但是我必须在Django中使用raw方法来做到这一点，对吗?无法将其与ORM插入?到目前为止，至少这是我所了解的.或者我只是不知道该怎么做.:P
But I'd have to use raw method in django to do this, right? Inserting it with ORM is not possible? At least that's what I've understood this far. Or I just dont know how to do this. :P
因此，我认为不使用raw而是添加第三列以按该顺序对整个表进行排序会很简单；)
So instead of using raw, I thought it would be simple to add a third column just to order the whole table by that ;)
好吧，事实证明它并没有我希望的那么简单，如果可能的话，我希望获得任何帮助.谢谢！
Well, it turned out not to be as simple as I hoped and I would like to get any help if possible. Thank you!
推荐答案
这是一种实现方法.不建议命名模型字段 field_a 或 field_sum ，但比命名 a 或 sum 更好.
This is one way to do it. Naming the modelfields field_a or field_sum isn't recommended, but better than just a or sum.
from django.db import models
class Example(models.Model):
VALUES = {
'AA': 1,
'AB': 3,
'AC': 2,
'BA': 1,
'BB': 3,
'BC': 2,
}
field_a = models.CharField(default='AA', max_length=2,)
field_b = models.CharField(default='BA', max_length=2,)
# The value in this field is derived from the first two.
# Updated every time the model instance is saved.
field_sum = models.PositiveIntegerField(
default=0, # This value will be overwritten during save()
editable=False, # Hides this field in the admin interface.
)
def save(self, *args, **kwargs):
# calculate sum before saving.
self.field_sum = self.calculate_sum()
super(Example, self).save(*args, **kwargs)
def calculate_sum(self):
""" Calculate a numeric value for the model instance. """
try:
value_a = self.VALUES[self.field_a]
value_b = self.VALUES[self.field_b]
return value_a + value_b
except KeyError:
# Value_a or value_b is not in the VALUES dictionary.
# Do something to handle this exception.
# Just returning the value 0 will avoid crashes, but could
# also hide some underlying problem with your data.
return 0
有关覆盖save()方法的Django文档: https://docs.djangoproject.com/zh_CN/1.7/topics/db/models/#overriding-predefined-model-methods
The Django documentation about overriding the save() method: https://docs.djangoproject.com/en/1.7/topics/db/models/#overriding-predefined-model-methods
您可能需要阅读一些有关数据库设计和规范化的信息.在数据库中存储派生值通常不是一个好主意.
You might want to read a bit about database design and normalization. It's often not a good idea to store derived values in the database.
如果在不使用Django ORM和save()方法的情况下更改VALUES字典或更新表，则field_sum中的值可能是错误的.
If you change the VALUES dictionary or update the table without using the Django ORM and the save() method, the value in field_sum might be wrong.
保存所有实例将确保field_sum是正确的.
Saving all instances will make sure that the field_sum is correct.
for instance in Example.objects.all(): instance.save()
这篇关于Django ORM将两列求和以填充第三列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
