Django如何关联多表数据并在单张网页表格中无嵌套循环展示

1 模型层调整（推荐方案，兼容存量数据） #

原两张表未建立ORM层面的关联，先给Meter_Data_table添加外键关联，无需修改现有数据库字段名和存量数据：

class Meters_table(models.Model):
    Meter_id = models.AutoField(unique=True, editable=False, primary_key=True)
    Account_id = models.CharField(max_length=128)
    Location_id = models.CharField(max_length=150, help_text="(ID of the locations table)")
    RR_No = models.CharField(max_length=128)
    Meter_type = models.CharField(max_length=150, help_text="(Industry,Residential & Transformer)")
    Meter_make = models.CharField(max_length=150)
    Meter_model = models.CharField(max_length=150)
    Usage_type = models.CharField(
    max_length=150, help_text="(Industry,Residential & Transformer)")

    def __str__(self):
            return self.Usage_type

class Meter_Data_table(models.Model):
    id = models.AutoField(unique=True, editable=False, primary_key=True)
    # 改外键关联Meters_table，保持原有字段名不变
    Meter_id = models.ForeignKey(
        Meters_table,
        on_delete=models.CASCADE,
        to_field='Meter_id',
        db_column='Meter_id',
        related_name='data_records'
    )
    IMEI_Number = models.CharField(max_length=150)
    KWH = models.CharField(max_length=128)
    KVAH = models.CharField(max_length=150)
    PF = models.CharField(max_length=150)
    BMD = models.CharField(max_length=128)
    Meter_time_stamp = models.DateTimeField(max_length=150)
    Receive_time_stamp = models.DateTimeField(max_length=150)

    def __str__(self):
            return str(self.Meter_id.Meter_id)

修改完成后执行python manage.py makemigrations和python manage.py migrate即可，不会改动现有数据。

2 视图层关联查询 #

用select_related实现单次JOIN查询，一次性带出两张表的所有所需字段，无需嵌套循环：

def meterstatistics(request):
    # 关联查询，一次性取出Meter_Data_table和对应的Meters_table数据
    vark = Meter_Data_table.objects.select_related('Meter_id').all()
    Lastkwh = Meter_Data_table.objects.last()
    Lasttime = Meter_Data_table.objects.last()
    d = {
        'Lastkwh': Lastkwh, 'Lasttime': Lasttime, 'vark': vark
    }
    return render(request, 'meterstatistics.html', d)

原varr参数已不需要，所有关联数据都已经整合到vark查询集里。

3 前端模板渲染（单层循环即可） #

修正原有模板的语法错误，直接通过点语法访问关联表字段：

<table id="table">
<thead class="thead-light bg-primary">
    <tr>
        <th scope="col">Meter_id</th>
        <th scope="col">DCU IMEI</th>
        <th scope="col">RR No</th>
        <th scope="col">Last KWH</th>
        <th scope="col">PF</th>
        <th scope="col">Last Meter Time Stamp</th>
        <th scope="col">Location</th>
        <th scope="col">Frequency</th>
        <th scope="col">Relay</th>
        <th scope="col">Action</th>
    </tr>
</thead>
<tbody>
    {% for i in vark %}
    <tr>
        <td>{{i.Meter_id.Meter_id}}</td>
        <td>{{i.IMEI_Number}}</td>
        <!-- 从关联的Meters_table取RR_No -->
        <td>{{i.Meter_id.RR_No}}</td>
        <td>{{i.KWH }}</td>
        <td>{{i.PF}}</td>
        <td>{{i.Meter_time_stamp }}</td>
        <!-- 从关联的Meters_table取Location_id -->
        <td>{{i.Meter_id.Location_id}}</td>
        <td></td>
        <td><label class="switch">
                <input type="checkbox">
                <span class="slider round"></span>
            </label></td>
        <td>
            <a href="{% url 'graph' %}"><i style="font-size: 30px;" class="fa fa-eye"
                    aria-hidden="true"></i> </a>
            <a href="metertableedit/{{i.Meter_id.Meter_id}}"><i style="font-size: 30px;"
                    class="fa fa-pencil-square-o" aria-hidden="true"></i></a>
            <!-- <a href="delmetertable/{{i.Meter_id.Meter_id}}"><i style="font-size: 30px;"
                    class="fa fa-trash" aria-hidden="true"></i></a> -->
        </td>
    </tr>
    {% endfor %}
</tbody>
</table>

备选方案（无需修改模型） #

如果不能修改现有模型结构，可以在视图层用字典手动合并数据，同样实现单层循环：

def meterstatistics(request):
    # 先把Meters_table的数据按Meter_id转成字典，O(1)查询
    meter_dict = {item.Meter_id: item for item in Meters_table.objects.all()}
    vark = Meter_Data_table.objects.all()
    # 给每个vark对象绑定对应的meter数据
    for item in vark:
        item.meter_info = meter_dict.get(int(item.Meter_id), None)
    Lastkwh = Meter_Data_table.objects.last()
    Lasttime = Meter_Data_table.objects.last()
    d = {
        'Lastkwh': Lastkwh, 'Lasttime': Lasttime, 'vark': vark
    }
    return render(request, 'meterstatistics.html', d)

前端直接访问{{i.meter_info.RR_No}}、{{i.meter_info.Location_id}}即可，同样不需要嵌套循环。

内容的提问来源于stack exchange，提问作者kabilan kabi