英文:
How to fetch related model in django_tables2 to avoid a lot of queries?
问题
以下是您要翻译的内容:
"I might be missing something simple here. And I simply lack the knowledge or some how-to.
I got two models, one is site
, the other one is siteField
and the most important one - siteFieldValue
.
My idea is to create a django table (for site
) that uses the values from siteFieldValue
as a number in a row, for a specific site, under certain header. The problem is - each site can have 50s of them. That * number of columns specified by def render_
functions * number of sites equals to a lot of queries and I want to avoid that.
My question is - is it possible to, for example, prefetch all the values for each site (SiteFieldValue.objects.filter(site=record).first()
somewhere in the SiteListTable
class), put them into an array and then use them in the def render_
functions by simply checking the value assigned to a key (id of the field)."
以下是相关的模型和表视图的代码部分:
class Site(models.Model):
name = models.CharField(max_length=100)
class SiteField(models.Model):
name = models.CharField(max_length=100)
description = models.CharField(max_length=500, null=True, blank=True)
def __str__(self):
return self.name
class SiteFieldValue(models.Model):
site = models.ForeignKey(Site, on_delete=models.CASCADE)
field = models.ForeignKey(SiteField, on_delete=models.CASCADE)
value = models.CharField(max_length=500)
class SiteListTable(tables.Table):
name = tables.Column()
importance = tables.Column(verbose_name='Importance', empty_values=())
vertical = tables.Column(verbose_name='Vertical', empty_values=())
#... and many more to come... all values based on siteFieldValue
def render_importance(self, value, record):
q = SiteFieldValue.objects.filter(site=record, field=1).first()
# ^^ I don't want this!! I would want the SiteFieldValue to be prefetched somewhere else for that model and just check the array for field id in here.
if (q):
return q.value
else:
return None
def render_vertical(self, value, record):
q = SiteFieldValue.objects.filter(site=record, field=2).first()
# ^^ I don't want this!! I would want the SiteFieldValue to be prefetched somewhere else for that model and just check the array for field id in here.
if (q):
return q.value
else:
return None
class Meta:
model = Site
attrs = {
"class": "table table-striped",
"thead": {'class': 'thead-light',}
}
template_name = "django_tables2/bootstrap.html"
fields = ("name", "importance", "vertical",)
英文:
I might be missing something simple here. And I simply lack the knowledge or some how-to.
I got two models, one is site
, the other one is siteField
and the most important one - siteFieldValue
.
My idea is to create a django table (for site
) that uses the values from siteFieldValue
as a number in a row, for a specific site, under certain header. The problem is - each site can have 50s of them. That * number of columns specified by def render_
functions * number of sites equals to a lot of queries and I want to avoid that.
My question is - is it possible to, for example, prefetch all the values for each site (SiteFieldValue.objects.filter(site=record).first()
somewhere in the SiteListTable
class), put them into an array and then use them in the def render_
functions by simply checking the value assigned to a key (id of the field).
Models:
class Site(models.Model):
name = models.CharField(max_length=100)
class SiteField(models.Model):
name = models.CharField(max_length=100)
description = models.CharField(max_length=500, null=True, blank=True)
def __str__(self):
return self.name
class SiteFieldValue(models.Model):
site = models.ForeignKey(Site, on_delete=models.CASCADE)
field = models.ForeignKey(SiteField, on_delete=models.CASCADE)
value = models.CharField(max_length=500)
Table view
class SiteListTable(tables.Table):
name = tables.Column()
importance = tables.Column(verbose_name='Importance',empty_values=())
vertical = tables.Column(verbose_name='Vertical',empty_values=())
#... and many more to come... all values based on siteFieldValue
def render_importance(self, value, record):
q = SiteFieldValue.objects.filter(site=record, field=1).first()
# ^^ I don't want this!! I would want the SiteFieldValue to be prefetched somewhere else for that model and just check the array for field id in here.
if (q):
return q.value
else:
return None
def render_vertical(self, value, record):
q = SiteFieldValue.objects.filter(site=record, field=2).first()
# ^^ I don't want this!! I would want the SiteFieldValue to be prefetched somewhere else for that model and just check the array for field id in here.
if (q):
return q.value
else:
return None
class Meta:
model = Site
attrs = {
"class": "table table-striped","thead" : {'class': 'thead-light',}}
template_name = "django_tables2/bootstrap.html"
fields = ("name", "importance", "vertical",)
答案1
得分: 1
以下是翻译好的代码部分:
这可能会让你入门。我已经将它分成了几个步骤,但它们可以很容易地链接在一起。
# 获取所需的所有对象。您可以根据需要进行筛选,比如按site__name进行筛选。
qs = SiteFieldValue.objects.select_related('site', 'field')
# 让我们保持简单,只获取我们想要的值
qs_values = qs.values('site__name', 'field__name', 'value')
# qs_values是一个查询集。为了方便操作,让我们将它转换成一个列表
qs_list = list(qs_values)
# 设置最终的字典
final_dict = {}
# 创建键(站点)和值,以便它们都分组在一起
for site in qs_list:
# 如果尚未创建字段的子字典,请创建它
if site['site__name'] not in final_dict:
final_dict[site['site__name']] = {}
final_dict[site['site__name']][site['name']] = site['site__name']
final_dict[site['site__name']][site['field__name']] = site['value']
# 现在让我们将我们的字典列表转换为字典列表,以便根据table2文档使用
data = []
for site in final_dict:
data.append(final_dict[site])
现在你有一个字典列表,例如:
[{'name': site__name, 'col1name': value...}]
并可以按照table2文档中所示添加它。
英文:
This might get you started. I've broken it up into steps but they can be chained quite easily.
#Get all the objects you'll need. You can filter as appropriate, say by site__name).
qs = SiteFieldValue.objects.select_related('site', 'field')
#lets keep things simple and only get the values we want
qs_values = qs.values('site__name','field__name','value')
#qs_values is a queryset. For ease of manipulation, let's make it a list
qs_list = list(qs_values)
#set up a final dict
final_dict = {}
# create the keys (sites) and values so they are all grouped
for site in qs_list:
#create the sub_dic for the fields if not already created
if site['site__name'] not in final_dict:
final_dict[site['site__name']] = {}
final_dict[site['site__name']][site['name']] = site['site__name']
final_dict[site['site__name']][site['field__name']] = site['value']
#now lets convert our dict of dicts into a list of dicts
# for use as per table2 docs
data = []
for site in final_dict:
data.append(final_dict[site])
Now you have a list of dicts eg,
[{'name':site__name, 'col1name':value...] and can add it as shown in the table2 docs
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论