One to many relationship with limit?

Using Django Using the ORM
1

I have a one-to-many relationship of the parent-child kind and I would like to impost a limit on the number of children a parent can have. Let’s say I am selling a TODO list service where different price tiers can have different numbers of maximum items per list.

The models would look like this:

class TodoList(models.Model):
	"""A TODO list which can contain items"""
	title = models.CharField()
    max_items = models.PositiveIntegerField(blank=False, default=3)


class ListItem(models.Model):
    """An item of a TODO list."""
    text = models.TextField()
	parent = models.ForeignKey(TodoList, on_delete=models.CASCADE)

There are couple of things I want to achieve:

  • DB Constraint that there can be no more than max_items ListItem instances which have the same parent
  • Automatically delete items if the list’s max_items drops and there are superfluous list items; this would be a DB trigger which either deletes arbitrary items or items according to some condition (e.g. least recently edited)
  • Query the DB for maxed-out lists

For the last point I have an SQL query, but I don’t know how to translate it to Django’s ORM syntax.

SELECT id, max_items FROM todolist
 WHERE max_items = (SELECT count(parent_id) FROM listitem
                       WHERE parent_id = todolist.id)

I was thinking of something along these lines:

TodoList.objects.filter(max_items=ListItem.objects.filter(parent_id='???').count())

The problem is what should ??? be, how do I refer back to the “current” list in the query? I was able to make it work with this expression:

TodoList.objects.annotate(item_count=Count('listitem')).filter(max_items=F('item_count'))

Is this the proper way? It looks brittle because there might be different models named ListItem in different Python scripts.

2

You wrote:

What you’ve got here is right.

The use of the name "listitem" here works because this is how you’re models are defined - it’s a direct reference to the “lower-cased model name”. (This has nothing to do with the view in which it’s being used or any variables created or used in that view.)

You’re querying your TodoList model. The model with a foreign key to TodoList is named ListItem. By definition, the reference to the set of ListItem is either listitem_set or listitem. (The former when you’re operating on this as a queryset, the latter if you’re using it as a field reference in a query.)

So if that model were instead named TodoItems, the reference in the query would be todoitems.

3

OK, I understand that part, but what if these models were defined in separate files and there was another ListItem model in another file? How does Django know which listitem to reference? I know this is a contrived case, but I’m trying to understand how Django maps between Python classes, database tables and string names.

4

Fundamentally, it’s based on the ForeignKey definitions. If you have two pair of TodoList and ListItem models in different apps, then there’s no issue. Each ListItem model is related to its own TodoList model.

If you have two different ListItem models, each with a foreign key to the same TodoList model, then you could have an issue.

It’s not a contrivied case, and Django is set up to detect this. It’s one of the reasons for the related_name and related_query_name field parameters.

When Django detects a reverse-reference naming conflict, it issues an error at startup and refuses to proceed. (They’re error codes E304 and E305 defined in django.db.models.fields.related.RelatedField)

5

OK, that’s perfect. It means I don’t have to worry about accidental collisions. That answers the third questions. Is it possible to solve the other two questions using Django’s ORM as well?

6

Regarding triggers, there is a third-party package, django-pgtrigger, to allow you to create triggers in Django models. (I’ve never used it, so I can’t vouch for it. However, there are a couple people whose opinions I greatly respect that think highly of it, and so I wouldn’t hesitate to try it if I had the need. See the thread at Support for database triggers?)

Regarding those constraints, I don’t think there’s a way to do it through the ORM. There are a number of limitations on what you can do with constraints, and I have the feeling that this type of constraint wouldn’t fit within those restrictions.

7

I have already found that package, but unfortunately it only supports Postgres. If I wanted to do it in Python instead, what would be the best way to “hook in”? Override the save method? Use signals?

OK, that sucks. What’s the best alternative? I would guess overriding the save method of the list item to check how many children the parent list already contains, and if the capacity is full raise an error. Would this work or would I run into race conditions?

8

If there’s the chance that max_items might be arbitrarily lowered, then it’s at that location in the code that you would want to check for there being too many ListItem for a TodoList. (In other words, call your check function any place where max_items may be changed.)

Similar logic for the constraint. Add the code for the test in each location where entries may be added to ListItem.

9

If race conditions and data integrity are a concerns for you then then I don’t see a way around using triggers unfortunately.

1 Like