英文:
Django prefetch_related over 3 models (one "relationship Model"
问题
I'm trying to aggregate and Output Data from my Models to my HTML Page
Models.py
from django.db import models
from django.contrib import admin
class ClientDB(models.Model):
Hostname= models.CharField(max_length=50,primary_key=True)
User= models.CharField(max_length=100)
class VULDB(models.Model):
VID=models.IntegerField(primary_key=True)
CVELIST=models.CharField(max_length=50)
CVSScore=models.FloatField(max_length=5)
Serverity=models.CharField(max_length=25)
Title=models.CharField(max_length=1000)
Summary=models.CharField(max_length=1000)
class ClientVULDB(models.Model):
Hostname= models.ForeignKey(ClientDB, on_delete=models.CASCADE)
VID=models.ForeignKey(VULDB, on_delete=models.CASCADE)
Path=models.CharField(max_length=1000)
isActive=models.BooleanField(default=True)
class Meta:
constraints = [
models.UniqueConstraint(
fields=['Hostname', 'VID'], name='unique_migration_host_combination'
)
]
I would like to create a view that Output and aggregate Data in the Model to my HTML Template. I need help how I can aggregate (and put it in a Queryset?) the data like in the Screenshot
I've got two Querysets which Query the Models:
q1=VULDB.objects.filter().values_list('Title','clientvuldb__Hostname')
q2=ClientDB.objects.filter().values_list('Hostname', 'Benutzer', 'clientvuldb__VID')
The Output is as follows:
<QuerySet [('Test-VUL', 'Host2'), ('Test-VUL2', 'Host2'), ('Test-VUL3', 'Host1'), ('Test-Vul4', 'Host2')]>
<QuerySet [('Host1', 'User1', 1), ('Host2', 'User2', 1)]
I would like to render
Host1, Test-VUL3
Host2, Test-VUL, Test-VUL2, Test-VUL3
英文:
I'm trying to aggregate and Output Data from my Models to my HTML Page
Models.py
from django.db import models
from django.contrib import admin
class ClientDB(models.Model):
Hostname= models.CharField(max_length=50,primary_key=True)
User= models.CharField(max_length=100)
class VULDB(models.Model):
VID=models.IntegerField(primary_key=True)
CVELIST=models.CharField(max_length=50)
CVSScore=models.FloatField(max_length=5)
Serverity=models.CharField(max_length=25)
Title=models.CharField(max_length=1000)
Summary=models.CharField(max_length=1000)
class ClientVULDB(models.Model):
Hostname= models.ForeignKey(ClientDB, on_delete=models.CASCADE)
VID=models.ForeignKey(VULDB, on_delete=models.CASCADE)
Path=models.CharField(max_length=1000)
isActive=models.BooleanField(default=True)
class Meta:
constraints = [
models.UniqueConstraint(
fields=['Hostname', 'VID'], name='unique_migration_host_combination' # legt
)
]
I would like to create a view that Output and aggregate Data in the Model to my HTML Template. I need help how I can aggregate (and put it in an Queryset?) the data like in the Screenshot
I've got two Querysets which Query the Models:
q1=VULDB.objects.filter().values_list('Title','clientvuldb__Hostname')
q2=ClientDB.objects.filter().values_list('Hostname', 'Benutzer', 'clientvuldb__VID')
The Output is as follows:
<QuerySet [('Test-VUL', 'Host2'),
('Test-VUL2', 'Host2'),
('Test-VUL3', 'Host1'),
('Test-Vul4', 'Host2')]>
<QuerySet [ ('Host1', 'User1', 1),
('Host2', 'User2', 1)]
I would like to render
Host1, Test-VUL3
Host2, Test-VUL, Test-VUL2, Test-VUL3
答案1
得分: 1
不要使用 .values(..) 或 .values_list(..)，它会侵蚀模型层。
使用以下方式：
from django.db.models import Prefetch
qs = VULDB.objects.prefetch_related(
Prefetch('clientvuldb_set', ClientVULDB.objects.select_related('VID'))
)
在模板中，使用以下方式进行渲染：
<ul>
{% for item in qs %}
<li>{{ item.Hostname }}</li>
<ul>
{% for subitem in item.clientvuldb_set.all %}
<li>{{ subitem.VID.title }}</li>
{% endfor %}
</ul>
{% endfor %}
</ul>
注意: 通常，Django 模型中字段的命名应该使用 snake_case，而不是 PascalCase，所以应该是：
title而不是Title。
注意: 模型通常不应该带有
Db后缀。模型不是表，它存储在关系数据库中作为表，但即使在那里，它还有额外的逻辑，如验证器、管理器等。
英文:
Don't use .values(..) or .values_list(..), it will erode the model layer.
work with:
<pre><code>from django.db.models import Prefetch
qs = VULDB.objects.prefetch_related(
<b>Prefetch(</b>'clientvuldb_set', ClientVULDB.objects.select_related('VID')<b>)</b>
)</code></pre>
in the template, render with:
<pre><code><ul>
{% for item in qs %}
<li>{{ qs.Hostname }}</li>
<ul>
{% for subitem in <b>item.clientvuldb_set.all</b> %}
<li>{{ subitem.VID.Title }}</li>
{% endfor %}
</ul>
{% endfor %}
</ul></code></pre>
> Note: normally the name of the fields in a Django model are written in snake_case, not PascalCase, so it should be: title instead of <s>Title</s>.
> Note: Models normally have no Db suffix. A model is not a table, it is stored in a relational database as a table, but even then it has extra logic like validators, managers, etc.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论