Only return users that are not already part of a Patient relationship

user_504377 0
Viewed 40

In my current implementation I can query for users by name. I would like however that the search results only contain users that do not appear as part of a Patient relationship for a certain therapist. Should I try to populate my user objects with their therapist? Or is there a way to filter users out that appear in Patient table with a certain therapist?

class User(AbstractUser): 
  is_therapist = models.BooleanField(default=False)
  is_patient = models.BooleanField(default=True)

  def __str__(self):
    return self.email

class Patient(models.Model):
  therapist = models.ForeignKey(User, related_name="patients", on_delete=models.CASCADE)
  user = models.ForeignKey(User, on_delete=models.CASCADE)

  complaints = models.TextField(blank=True, default="")

  class Meta:
      constraints = [
          models.UniqueConstraint(name='unique_patient', fields=['therapist', 'user'])
      ]

class UserViewSet(viewsets.ModelViewSet):
  serializer_class = UserSerializer
  def get_queryset(self):
      queryset = User.objects.all()
      full_name = self.request.query_params.get('full_name', None)
      therapist_id = self.request.query_params.get('therapist_id', None)
      if full_name is not None:
          queryset = User.objects.annotate(full_name=Concat('first_name', V(' '), 'last_name')).filter(full_name__icontains=full_name)
      return queryset

edit: I am only interested in users (patients) who are not in a Patient relationship for the currently logged in user (therapist_id).

django-rest-framework django python
1 Answers

You can query with:

User.objects.filter(patients=None)

If you only want therapists, you can thus combine this with:

User.objects.filter(is_therapist=True, patients=None)

This works because this contructs a LEFT OUTER query, and thus therapists that do not have a related Patient, will thus construct a row with NULLs at the Patient side. This thus constructs a query with:

SELECT app_name_user.*
FROM app_name_user
LEFT OUTER JOIN app_name_patient
WHERE app_name_patient.id IS NULL
  AND app_name_user.is_therapist

If you want to retrieve the Users or Patients that are not in a patient relationship with the logged in user, we can use .exclude(…):

User.objects.filter(
    is_patient=True
).exclude(patient__therapist=request.user)

or if you are interested in the Patient and not (directly) the User, we can work with:

Patient.objects.exclude(therapist=request.user)
user_675790
Related