Django: nested queries from non-nested models
I am trying to find the best way to get a nested menu from a set of non-nested models. Given the layout, something like this:
class Beverage(models.Model):
country = models.ForeignKey(Country,null=True,blank=True)
region = models.ForeignKey(Region,null=True,blank=True)
subregion = models.ForeignKey(SubRegion,null=True,blank=True)
in_stock = models.BooleanField()
...
The resulting menu will look something like this:
France
Region 1
Subregion 1
Subregion 2
Region 2
Subregion 3
Subregion 4
Spain
....
No country, region, or sub-region should appear on the menu unless there are drinks that are not in stock. Since a sub-region always belongs to a region and a region always belongs to a country, my initial approach was to nest the models themselves and only put SubRegion on Beverage. The region and country will always be known to the drink sub-region. Unfortunately, there are too many real exceptions to make this viable - fault with region but not sub-regions, etc. So I flattened the layout as above.
Now the question is how to get the menu out of this model layout. It's like a list of deeply nested querysets, this would be the way to go, but it seems computationally expensive and hard to code. Is there a cleaner way?
source to share
After many attempts, I believe I have found a working solution that uses very little LOC by creating a set of nested dictionaries and lists. I wanted to send real objects to the template, not just strings (basically as close as possible to the general queryset methodology). Generated dictionary form:
{
Country1:{
region1:[subregion1,subregion2],
region2:[subregion3,subregion4]
},
Country2: {
region3:[subregion5,subregion6],
region4:[subregion7,subregion8]
},
}
where each country, region and sub-area is a real object, not a string. Here's the business end (it's in the templatetag). Note that we check for the availability of resources on each iteration, only setting a dictionary or list item if something is in stock.
regionmenu = {}
for c in Country.objects.all() :
if Wine.objects.filter(country=c,inventory__gt=0).count() > 0 :
regionmenu[c] = {}
for r in c.region_set.all():
if Wine.objects.filter(country=c,region=r,inventory__gt=0).count() > 0 :
regionmenu[c][r] = []
for s in r.subregion_set.all():
if Wine.objects.filter(country=c,region=r,subregion=s,inventory__gt=0).count() > 0 :
regionmenu[c][r].append(s)
Dictionaries fit the needs perfectly, except you lose the sorting capability, so I'll have to figure out more for the alphabet.
To iterate through dicts in a template:
<ul>
{% for country, regions in regionmenu.items %}
<li>{{ country }}
<ul>
{% for region, subregions in regions.items %}
<li>{{ region }}
<ul>
{% for subregion in subregions %}
<li>{{ subregion }}</li>
{% endfor %}
</ul>
</li>
{% endfor %}
</ul>
</li>
{% endfor %}
</ul>
Since we passed in objects, not strings, I can now do a URL pivot, get bullets, etc. for each item at each level (removed in this example).
source to share
The process I have used in the past to solve a similar problem is to select all items with one query based on country, then region, then sub-region. Then you punch through the query result and store the variables pointing to the last ID you saw for the country and region. If the next country / region ID in the drink does not match the last ID, you keep the old list and start a new one. Here's some really rough / messy pythoncode to explain the idea:
beverages = Beverage.objects.order_by('country', 'region', 'subregion')
last_country = -1
menu = []
country_obj = None
for beverage in beverages:
if beverage.country_id != last_country:
if country_obj is not None:
if region_obj is not None:
if subregion_obj is not None:
region_obj['children'].append(subregion_obj)
country_obj['children'].append(region_obj)
menu.append(country_obj)
country_obj = {'name': beverage.country.name, 'children': []}
last_country = beverage.country_id
last_region = -1
region_obj = None
last_subregion = -1
subregion_obj = None
if beverage.region is None:
country_obj['children'].append(beverage)
else:
if beverage.region_id != last_region:
if region_obj is not None:
if subregion_obj is not None:
region_obj['children'].append(subregion_obj)
country_obj['children'].append(region_obj)
region_obj = {'name': beverage.region.name, 'children': []}
last_region = beverage.region_id
last_subregion = -1
subregion_obj = None
if beverage.subregion is None:
region_obj['children'].append(beverage)
else:
if beverage.subregion_id != last_subregion:
if subregion_obj is not None:
region_obj['children'].append(subregion_obj)
subregion_obj = {'name': beverage.subregion.name, 'children': []}
last_subregion = beverage.subregion_id
subregion_obj['children'].append(beverage)
if beverage.subregion is not None:
region_obj['children'].append(subregion_obj)
if beverage.region is not None:
country_obj['children'].append(region_obj)
menu.append(country_obj)
As you can probably tell, each of the levels has the same logic: check if the id has changed, if it adds the old x_obj and starts a new one. The last five lines are for handling the last drink, since you always keep the previous item during the current iteration (and there is no next iteration for the last item). This is very rough around the edges, but the process I have used only requires one request.
I edited to fix a few bugs I found when I finally got to get it running. It seems to work for my simple test cases.
source to share
Two ideas
- For your first approach to work, you can bind GenericForeignKeys to any country, region, or sub-region. Or what may ever be identifying the origin. Use
limit_choices_to
with Q objects to control what types can be added.
Code:
content_type = models.ForeignKey(ContentType)
object_id = models.PositiveIntegerField()
origin = generic.GenericForeignKey('content_type', 'object_id',
limit_choices_to = \
Q(name='contry', app_label='what ever is the name of the app')| \
Q(name='region', app_label='what ever is the name of the app')| \
Q(name='subregion', app_label='what ever is the name of the app')')))
-
Or my second idea: don't optimize the db query in the first place - use some caching.
You can first query only countries, loop over that set and query the regions of that country, and write the menu in different loops.
This results in many db hits, but the code is pretty simple.
Since you will not be doing this calculation with every request to the site, you should write the menu to a global variable. This calculation can be performed without a save or delete action in the models that form the menu. So you can control it by signaling .
But be careful: signals and globals only work in the scope of the process. But perhaps the web server spans multiple processes. Here, you can write the menu to a database or file and save the timestamps to check if it needs to be reloaded.
Of course, these ideas can be combined
source to share