[Answered ]-Nested GROUP BY in django: returning Objects

I’m afraid I don’t know how to solve this problem with django’s querysets. There may not be a solution, or if there is it probably involves using undocumented features.

However, the python solution would do fine in most cases. Django would probably have to do something similar itself anyway.

It looks like there may be a slight issue with your current code. You should almost always sort your entries before using itertools.groupby – it’s a common trap, so much so that I’m surprised it doesn’t sort automatically. Groupby iterates through the iterable, and creates a new group every time the value of the key changes. So to fully group an iterable, you need to sort first by the key.

Here you could do the sorting using the database, and simply use groupby twice:

queryset = CitiesTable.objects.order_by('country', 'region_or_state')

countries_dict = {}

for country, group in groupby(queryset, lambda x: x.country):
    countries_dict[country] = {}
    for region, inner_group in groupby(group, lambda x: x.region_or_state):
        countries_dict[country][region] = list(inner_group)