Retrieving multiple rows from seperate model in django relationships

Question

I've been struggling with this puzzled for a few hours. Here's a schema of what I'm trying to do.

I have a user model and a profile model, it's a one-to-one relationship, but I'd like to be able to query a user and retrieve all the email addresses (from the User model) for users that share the same company (from the Profile Model). To be fair, my understanding of django is limited, but I went through the serializer relations guide and tried my hands at most approach described there, to no avail. At this point, I'm not even sure I'm on the right path.

So, my understanding of it is

1. From the user, I need to fetch the profile (a source='profile' approach may work)
2. From that profile, I need to retrieve the company
3. From that company, I need to retrieve all the user_id that belongs to that company
4. From those user_ids, I need to lookup the email fields of all those users
5. I need to also filter out the email address of the user making the request

Does this make any sense? At this point, I'm trying to accomplish all of that from the serializer, but was unsuccessful. Here are some snippets of code I tried, but I doubt any of them will point towards any form of solution, unfortunately.

class TeamEmailsSerializer(serializers.ModelSerializer):
    class Meta:
        model = User
        fields = ['email']

class UserSerializer(serializers.ModelSerializer):

    ...

    # only one of them was present at a time, but none gave any promising results

    test_one = Profile.objects.filter(source=profile.company.id).values_list('user_id', flat=True)

    test_one = serializers.RelatedField(source='profile.company.id', read_only=True)

    test_one = TeamEmailsSerializer(many=True, read_only=True)

    test_one = serializers.PrimaryKeyRelatedField(source='email', queryset=User.objects.filter())

    class Meta:
        model = User
        fields = (
            'test_one'
        ) 

I'm grateful for any clue that may lead towards a solution.

Answer 1

First, you should add company FK on your user as well, it will make things much easier for you.

Then you can define a new method on User model:

class User(AbstractBaseUser):
  ...

  def other_users_emails(self):
    return self.company.users.exclude(pk=self.id).values_list('email', flat=True)

Then in your serializer add 'other_users_emails' to the fields list.

Alternatively you could modify to_representation method on your serializer and add 'other_users_emails' attribute directly there