How to djanog orm filter reverse foreign key

user_11872467 0
Viewed 53

When I use django, I created two models.

class GroupModel(models.Model):
    group_name = models.CharField(max_length=2, null=False, default="A")


class MemberModel(models.Model):
    name = models.CharField(max_length=8, null=False, default="")
    group = models.ForeignKey(
        to=GroupModel,
        on_delete=models.CASCADE,
        related_name="members",
        db_column="member",
    )
    isActive = models.BooleanField(null=False, default=False)

The contents lisk:

[{'group_name': 'GROUP_A',
  'id': 1,
  'members': [{'isActive': False, 'name': 'j'},
              {'isActive': True, 'name': 'b'},
              {'isActive': True, 'name': 'y'}]},
 {'group_name': 'GROUP_B',
  'id': 2,
  'members': [{'isActive': True, 'name': 'f'},
              {'isActive': True, 'name': 'i'},
              {'isActive': True, 'name': 'y'}]}]

Now, I want to get the queryset wiht isActive=False

queryset = GroupModel.objects.filter(members__isActive=False).distinct()

but the resutl is

[{'group_name': 'GROUP_A',
  'id': 1,
  'members': [{'isActive': False, 'name': 'j'},
              {'isActive': True, 'name': 'b'},
              {'isActive': True, 'name': 'y'}]}]

How I can get the expected result list

[{'group_name': 'GROUP_A',
  'id': 1,
  'members': [{'isActive': False, 'name': 'j'}]}]

UPDATE

I got the result of the first question using prefetch_related.

I changed the MemberModel

class MemberModel(models.Model):
    name = models.CharField(max_length=8, null=False, default="")
    group = models.ForeignKey(
        to=GroupModel,
        on_delete=models.CASCADE,
        related_name="members",
        db_column="member",
    )
    isActive = models.BooleanField(null=False, default=False)
    country = models.CharField(max_length=8, null=False, default="CN")

I want to get the groups with isActive=False and country=CN

queryset = GroupModel.objects.prefetch_related(
    Prefetch("members", MemberModel.objects.filter(isActive=False, country="CN"))
).distinct()

I got the result

[{'group_name': 'GROUP_A', 'id': 1, 'members': []},
 {'group_name': 'GROUP_B', 'id': 2, 'members': []},
 {'group_name': 'GROUP_C',
  'id': 3,
  'members': [{'country': 'CN', 'isActive': False, 'name': 'ENWK'},
              {'country': 'CN', 'isActive': False, 'name': 'LKMP'}]}]

But I want to result is:

[{'group_name': 'GROUP_C',
  'id': 3,
  'members': [{'country': 'CN', 'isActive': False, 'name': 'ENWK'},
              {'country': 'CN', 'isActive': False, 'name': 'LKMP'}]}]

What can I do?

foreign-keys orm django python
2 Answers

I think you should use MemberModel instead on GroupModel class in your queryset

queryset = MemberModel.objects.filter(isActive=False).distinct()
for objects in queryset:
    print(objects.name)

Thank you

user_129422840

You can filter the relation as well with a Prefetch object [Django-doc]:

from django.db.models import Prefetch

queryset = GroupModel.objects.filter(
    members__isActive=False
).prefetch_related(
    Prefetch('members', MemberModel.objects.filter(isActive=False))
).distinct()
user_675790
Related