Fetching nested child records in django framework

This project is being developed in python and django.

As per my requirement I want to querying all the products from the categories upto two to three level up...

My entity structure is as follows.

Category:
- Name
- ParentCategory

Product:
- ID
- Name
- Category

Here are sample records which I want to query.

Category:
 - Name: Apparel | Parent:None
 - Name: Shirt | Parent: Apparel
 - Name: TShirts | Parent: Apparel
 - Name: MaleTShirts | Parent:TShirts
 - Name: FemaleTShirts | Parent: TShirts
 - Name:Electornics | Parent:None

Produ开发者_Python百科ct:
 - ID:1 | Name:ABC | Category:MaleTShirt
 - ID:2 | Name:XYZ | Category:FemaleTShirt
 - ID:3 | Name:JKL | Category:Shirt

Problem is, user should access these products from any level in category. For example,

• When user selects category Apparel, product ABC, XYZ, and JKL should appear in the resultset.
• When user selects category TShirts, product ABC and XYZ should appear in the resultset.
• When user selects category MaleTShirts, only ABC should appear in the resultset.
• When user selects category FemaleTShirts, only XYZ should appear in the resultset.

Any idea how the model classes should be structured, and how should we query so that desired results can be achieved.

Any suggestions would be helpful. It would be good if code is also provided.

You can use something like django-mptt to store hierarchical data(your Categories model) in a database. It provides additional methods to retrieve all descendants of certain element. And using Product.objects.filter(category__in=...) you can then retrieve all products, related to selected category's descendants.

What about thinking an implementation of 2-interval graphs ?

i think your model look just fine with the fields that you have there; and for the query you can do (it's not tested):

list_categories = []

head_category = Category.objects.filter(parent_category=request.GET['category'])

while head_category:
     # Transform the Queryset to a list.
     head_category = [category.category for category in head_category]

     # Put all the new categories in the list
     for category in head_category:
         list_categories.append(category)

     # Get child categories of the current categories.
     child_category = Category.objects.filter(parent_category__in=head_category)
     head_category = child_category

# Get all product from those category list.
Product.objects.filter(category__parent_category__in=list_categories)

For hierarchical data, you probably want to start using django-treebeard.