Need help for defining foreign key based on value from another table

[Kevin Yu]

I'm working with a legacy database so I have to set managed=False in the model. Here's the 3 related tables:

class Branches(models.Model):

    name = models.CharField(max_length=128)

    branchpoint_str = models.CharField(max_length=255)

    dev_lead_id = models.IntegerField(blank=True, null=True)

    source = models.CharField(max_length=255)

    state = models.CharField(max_length=255)

    kind = models.CharField(max_length=255)

    desc = models.TextField(blank=True, null=True)

    approved = models.IntegerField()

    for_customer = models.IntegerField()

    deactivated_at = models.DateTimeField(blank=True, null=True)

    created_at = models.DateTimeField(blank=True, null=True)

    updated_at = models.DateTimeField(blank=True, null=True)

    codb_id = models.IntegerField(blank=True, null=True)

    pm_lead_id = models.IntegerField(blank=True, null=True)

    version = models.CharField(max_length=20, blank=True, null=True)

    path_id = models.IntegerField(blank=True, null=True)

    branchpoint_type = models.CharField(max_length=255, blank=True, null=True)

    branchpoint_id = models.IntegerField(blank=True, null=True)

    class Meta:

        managed = False

        db_table = 'branches'

        verbose_name_plural = 'Branches'

class Projects(models.Model):

    id = models.AutoField(primary_key=True)

    name = models.CharField(max_length=40, primary_key=True)

    status = models.CharField(max_length=255)

    platform = models.CharField(max_length=255)

    enabled = models.IntegerField()

    path = models.CharField(max_length=128, blank=True, null=True)

    tag_prefix = models.CharField(max_length=64, blank=True, null=True)

    created_at = models.DateTimeField(blank=True, null=True)

    updated_at = models.DateTimeField(blank=True, null=True)

    codb_id = models.IntegerField(blank=True, null=True)

    template = models.CharField(max_length=64, blank=True, null=True)

    image_path = models.CharField(max_length=128, blank=True, null=True)

    repository_id = models.IntegerField(blank=True, null=True)

    number_scheme = models.CharField(max_length=32)

    special_dir = models.CharField(max_length=32, blank=True, null=True)

    project_family_id = models.IntegerField()

    class Meta:

        managed = False

        db_table = 'projects'

        verbose_name_plural = 'projects'

class BranchesProjects(models.Model):

    # project_id = models.IntegerField()

    # branch_id = models.IntegerField()

    project = models.ForeignKey(Projects, on_delete=models.CASCADE)

    branch = models.ForeignKey(Branches, on_delete=models.CASCADE)

    class Meta:

        managed = False

        db_table = 'branches_projects'

I have been able to do the join using raw(). However, the return object is rawqueryset. What I want is queryset so that I can use django-filter to process it. My current raw sql is like this:

Branches.objects.raw(

                '''SELECT br.id, br.name, br.created_at, br.updated_at,

                br.branchpoint_str, br.source

                FROM branches as br

                LEFT JOIN branches_projects as bp

                ON br.id = bp.branch_id 

                WHERE bp.project_id = "%s" AND source != "other"

                ORDER BY updated_at DES'''

 
My question is, is there a way to achieve the same result using Django's queryset? I've also explored  the idea of using the extra() in django extra but it doesn't really work for me.

[Kevin Yu]

I just found out on Django document, there's an example that's for manytomany, using 'through' when defining the foreign key. I think this is what i need.

Here's the example:

from django.db import models

class Person(models.Model):
    name = models.CharField(max_length=128)

    def __str__(self):              # __unicode__ on Python 2
        return self.name

class Group(models.Model):
    name = models.CharField(max_length=128)
    members = models.ManyToManyField(Person, through='Membership')

    def __str__(self):              # __unicode__ on Python 2
        return self.name

class Membership(models.Model):
    person = models.ForeignKey(Person, on_delete=models.CASCADE)
    group = models.ForeignKey(Group, on_delete=models.CASCADE)
    date_joined = models.DateField()
    invite_reason = models.CharField(max_length=64)