How to fetch related model in django_tables2 to avoid a lot of queries?

Question

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",)
    

Answer 1

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