Getting Django query list based on another table

Question

I have models as below:

Group Model

class Group(model.Model):
    name = models.CharField(max_length=100, null=True, blank=True)

GroupMember Model

class GroupMember(model.Model):
    group = models.ForeignKey(Group, on_delete=models.CASCADE, related_name='group_member_group')
    member = models.ForeignKey(User, on_delete=models.CASCADE, related_name='group_member_user')

Course Model

class Course(model.Model):
    name = models.CharField(max_length=100)

GroupCourse Model

class GroupCourse(model.Model):
    group = models.ForeignKey(Group, on_delete=models.CASCADE, related_name='course_group')
    course = models.ForeignKey(Course, on_delete=models.CASCADE, related_name='group_course')

CourseStaff Model

class CourseStaff(model.Model):
    course = models.ForeignKey(Course, on_delete=models.CASCADE, related_name='course_staff_course')
    staff = models.ForeignKey(User, on_delete=models.CASCADE, related_name='course_staff_user')

What I want is to get All the GroupMembers that does not exists in CourseStaff model for a specific course.

For example: I have total 5 members in Group A

• Member 1
• Member 2
• Member 3
• Member 4
• Member 5

I have total 2 course in Group A

• Course 1
• Course 2

Course 1 has two members:

• Member 1
• Member 2

Course 2 has two members:

• Member 2
• Member 3

What I want is to get Member 3-5 from GroupMember when querying for Course 1

Also get Member 1,4, 5 from GroupMember when querying for Course 2

How can I do that?

Thanks

Answer 1

"What I want is to get All the GroupMembers that does not exists in CourseStaff model for a specific course."

Your realtions between models are weak. You want to exclude GroupMembers that is no CourseStaff but in GroupMembers there is no relation with CourseStaff. You could do on course field of CourseStaff. But your Course model has no relation with any models. You need to add a field under the Course model to connect a relation.

Answer 2

This is what I did. It may not perfect, but it works.

course_staffs = CourseStaff.objects.filter(course_id=course_id).values_list('staff_id')
members = User.objects.filter(~Q(id__in=course_staffs), group_member_user__group_id=group_id)