Queryset with collated/merged values-CodePudding

Let's say I have the following two models:

class Parent(models.Model):
    name = models.CharField(max_length=48)

class Child(models.Model):
    name = models.CharField(max_length=48)
    movement = models.ForeignKey(Parent, related_name='children')

And I have the following DRF generics.ListAPIView where I want to be able to search/filter on Child objects but actually return the related Parent objects:


class ParentSearchByChildNameView(generics.ListAPIView):
    """
    Return a list of parents who have a child with the given name
    """
    serializer_class = ParentSerializer

    def get_queryset(self):
        child_name = self.request.query_params.get('name')

        queryset = Child.objects.all()
        if child_name is not None:
            queryset = queryset.filter(name__contains=child_name)

        matched_parents = Parent.objects.filter(children__in=queryset).distinct()
        return matched_parents

Now, this works well for me. If a Parent object has 3 Child objects which all match the given "name" query_param, then I only get one Parent object back, which is what I want:

{
    "next": null,
    "previous": null,
    "results": [
        {
            "url": "URL to parent",
            "name": "Parent #1",
        }
    ]
}

However, what I also want is to indicate the matched Child object IDs within the result. If I may illustrate in JSON what I want:

{
    "next": null,
    "previous": null,
    "results": [
        {
            "url": "URL to parent",
            "name": "Parent #1",
            "matched_child": [1, 3, 7]
        }
    ]
}

Is this something I can do with built-in tools, without expensively and repeatedly hitting the database?

CodePudding user response：

Without making use of a database-specific feature, you can work with a Prefetch object [Django-doc]:

from django.db.models import Prefetch

class ParentSearchByChildNameView(generics.ListAPIView):
    """
    Return a list of parents who have a child with the given name
    """
    serializer_class = ParentSerializer

    def get_queryset(self):
        child_name = self.request.query_params.get('name')
        return = Parent.objects.filter(
            children__name__contains=child_name
        ).prefetch_related(
            Prefetch('children', Child.objects.filter(name=child_name), to_attr='matched_children')
        ).distinct()

For the serializer we then can work with a PrimaryKeyRelatedField [drf-doc]:

class ParentSerializer(serializers.ModelSerializer)
    matched_children = serializers.PrimaryKeyRelatedField(
        many=True,
        read_only=True
    )

    class Meta:
        model = Parent
        fields = ['url', 'name', 'matched_children']

CodePudding user response：

If you work with postgresql, you can work with an ArrayAgg expression [Django-doc]:

from django.contrib.postgres.aggregates import ArrayAgg

class ParentSearchByChildNameView(generics.ListAPIView):
    """
    Return a list of parents who have a child with the given name
    """
    serializer_class = ParentSerializer

    def get_queryset(self):
        child_name = self.request.query_params.get('name')
        return = Parent.objects.filter(
            children__name__contains=child_name
        ).annotate(matched_children=ArrayAgg('children__pk'))

In the serializer you thus add a ListField that will list the children with:

class ParentSerializer(serializers.ModelSerializer)
    matched_children = serializers.ListField(
        child=serializers.IntegerField(),
        read_only=True
    )

    class Meta:
        model = Parent
        fields = ['url', 'name', 'matched_children']