ForeignKey creates restrictions in Django

Question

I am using Django to write a blog application, and I am trying to implement a hierarchical category structure. Each category has a "parent" ForeignKey pointing to the same category category. I want admins to add categories, and I want the interface to let them select the category of the parent category. However, I want to avoid “I-my-own-grandfather” situations, so I want to limit the available selection of categories to those who do not have the corresponding category as an ancestor.

Now I control this from the view:

parent_candidates = list(Category.objects.all()) pruned_parent_list = [cat for cat in parent_candidates if instance.id not in cat.getHierarchy()] 

where the instance is an editable category and getHierarchy () is a method for listing the ancestor identifiers.

There are a number of problems with this approach. In particular, it uses an extra database byte to get a list of all categories, and this forces me to write the selection mechanism to my template by going through pruned_parent_list to get the parameters when I really would like to just specify a widget.

Is there a better way to do this? I know that I can add custom validation on the internal server to prevent this, but why provide users with this option?

Answer 1

If I understand your predicament correctly, the problem itself is how you deal with which categories may be parents and which may not. One way to avoid these problems is to actually limit the level of categories that can become parents. For example, let's say you have the following categories:

• the Internet
  • Google
  • Yahoo
• Offline
  • MS Office
  • Openoffice

As I usually deal with this, I obviously have a parent_id FK in the category table. For root elements (Internet, Offline), the parent identifier will be 0. So, when you try to find the "parent categories" for the drop-down list, you need to decide how far they can hold the nesting. I basically limit this to the first level, so to select the categories that will be displayed in the drop-down list, you would do something like:

 parents = Category.objects.filter(parent_id=0) 

Now it’s obvious that this limits the approach a bit, but you can increase the level you want to include and develop some kind of visual identification system for the drop-down list in your template (including additional spaces or dashes for each level in the hierarchy or something else) .

In any case, sorry for the long answer, and hopefully this has somewhat affected your problem.

Answer 2

I had to deal with a category of arbitrary depth in SQL and does not seem to be very suitable for storing this type of data in normal form, as subqueries and / or several JOINs tend to be ugly extremely quickly.

This is almost the only case when I would go with some kind of wrong decision, namely to store categories in string form, subcategories separated by a separator. This makes database queries and other operations more trivial.

Category table

It looks something like this:

 id name 1 Internet 2 Internet/Google 3 Internet/Yahoo 4 Offline 5 Offline/MS Office/MS Excel 6 Offline/Openoffice 

Another solution is that depending on the expected use, you can implement a binary tree in the list of categories. This allows you to elegantly select category trees and parent / child relationships. However, it has a limitation that when inserting new categories the whole tree can be recounted and it is useful to know the approximate size of the tree.

In any case, hierarchical data in SQL is not trivial in itself, so whatever you do, you probably have to do some pretty specific user coding.

Answer 3

Take a look at django-treebeard .

Answer 4

"Is there a better way to do this?" Not really. Hierarchies in the relational model are complex. Nothing makes it easier than completely abandoning SQL.

"write the selection mechanism to my template by going through pruned_parent_list to get the options" - probably not optimal. This should happen in your view.

Answer 5

I'm not sure if this is better (interacting anyway), but ...

You can check hierarchical integrity while saving and increase the error if necessary.

Ideally, for this type of data, I would like to see an instance tree on the side. Or at least a complete ancestor in representing the details of an object. In both cases, you have already made the extra trip mentioned in the database.