# One to many relationship with limit?

**URL:** <https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533>\
**Category:** Using the ORM\
**Created:** [April 25, 2024, 10:34am UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533 "2024-04-25T10:34:21Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![HiPhish](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/hiphish/32/16434_2.png) [@HiPhish](https://forum.djangoproject.com/u/HiPhish)\
**Post date:** [April 25, 2024, 10:34am UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/1 "2024-04-25T10:34:21Z")

</div>

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:

```python
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.

```sql
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:

```python
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:

```python
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.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [April 25, 2024, 12:05pm UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/2 "2024-04-25T12:05:24Z")

</div>

You wrote:

> [@HiPhish](#):
>
> `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.

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`.

---

<div class="post-metadata">

**Author:** ![HiPhish](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/hiphish/32/16434_2.png) [@HiPhish](https://forum.djangoproject.com/u/HiPhish)\
**Post date:** [April 25, 2024, 1:42pm UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/3 "2024-04-25T13:42:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [April 25, 2024, 2:57pm UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/4 "2024-04-25T14:57:39Z")

</div>

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`](https://docs.djangoproject.com/en/5.0/ref/models/fields/#django.db.models.ForeignKey.related_name) and [`related_query_name`](https://docs.djangoproject.com/en/5.0/ref/models/fields/#django.db.models.ForeignKey.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)

---

<div class="post-metadata">

**Author:** ![HiPhish](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/hiphish/32/16434_2.png) [@HiPhish](https://forum.djangoproject.com/u/HiPhish)\
**Post date:** [April 25, 2024, 3:36pm UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/5 "2024-04-25T15:36:28Z")

</div>

> [@KenWhitesell](#):
>
> When Django detects a reverse-reference naming conflict, it issues an error at startup and refuses to proceed.

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?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [April 25, 2024, 4:37pm UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/6 "2024-04-25T16:37:49Z")

</div>

Regarding triggers, there is a third-party package, [django-pgtrigger](https://django-pgtrigger.readthedocs.io/en/4.11.1/), 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?](https://forum.djangoproject.com/t/support-for-database-triggers/26005))

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.

---

<div class="post-metadata">

**Author:** ![HiPhish](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/hiphish/32/16434_2.png) [@HiPhish](https://forum.djangoproject.com/u/HiPhish)\
**Post date:** [April 25, 2024, 10:53pm UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/7 "2024-04-25T22:53:22Z")

</div>

> [@KenWhitesell](#):
>
> Regarding triggers, there is a third-party package, [django-pgtrigger](https://django-pgtrigger.readthedocs.io/en/4.11.1/), to allow you to create triggers in Django models.

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?

> [@KenWhitesell](#):
>
> Regarding those constraints, I don’t think there’s a way to do it through the ORM.

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?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [April 26, 2024, 1:03pm UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/8 "2024-04-26T13:03:40Z")

</div>

> [@HiPhish](#):
>
> If I wanted to do it in Python instead, what would be the best way to “hook in”?

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`.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [April 26, 2024, 1:18pm UTC](https://forum.djangoproject.com/t/one-to-many-relationship-with-limit/30533/9 "2024-04-26T13:18:46Z")

</div>

> [@HiPhish](#):
>
> Would this work or would I run into race conditions?

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