django multiple table query

Question

I have a form for filling out lessons that I want to limit who the students can select as their teacher to only confirmed connections. I have three models:

class User(AbstractBaseUser, PermissionsMixin):
    email = models.EmailField(max_length=254, unique=True)
    name = models.CharField(max_length=254, null=True, blank=True)

class Lesson(models.Model):
   user = models.ForeignKey(User, related_name='fencer', on_delete=models.SET_NULL, null=True, blank=True)
   teacher = models.ForeignKey(Fencer, related_name='instructor', on_delete=models.SET_NULL, null=True, blank=True)
   lesson_date = models.DateField(default="1900-01-01")
   title = models.CharField(max_length=100, null = True, blank=True)
   description = models.TextField(null=True, blank=True)

class Connection(models.Model):
    student = models.ForeignKey(User, related_name='student', on_delete=models.CASCADE, blank=True)
    teacher = models.ForeignKey(User, related_name='teacher', on_delete=models.CASCADE, blank=True)
    student_accepts = models.BooleanField(default=False)
    teacher_accepts = models.BooleanField(default=False)

    @property
    def connected(self):
        if self.student_accepts == True and self.teacher_accepts == True:
            return True
        else:
            return False

My form so far is:

class LessonForm(ModelForm):
    class Meta:
        model = models.Lesson
        #fields = ()
        fields = '__all__'

    def __init__(self, user, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.fields['teacher'].queryset = Users.objects.filter()  # the best I have so far

How do I filter the User model based on the link made in the Connection model? Maybe I'm overcomplicating this or is there a better way?

Thank you in advance

Answer 1

Found the answer in this other question on here about spanning models.

I had a hard time getting django to see the fields for some reason so I will keep this ugly version.

def __init__(self, user, *args, **kwargs): # will need to pass the user to the form when used
    super(LessonForm, self).__init__(*args, **kwargs)
    
    # reduce options to just the coaches that the student is connected with
    connected_teachers = Connection.objects.filter(Q(student=user) and (Q(student_accepts=True) and Q(teacher_accepts=True)))
    teachers = User.objects.filter(teacher__in=connected_teachers)
    self.fields['teacher'].queryset = teachers