在Django中将稀疏数据集显示为表的最有效方法是什么？

I want to display data in a table where there is a many-to-one relationship between my column values (Components) and my rows (Materials). There is a further many-to-one relationship between these column values and the column headers (ComponentTypes). At the moment I have implemented something similar to this in my view:

我想在表格中显示数据,其中我的列值(组件)和我的行(材料)之间存在多对一关系。这些列值与列标题(ComponentTypes)之间存在进一步的多对一关系。目前我在我看来已经实现了类似的东西:

rows:

materials_list = Materials.objects.all()

columns:

component_type_list = Components.objects.filter(material__in = materials_list).values("component__name")

Establishing the values for the row in the order of the column titles:

按列标题的顺序建立行的值:

for material in materials:
    component_list = []
    for component_type in component_type_list:
        try:
            component = material.components_set.filter(component__name = component_type['component__name'])[0].weight_pct
        except:
            component = 0
        component_list.append(component)
    material.component_list_for_table = component_list

I then pass materials and component_type_list to the template where I have the following:

然后我将材料和component_type_list传递给我有以下内容的模板:

<table>
    <thead>
    <tr>
        <th>Guru ID</th>
        <th>Material</br>Code</th>
    {% for component_type in component_type_list %}
        <th>{{ component_type.component__name }}</th>
    {% endfor %}
    </tr>
    </thead>
    <tbody>
{% for material in materials %}
    <tr>
        <th>{{ material.GURU_ID }}</th>
        <th>{{ material.MATERIALCODE }}</th>
        {% for component in material.component_list_for_table %}
        <td>{{ component|floatformat }}%</td>     
        {% endfor %}
    </tr>
{% endfor %}
    </tbody>
</table>

Even with just 50 rows (potentially ~100 columns) this operates extremely slowly. Is there a more efficient way to do it?

即使只有50行(可能约100列),这也非常缓慢。有更有效的方法吗?

I have edited the code to simplify it so it may not be perfect.

我编辑了代码以简化它,因此它可能不完美。

1 个解决方案

#1

My 2 cents:

我的2美分:

use Django Debug Toolbar to check how many queries you've got and how long they take. You mention "this operates extremely slowly" but it's good to have data.

使用Django调试工具栏来检查你有多少查询以及他们花了多长时间。你提到“这种操作非常缓慢”但是拥有数据是件好事。

have you tried to use prefetch_related to limit queries?

您是否尝试使用prefetch_related来限制查询?

I'd use Python code (like you did) to reorganise data in the records if that's too complicated through SQL.

如果通过SQL过于复杂,我会使用Python代码(就像你一样)重新组织记录中的数据。

Maybe something like this:

也许是这样的:

materials_list = Materials.objects.prefetch_related('components_set').prefetch_related('components_set__type').all()

for material in material_list:
     component_list = []
     # your logic here - which should run fast thanks to the prefetches.
     material.component_list = component_list

#1