Django ORM query with multiple joins
<p>I've been trying to come up with the django version of this query and I can't seem to get it.</p>

<pre><code>SELECT
  *
FROM answer a
LEFT JOIN groups g
  ON a.group_id = g.id
LEFT JOIN group_permissions gp
  ON g.id = gp.group_id
WHERE gp.user_id = '77777';
</code></pre>

<p>I've been trying something like this:</p>

<pre><code>answers = Answer.objects.filter(user_id=user_id).prefetch_related('group__grouppermissions')
</code></pre>

<p>Answers are associated to groups. Groups are associated to multiple users. Group permissions are associated to groups and users. </p>

<p>I'm sure I'm missing some relationship somewhere. </p>

<p>Models:</p>

<pre><code>class User(models.Model):
    id = models.BigAutoField(primary_key=True)
    username = models.CharField(max_length=255)

class Answer(models.Model):
    id = models.BigAutoField(primary_key=True)
    user = models.ForeignKey('User', models.DO_NOTHING)
    group = models.ForeignKey('Groups', models.DO_NOTHING)
    class Meta:
        managed = False
        db_table = 'answer'

class Groups(models.Model):
    id = models.BigAutoField(primary_key=True)
    name = models.CharField(unique=True, max_length=255)

    class Meta:
        managed = False
        db_table = 'groups'

class GroupPermissions(models.Model):
    id = models.BigAutoField(primary_key=True)
    group = models.ForeignKey('Groups', models.DO_NOTHING)
    user = models.ForeignKey('User', models.DO_NOTHING)
    role = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'group_permissions'
</code></pre>

<p>There is an implicit association django creates for you: <code>group_grouppermissions_set</code>. You can read about it in <a href="https://docs.djangoproject.com/en/dev/topics/db/examples/many_to_one/" rel="nofollow">docs</a> about many-to-one relationship.</p>

<p>So your query will look like something like this:</p>

<pre><code>answers = Answer.objects.\
    filter(group__grouppermissions_set__user_id=77777)
</code></pre>

<p>Also notice that django will create <code>LEFT OUTER JOIN</code>.</p>

<p>Then when you will confident that your query is working you can add optimization like <code>prefetch_related</code> to it with similar lookups. It depends on how deep you want to fetch from <code>Answer</code> model. </p>

python mysql django join orm