django - Django：使用嵌套模型从许多关系查询中进行复杂排序和过滤
问题描述
我想要实现的目标： 我想要列表
[
{
"location":"Loc 1",
"session":[
{
"start":"2021-01-01",
"counts":600,
"details":[
{
"id":13,
"length_max":21,
"length_min":15,
"length_avg":16,
"length_std":19,
"is_active":false,
"type":"dog"
}
]
}
]
},
{
"location":"Loc3",
"session":[
{
"start":"2021-01-01",
"counts":500,
"details":[
{
"id":15,
"length_max":19,
"length_min":16,
"length_avg":16,
"length_std":19,
"is_active":false,
"type":"dog"
}
]
}
]
}
]
我的视图集是
class SessionFilter(FilterSet):
type_filter = filters.CharFilter(method="filter_by_type")
def filter_by_type(self,queryset,name,value):
queryset = queryset.filter(session__details__type=value).distinct()
return queryset
class SessionModelViewSet(ModelViewSet):
queryset = Session.objects.all()
serializer_class = SessionSerializers
filter_backends = (DjangoFilterBackend,)
filter_class = SessionFilter
我正在尝试根据类型进行过滤，但无法获取我需要的内容。我得到的结果是
[
{
"location":"Loc 1",
"session":[
{
"start":"2021-01-01",
"counts":600,
"details":[
{
"id":13,
"length_max":21,
"length_min":15,
"length_avg":16,
"length_std":19,
"is_active":false,
"type":"dog"
}
]
},
{
"start":"2021-01-01",
"counts":600,
"details":[
{
"id":7,
"length_max":39,
"length_min":25,
"length_avg":25,
"length_std":27,
"is_active":true,
"type":"cat"
},
{
"id":19,
"length_max":39,
"length_min":25,
"length_avg":25,
"length_std":27,
"is_active":false,
"type":"cat"
}
]
}
]
},
{
"location":"Loc3",
"session":[
{
"start":"2021-01-01",
"counts":500,
"details":[
{
"id":15,
"length_max":19,
"length_min":16,
"length_avg":16,
"length_std":19,
"is_active":false,
"type":"dog"
}
]
},
{
"start":"2021-01-01",
"counts":500,
"details":[
{
"id":9,
"length_max":39,
"length_min":25,
"length_avg":25,
"length_std":27,
"is_active":true,
"type":"cat"
},
{
"id":21,
"length_max":39,
"length_min":25,
"length_avg":25,
"length_std":27,
"is_active":false,
"type":"cat"
}
]
}
]
}
]
如何自定义过滤器或更改嵌套查询以获取所需的输出以及如何按 id 排序。直到第二个嵌套级别，我才能获取数据（也许并不复杂），但在第三级，我面临这个问题。
模型.py
class Place(models.Model):
id = models.IntegerField(primary_key=True)
location = models.CharField(max_length=100)
class Meta:
db_table = 'place'
managed=False
class Session(models.Model):
id = models.IntegerField(primary_key=True)
place = models.ForeignKey(Place,related_name='session',on_delete=models.CASCADE, null=True)
start = models.DateField(auto_now=True)
counts = models.IntegerField()
class Meta:
db_table = 'session'
managed=False
class Animal(models.Model):
id = models.IntegerField(primary_key=True)
sess = models.ForeignKey(Session,related_name='details',on_delete=models.CASCADE, null=True)
type = models.CharField(max_length=100)
is_active = models.BooleanField()
length_max = models.IntegerField()
length_min = models.IntegerField()
length_avg = models.IntegerField()
length_std = models.IntegerField()
class Meta:
db_table = 'animal'
managed=False
序列化程序.py
class AnimalSerializers(FlexFieldsModelSerializer):
class Meta:
model = Animal
fields = ["id","length_max","length_min","length_avg","length_std","is_active","type"]
class SessionSerializers(FlexFieldsModelSerializer):
class Meta:
model = Session
fields = ["start","counts","details"]
expandable_fields = {
'details': (AnimalSerializers, {'many': True})
}
class PlaceSerializers(FlexFieldsModelSerializer):
class Meta:
model = Place
fields = ["location","session"]
expandable_fields = {
'session': (SessionSerializers, {'many': True})
}
预取后，我得到的 json 格式为
{
"location": "Loc 2",
"session": [
{
"start": "2021-01-01",
"counts": 300,
"details": [
{
"id": 14,
"length_max": 22,
"length_min": 16,
"length_avg": 16,
"length_std": 19,
"is_active": true,
"type": "dog"
}
]
},
{
"start": "2021-01-01",
"counts": 300,
"details": []
}
]
}
有没有办法消除和获取唯一需要的？
使用with过滤方法后，得到的json格式为
.prefetch_related(
Prefetch('session', queryset=Session.objects.filter(details__type=value)),
Prefetch('session__details', queryset=Details.objects.all().order_by('id'))),
)
[
{
"location": "Loc 3",
"session": [
{
"start": "2021-01-01",
"counts": 500,
"details": [
{
"id": 15,
"length_max": 19,
"length_min": 16,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
}
]
},
{
"location": "Loc 4",
"session": [
{
"start": "2021-01-02",
"counts": 800,
"details": [
{
"id": 1,
"length_max": 24,
"length_min": 18,
"length_avg": 25,
"length_std": 27,
"is_active": false,
"type": "cat"
},
{
"id": 4,
"length_max": 24,
"length_min": 18,
"length_avg": 25,
"length_std": 27,
"is_active": false,
"type": "cat"
},
{
"id": 16,
"length_max": 24,
"length_min": 18,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
},
{
"start": "2021-01-02",
"counts": 800,
"details": [
{
"id": 10,
"length_max": 29,
"length_min": 16,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
},
{
"id": 22,
"length_max": 29,
"length_min": 16,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
}
]
},
更新代码后的输出
[
{
"location": "Loc 1",
"session": [
{
"start": "2021-01-01",
"counts": 600,
"details": [
{
"id": 13,
"length_max": 21,
"length_min": 15,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
}
]
},
{
"location": "Loc 2",
"session": [
{
"start": "2021-01-01",
"counts": 300,
"details": [
{
"id": 14,
"length_max": 22,
"length_min": 16,
"length_avg": 16,
"length_std": 19,
"is_active": true,
"type": "dog"
}
]
}
]
},
{
"location": "Loc 3",
"session": [
{
"start": "2021-01-01",
"counts": 500,
"details": [
{
"id": 15,
"length_max": 19,
"length_min": 16,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
}
]
},
{
"location": "Loc 4",
"session": [
{
"start": "2021-01-02",
"counts": 800,
"details": [
{
"id": 16,
"length_max": 24,
"length_min": 18,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
},
{
"start": "2021-01-02",
"counts": 800,
"details": [
{
"id": 10,
"length_max": 29,
"length_min": 16,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
},
{
"id": 22,
"length_max": 29,
"length_min": 16,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
}
]
},
{
"location": "Loc 5",
"session": [
{
"start": "2021-01-02",
"counts": 400,
"details": [
{
"id": 17,
"length_max": 28,
"length_min": 19,
"length_avg": 16,
"length_std": 19,
"is_active": true,
"type": "dog"
}
]
},
{
"start": "2021-01-02",
"counts": 400,
"details": [
{
"id": 11,
"length_max": 38,
"length_min": 28,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
},
{
"id": 23,
"length_max": 38,
"length_min": 28,
"length_avg": 16,
"length_std": 19,
"is_active": true,
"type": "dog"
}
]
}
]
},
{
"location": "Loc 6",
"session": [
{
"start": "2021-01-02",
"counts": 450,
"details": [
{
"id": 18,
"length_max": 35,
"length_min": 26,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
},
{
"start": "2021-01-02",
"counts": 450,
"details": [
{
"id": 12,
"length_max": 15,
"length_min": 13,
"length_avg": 16,
"length_std": 19,
"is_active": true,
"type": "dog"
},
{
"id": 24,
"length_max": 15,
"length_min": 13,
"length_avg": 16,
"length_std": 19,
"is_active": false,
"type": "dog"
}
]
}
]
}
]
解决方案
试试这个：
.prefetch_related(
Prefetch('session', queryset=Session.objects.filter(details__type=value)),
Prefetch('session__details', queryset=Details.objects.filter(type=value).order_by('id'))),
)
这将删除所有没有具有您要查找的值的详细信息的会话，并将按其 ID 对这些会话中的所有详细信息进行排序。
推荐阅读
- python-3.x - 我们可以将 csv 列数据合并到具有特定 ID 的记事本现有数据集吗？
- javascript - 我可以用 css 或 javascript 伪造我的屏幕宽度吗？
- java - 我的 CSES 问题静态范围和查询代码中的错误是什么
- django - 如何使用 django manager 的原始方法执行更新操作？
- git - 带有 ssh 地址的 Git 克隆失败
- mysql - UnicodeDecodeError：“utf-8”编解码器无法解码位置 180 中的字节 0xa0：无效的起始字节
- blazor - 使用 Blazor 时如何禁用 ENTER 键按下提交按钮
- java - 为什么 Eclipse 删除“自动构建”标志？
- image - 将原始DICOM图像转换为NIfTI图像后，蒙版无法匹配原始图像
- css - ReactJS 路由器 - 覆盖默认字体、大小写和颜色