Django 4.0 - Trying to run a query based on result from another query-CodePudding

guys!

Here's what I'm trying to do: I have a User model, a LinkedOrganization model, an Organization model that looks like this:

class User(AbstractBaseUser, PermissionsMixin):
    email = models.EmailField(db_index=True, unique=True)
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    mobile = models.CharField(max_length=12)

    is_staff =  models.BooleanField(default=False)
    is_active = models.BooleanField(default=True)
    is_superuser = models.BooleanField(default=False)

    objects = UserManager()

    USERNAME_FIELD = 'email'
    REQUIRED_FIELDS = ['first_name', 'last_name', 'mobile']

    class Meta:
        verbose_name = 'User'
        verbose_name_plural = 'Users'

# Organization fields
class Organization(models.Model):
    organization_name = models.CharField(max_length=50, unique=True)
    
    def __str__(self):
        return self.organization_name

class LinkedOrganization(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='linked_user')
    organization = models.ForeignKey(Organization, on_delete=models.CASCADE, related_name='linked_organization')
    is_manager = models.BooleanField(default=False)
    is_admin = models.BooleanField(default=False)
    class Meta:
        unique_together = (
            ("user", "organization")
        )

In my API-view I'm trying to return all the users that are linked to an organization that the requester is a manager of.

Now, one requester can be a manager of 1 or more organizations, and I need to get all the users linked to those organizations. Meaning I need it to be sort of 'is member of organization A' OR 'is member of organization B', and iterate through all the organizations. Is it possible to do this with a Queryset in Django?

I realized when I wrote this that I could just get the organizations and use ReverseObjectManager to get the users of the organizations and iterate through those and make the dictionaries I need in Python, but if getting a query and use the result in a queryset is a function of Django that would be easier.

CodePudding user response：

You may benefit from a slightly different database schema, not just for this query but in general. Consider the following:

class User(AbstractBaseUser, PermissionsMixin):
    email        = models.EmailField(db_index=True, unique=True)
    first_name   = models.CharField(max_length=50)
    last_name    = models.CharField(max_length=50)
    mobile       = models.CharField(max_length=12)
    organization = models.ForeignKey('Organization', on_delete=models.CASCADE, related_name='user_organization')
    is_staff     = models.BooleanField(default=False)
    is_active    = models.BooleanField(default=True)
    is_superuser = models.BooleanField(default=False)

    objects = UserManager()

    USERNAME_FIELD = 'email'
    REQUIRED_FIELDS = ['first_name', 'last_name', 'mobile']

    class Meta:
        verbose_name = 'User'
        verbose_name_plural = 'Users'

# Organization fields
class Organization(models.Model):
    organization_name = models.CharField(max_length=50, unique=True)
    manager           = models.ForeignKey(User, etc...)
    
    def __str__(self):
        return self.organization_name

In this scenario, every user has an organization, and every organization has a manager. Simple. Also note that I referenced the 'Organization' in the foreign key field as a string, since it is declared above where the Organization model is defined. With that, you can query like so:

managed_users = User.objects.filter(user_organization__manager=request.user)

A common practice is to create a profile model for the user, where additional fields like their organization, contact info, etc can be added. In that case you'd have something like this:

class Profile(models.Model):
    user         = models.OneToOneField(User, on_delete=models.CASCADE)
    organization = models.ForeignKey(Organization, etc...)