Queryset for mutiple table joins

Question

class Organization(models.Model):
    """Organization Model"""
    name = models.CharField(max_length=100, blank=False)
    is_active = models.BooleanField(default=True)

class Department(models.Model):
    """Department Model"""
    org = models.ForeignKey(Organization, on_delete=models.PROTECT, related_name='Department_Org')
    name = models.CharField(max_length=100, blank=False)
    is_active = models.BooleanField(default=True)

class Project(models.Model):
    """Project Model"""
    dept = models.ForeignKey(Department, on_delete=models.PROTECT, related_name='Project_Dept', null=True)
    no = models.CharField(max_length=100, blank=False)
    name = models.CharField(max_length=255, blank=True)
    is_active = models.BooleanField(default=True)

class Subscription(models.Model):
    """Subscription Model"""
    MODULE_CHOICES = (
        ('ECM', 'Change Management'),
        ('DMS', 'Document Management'),
        ('AIM', 'Deviation Management'),
        ('PMS', 'Project Management'),
        ('ADM', 'Admin'),
        ('RPT', 'Reports'),
        ('MTS', 'My Tasks'),
    )
    project = models.ForeignKey(Project, on_delete=models.PROTECT, related_name='Subscription_Project')
    module = models.CharField(max_length=3, choices=MODULE_CHOICES)

class User(AbstractBaseUser, PermissionsMixin):
    """Custom User Model"""
    email = models.EmailField(max_length=255, unique=True)
    name = models.CharField(max_length=255)
    displayname = models.CharField(max_length=255, blank=True)
    fname = models.CharField(max_length=255, blank=True)
    lname = models.CharField(max_length=255, blank=True)
    is_active = models.BooleanField(default=True)
    is_staff = models.BooleanField(default=False)

class UserProjectSubscriptionRole(models.Model):
    """User Role Model"""
    user = models.ForeignKey(User, on_delete=models.PROTECT, related_name='UserProjectRole_user')
    project = models.ForeignKey(Subscription, on_delete=models.PROTECT, related_name='UserProjectRole_Module')
    role = models.ForeignKey(RoleType, on_delete=models.PROTECT, related_name="UserProjectRole_role")

I need to write queryset to get Organizations to which User is associated.

Answer 1

You can filter with:

Organization.objects.filter(
    Department_Org__Project_Dept__UserProjectRole_Module__user=my_user
)

However the related_name=… values ^[Django-doc] are cumbersome: these are names to access the related objects that point to an object. Your UserProjectSubscriptionRole is also a junction model of a ManyToManyField. You can rewrite the modeling to:

class Department(models.Model):
    org = models.ForeignKey(Organization, on_delete=models.PROTECT, related_name='departments')
    # …

class Project(models.Model):
    dept = models.ForeignKey(Department, on_delete=models.PROTECT, related_name='projects', null=True)
    # …

class Subscription(models.Model):
    project = models.ForeignKey(Project, on_delete=models.PROTECT, related_name='subscriptions')
    # …

class User(AbstractBaseUser, PermissionsMixin):
    # …
    projects = models.ManyToManyField(
        Project,
        trough='UserProjectSubscriptionRole',
        related_name='users'
    )

class UserProjectSubscriptionRole(models.Model):
    user = models.ForeignKey(User, on_delete=models.PROTECT, related_name='userprojectroles')
    project = models.ForeignKey(Subscription, on_delete=models.PROTECT, related_name='userprojectroles')
    role = models.ForeignKey(RoleType, on_delete=models.PROTECT, related_name='userprojectroles')

Then you can filter with:

Organization.objects.filter(
    departments__projects__users=my_user
)

This seems more readable and compact.