python - Django - 从values()函数返回子级列表？

Question

我正在构建一个 Django 应用程序并尝试返回我的 values() 字典中的对象列表。我的示例模型如下所示 -

class State(models.Model):
    StateID = models.AutoField(primary_key = True)
    StateName = models.CharField(max_length = 30, default='State Name')
    StateAbbreviation = models.CharField(max_length = 4, default='SN')

    Population = models.IntegerField(default = 0)

    NationID = models.ForeignKey(Nation, on_delete=models.CASCADE)



class City(models.Model):
    CityID = models.AutoField(primary_key = True)
    CityName = models.CharField(max_length = 30, default='City Name')

    Population = models.IntegerField(default = 0)

    StateID = models.ForeignKey(State, on_delete=models.CASCADE)

我想返回的是以下内容:

[{
   'StateName': 'California'
 , 'NationID__NationName': 'United States'
 , 'Population': 90000000
 , 'CityList': [{'CityName': 'Los Angeles', 'Population': 4000000},{'CityName': 'San Francisco', 'Population': 2000000}, ...more cities]
}, {
   'StateName': 'Texas'
 , 'NationID__NationName': 'United States'
 , 'Population': 50000000
 , 'CityList': [{'CityName': 'Austin', 'Population': 1000000},{'CityName': 'Dallas', 'Population': 2000000}, ...more cities]
}, 
...more states
]

现在，我很想使用下面的查询，但它当然不起作用，因为我不知道如何将列表返回到 values() 返回对象中。这可能吗？

StateList = State.objects.values('StateName', 'NationID__NationName', 'Population').annotate(
        CityList = City.objects.values('CityName', 'Population')
    )

我知道我可以执行类似下面的查询的操作，但是州信息(州名称、国家名称、州人口)将被复制到每个城市。

StateList = State.objects.values('StateName', 'NationID__NationName', 'Population', 'city__CityName', 'city__Population')

非常感谢!

Answer 1

SQL 查询不提供与您预期的输出类似的输出。相反，它提供平坦的输出。因此，我很乐意执行以下操作:

       from itertools import groupby
       cities = City.objects.values('StateID', 'Population', 'CityName')
       city_groups = {state: list(cities) for state, cities in groupby(cities, key=lambda x: x['StateID'])}
       states = State.objects.values('StateID', 'StateName', 'NationID__NationName', 'Population') 
       for state in states:
            state['cities'] = city_groups[state['StateID'])]
       print(states)