<p>Using Django's built in models, how would one create a triple-join between three models.</p>

<p>For example:</p>

<ul>
<li>Users, Roles, and Events are the models.</li>
<li>Users have many Roles, and Roles many Users.  (ManyToMany)</li>
<li>Events have many Users, and Users many Events. (ManyToMany)</li>
<li>But for any given Event, any User may have only one Role.</li>
</ul>

<p>How can this be represented in the model?</p>

[{'answer_id': 73153, 'author': 'Eli Courtwright', 'author_id': 1694, 'author_profile': 'https://Stackoverflow.com/users/1694', 'pm_score': 2, 'selected': False, 'text': '<p>I\'d recommend just creating an entirely separate model for this.</p>\n\n<pre><code>class Assignment(Model):\n    user = ForeignKey(User)\n    role = ForeignKey(Role)\n    event = ForeignKey(Event)\n</code></pre>\n\n<p>This lets you do all the usual model stuff, such as</p>\n\n<pre><code>user.assignment_set.filter(role__name="Chaperon")\nrole.assignment_set.filter(event__name="Silly Walkathon")\n</code></pre>\n\n<p>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 (<a href="http://docs.djangoproject.com/en/dev/topics/db/models/#overriding-predefined-model-methods" rel="nofollow noreferrer">http://docs.djangoproject.com/en/dev/topics/db/models/#overriding-predefined-model-methods</a>) or using signals (<a href="http://docs.djangoproject.com/en/dev/topics/signals/" rel="nofollow noreferrer">http://docs.djangoproject.com/en/dev/topics/signals/</a>)</p>\n'}, {'answer_id': 76221, 'author': 'Aaron Maenpaa', 'author_id': 2603, 'author_profile': 'https://Stackoverflow.com/users/2603', 'pm_score': 0, 'selected': False, 'text': "<p>I'd model Role as an association class between Users and Roles, thus,</p>\n\n<pre><code>class User(models.Model):\n     ...\n\nclass Event(models.Model):\n     ...\n\nclass Role(models.Model):\n     user = models.ForeignKey(User)\n     event = models.ForeignKey(Event)\n</code></pre>\n\n<p>And enforce the one role per user per event in either a manager or SQL constraints.</p>\n"}, {'answer_id': 77898, 'author': 'zuber', 'author_id': 9812, 'author_profile': 'https://Stackoverflow.com/users/9812', 'pm_score': 5, 'selected': True, 'text': '<p><strong>zacherates</strong> writes:</p>\n\n<blockquote>\n  <p>I\'d model Role as an association class between Users and Roles (...)</p>\n</blockquote>\n\n<p>I\'d also reccomed this solution, but you can also make use of some syntactical sugar provided by Django: <a href="http://docs.djangoproject.com/en/dev/topics/db/models/#extra-fields-on-many-to-many-relationships" rel="noreferrer">ManyToMany relation with extra fields</a>.</p>\n\n<p>Example:</p>\n\n<pre><code>class User(models.Model):\n    name = models.CharField(max_length=128)\n\nclass Event(models.Model):\n    name = models.CharField(max_length=128)\n    members = models.ManyToManyField(User, through=\'Role\')\n\n    def __unicode__(self):\n        return self.name\n\nclass Role(models.Model):\n    person = models.ForeignKey(User)\n    group = models.ForeignKey(Event)\n    date_joined = models.DateField()\n    invite_reason = models.CharField(max_length=64)\n</code></pre>\n'}, {'answer_id': 16290256, 'author': 'Brent Washburne', 'author_id': 584846, 'author_profile': 'https://Stackoverflow.com/users/584846', 'pm_score': 0, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>def event_users(event_name):\n    return User.objects.filter(roles__events__name=event_name)\n</code></pre>\n\n<p>this might create the following SQL:</p>\n\n<pre><code>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"\n</code></pre>\n\n<p>The INNER JOINs can be very slow compared with LEFT JOINs.  An even faster query can be found under gimg1\'s answer: <a href="https://stackoverflow.com/questions/10257433/mysql-query-to-join-three-tables#10257569">Mysql query to join three tables</a></p>\n\n<pre><code>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"\n</code></pre>\n\n<p>However, you will need to use a custom SQL query: <a href="https://docs.djangoproject.com/en/dev/topics/db/sql/" rel="nofollow noreferrer">https://docs.djangoproject.com/en/dev/topics/db/sql/</a></p>\n\n<p>In this case, it would look something like:</p>\n\n<pre><code>from django.db import connection\ndef event_users(event_name):\n    cursor = connection.cursor()\n    cursor.execute(\'select U.name from user U, roles R, event E\' \\\n                   \' where U.id=R.user_id and R.event_id=E.id and E.name="%s"\' % event_name)\n    return [row[0] for row in cursor.fetchall()]\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/72945', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/8507/']