How to create a triple-join table with Django
Using Django's built in models, how would one create a triple-join between three models.
- Users, Roles, and Events are the models.
- Users have many Roles, and Roles many Users. (ManyToMany)
- Events have many Users, and Users many Events. (ManyToMany)
- But for any given Event, any User may have only one Role.
How can this be represented in the model?
Asked by: Chester190 | Posted: 28-01-2022
I'd model Role as an association class between Users and Roles (...)
I'd also reccomed this solution, but you can also make use of some syntactical sugar provided by Django: ManyToMany relation with extra fields.
Answered by: Walter940 | Posted: 01-03-2022
class User(models.Model): name = models.CharField(max_length=128) class Event(models.Model): name = models.CharField(max_length=128) members = models.ManyToManyField(User, through='Role') def __unicode__(self): return self.name class Role(models.Model): person = models.ForeignKey(User) group = models.ForeignKey(Event) date_joined = models.DateField() invite_reason = models.CharField(max_length=64)
I'd recommend just creating an entirely separate model for this.
class Assignment(Model): user = ForeignKey(User) role = ForeignKey(Role) event = ForeignKey(Event)
This lets you do all the usual model stuff, such as
user.assignment_set.filter(role__name="Chaperon") role.assignment_set.filter(event__name="Silly Walkathon")
The only thing left is to enforce your one-role-per-user-per-event restriction. You can do this in the Assignment class by either overriding the save method (http://docs.djangoproject.com/en/dev/topics/db/models/#overriding-predefined-model-methods) or using signals (http://docs.djangoproject.com/en/dev/topics/signals/)Answered by: Ada224 | Posted: 01-03-2022
I'd model Role as an association class between Users and Roles, thus,
class User(models.Model): ... class Event(models.Model): ... class Role(models.Model): user = models.ForeignKey(User) event = models.ForeignKey(Event)
And enforce the one role per user per event in either a manager or SQL constraints.Answered by: Brad974 | Posted: 01-03-2022
While trying to find a faster three-table join for my own Django models, I came across this question. By default, Django 1.1 uses INNER JOINs which can be slow on InnoDB. For a query like:
def event_users(event_name): return User.objects.filter(roles__events__name=event_name)
this might create the following SQL:
SELECT `user`.`id`, `user`.`name` FROM `user` INNER JOIN `roles` ON (`user`.`id` = `roles`.`user_id`) INNER JOIN `event` ON (`roles`.`event_id` = `event`.`id`) WHERE `event`.`name` = "event_name"
The INNER JOINs can be very slow compared with LEFT JOINs. An even faster query can be found under gimg1's answer: Mysql query to join three tables
SELECT `user`.`id`, `user`.`name` FROM `user`, `roles`, `event` WHERE `user`.`id` = `roles`.`user_id` AND `roles`.`event_id` = `event`.`id` AND `event`.`name` = "event_name"
However, you will need to use a custom SQL query: https://docs.djangoproject.com/en/dev/topics/db/sql/
In this case, it would look something like:
Answered by: Dominik943 | Posted: 01-03-2022
from django.db import connection def event_users(event_name): cursor = connection.cursor() cursor.execute('select U.name from user U, roles R, event E' \ ' where U.id=R.user_id and R.event_id=E.id and E.name="%s"' % event_name) return [row for row in cursor.fetchall()]
python - How to describe m2m triple-join table in model (Django)
My question is pretty much the same as this question, except that ALL relationships should be many-to-many. I have the following classes in my models.py (somewhat simplified): class Profile(models.Model): # Extending the built in User model user = models.ForeignKey(User, unique=True) birthday = models.DateField() class Media(mo...
Still can't find your answer? Check out these communities...
PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python