如何在django_tables2中提取相关模型以避免大量查询?

huangapple go评论71阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年2月10日 04:32:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/75404140.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定