我有三个相关的模型:过程,因子和水平。流程与因素具有多对多关系,而因素将具有一个或多个级别。我正在尝试计算与流程有关的所有关卡组合。使用Python的itertools作为模型方法可以直接实现,但是执行速度有些慢,因此我试图弄清楚如何使用Django ORM在SQL中执行此计算。
楷模:
class Process(models.Model):
factors = models.ManyToManyField(Factor, blank = True)
class Factor(models.Model):
...
class Level(models.Model):
factor = models.ForeignKey(Factor, on_delete=models.CASCADE)
示例:流程运行涉及三个因子(距离,爬升,表面),每个因子由多个级别组成(长/短,平坦/陡峭,道路/混合/足迹)。计算SQL中的组合将涉及通过首先确定涉及多少因素(在此示例中为3)并执行CROSS JOIN
所有级别的多次来构建查询。
在SQL中,可以这样实现:
WITH foo AS
(SELECT * FROM Level
WHERE Level.factor_id IN
(SELECT ProcessFactors.factor_id FROM ProcessFactors WHERE process_id = 1)
)
SELECT a1.*, a2.*, a3.*
FROM foo a1
CROSS JOIN foo a2
CROSS JOIN foo a3
WHERE (a1.factor_id < a2.factor_id) AND (a2.factor_id < a3.factor_id)
a1.name | a2.name | a3.name
--------------------------
Long | Flat | Road
Long | Flat | Mixed
Long | Flat | Trail
Long | Hilly | Road
Long | Hilly | Mixed
Long | Hilly | Trail
Short | Flat | Road
Short | Flat | Mixed
Short | Flat | Trail
Short | Hilly | Road
Short | Hilly | Mixed
Short | Hilly | Trail
目前,我已经将此作为过程模型的一种方法实现为:
def level_combinations(self):
levels = []
for factor in self.factors.all():
levels.append(Level.objects.filter(factor = factor))
combinations = []
for levels in itertools.product(*levels):
combination = {}
combination["levels"] = levels
combinations.append(combination)
return combinations
是否可以使用Django ORM做到这一点,或者它是否足够复杂以至于应将其作为原始查询来实现,以提高Python代码实现的速度?
几年前,有一个关于在Django ORM中执行性能CROSS JOIN
的类似问题(大约是Django v1.3)似乎没有引起人们的广泛关注(作者只喜欢使用Python itertools)。
from itertools import groupby, product
def level_combinations(self):
# We need order by factor_id for proper grouping
levels = Level.objects.filter(factor__process=self).order_by('factor_id')
# [{'name': 'Long', 'factor_id': 1, ...},
# {'name': 'Short', 'factor_id': 1, ...},
# {'name': 'Flat', 'factor_id': 2, ...},
# {'name': 'Hilly', 'factor_id': 2, ...}]
groups = [list(group) for _, group in groupby(levels, lambda l: l.factor_id)]
# [[{'name': 'Long', 'factor_id': 1, ...},
# {'name': 'Short', 'factor_id': 1, ...}],
# [{'name': 'Flat', 'factor_id': 2, ...},
# {'name': 'Hilly', 'factor_id': 2, ...}]]
# Note: don't forget, that product is iterator/generator, not list
return product(*groups)
如果顺序无关紧要,则:
def level_combinations(self):
levels = Level.objects.filter(factor__process=self)
groups = {}
for level in levels:
groups.setdefault(level.factor_id, []).append(level)
return product(*groups.values())
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句