Re: Combining queries? A "join" in Python?

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 <graeme.pietersz@gmail.com> wrote:

On Aug 30, 7:25 pm, Stuart <stu...@bistrotech.net> wrote:
> On Aug 30, 3:10 am, graeme <graeme.piete...@gmail.com> 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 django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
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 django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.