FWIW, I'm using django-mptt to help me traverse my tree of self-referencing models, as well as run complex aggregates based on them.
http://django-mptt.github.com/django-mptt/index.html Be aware that the API has changed a lot from 0.3 (which is easily found around the web) to 0.5 (which will require some more googling or cloning for a specific commit from github). The key advantage of using this library is that it takes care of adding (and handling) a tree_id column for your MPTTModels, which you can then use to filter aggregates and the like. So do check it out! As an anedoctal aside, In my use case I have AccountGroup, Account and Entry, and my problem could only be solved through a HUMONGOUS query (62 lines total) that returns the aggregate sum for Entries for a given AccountGroup for a given month, including subtotals for each level of the AccountGroup and each Account. So be ready to sharpen up on your SQL skills if you're looking for something similar! Cheers, AT On Tue, Aug 30, 2011 at 2:10 PM, graeme <[email protected]> wrote: > > > On Aug 30, 7:25 pm, Stuart <[email protected]> wrote: > > On Aug 30, 3:10 am, graeme <[email protected]> wrote: > > > > > I think I need to restate this question with the actual models. > > > > Apologies in advance for answering a question you didn't ask, but is > > your subcategory model truly representing something different from > > your category model? I think the problems you are having with the > > query may be indicative of a problem with your model definitions / > > database layout. > > > > Unless I have the wrong idea about what you are trying to accomplish, > > I recommend a single Category model with a parent field defined as a > > back-reference to itself. Something like this: > > > > parent = models.ForeignKey('self', blank=True, null=True, > > related_name='child') > > > > This approach would eliminate the SubCategory and SubCategoryText > > models. The CategoryText looks about right as it is. > > > > I'm not clear on precisely what you are trying to accomplish with your > > query, but perhaps rethinking your models will make the query easier. > > I must admit I never thought of structuring the database like that. I > had to think about it quite a bit. > > What my query does is give me a list of subcategories, ordered by > category, and then by the number of places in the category, and > annotates each subcategory with the number of places in it. > > Having a single category model might simplify the query, but as I want > the page to show something like: > > CATEGORY > Subcategory One > Subcategory Two > CATEGORY TWO > Subcategory Three > etc. > > I am still going to have to, at least, join the category table to > itself, instead of joining the subcategory table to the category > table. I have not yet figured out if this will give me easier access > to the CategoryText of a parent category (in that case it may simplify > things a lot). > > If I am iterating over subcategories (which will now just be those > categories without a parent), will I have easier access to the > CategoryText of the parent. > > The disadvantage is that the current structure enforces, at the > database level, that Places can only belong to a sub-category, and > that the hierarchy is only two levels deep. I would have to move some > validation out of the database if I did this. > > My current approach, or adding the necessary attributes in Python > works. It does help, as I just found out, that I can add attributes to > the Categories while looping over SubCategoryTexts (i.e. > subcategorytextobject.subcategory.category.foo = bar works). It is > still an ugly hack, though. > > > > > Hope that helps, > > > > --Stuart > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/django-users?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
