pythondjangodjango-modelsdjango-rest-frameworkdrf-queryset

Django order by pviot table column


I want to order tasks within a bucket based on the 'order' column in the 'bucket_task' table. Tasks and buckets have a many-to-many relationship. How can I achieve this?

My models:

class Bucket(AbstractModel):
    user = models.ForeignKey(User, related_name='buckets', on_delete=models.deletion.DO_NOTHING)
    unique_id = models.CharField(max_length=255, default=uuid.uuid4, unique=True)
    title = models.CharField(max_length=191, blank=True)
    order = models.IntegerField(default=1)



class Task(AbstractModel):
    user = models.ForeignKey(User, related_name='tasks', on_delete=models.deletion.DO_NOTHING)
    unique_id = models.CharField(max_length=255, default=uuid.uuid4, unique=True)
    title = models.CharField(max_length=191, blank=True)


class BucketTask(AbstractModel):
    bucket = models.ForeignKey(Bucket, on_delete=models.deletion.CASCADE)
    task = models.ForeignKey(Task, on_delete=models.deletion.CASCADE)
    order = models.IntegerField(default=1)

My view:

class BucketsView(generics.ListCreateAPIView):
    permission_classes = [IsAuthenticated]
    serializer_class = BucketSerializer
    model = Bucket

    def get_queryset(self):
        queryset = self.model.objects.filter(
            user=self.request.user,
            deleted_on__isnull=True
        ).prefetch_related(
            'tasks'
        ).select_related('user').order_by('order')
        return queryset

My Serializer:

class BucketSerializer(ModelSerializer):
    tasks = TaskSerializer(many=True, read_only=True)

    class Meta:
        model = Bucket
        fields = "__all__"
        read_only_fields = ['unique_id', 'tasks']
        extra_kwargs = {"user": {"required": False}}

Solution

  • You can specify this in a Prefetch object [Django-doc]:

    from django.db.models import Prefetch
    
    
    class BucketsView(generics.ListCreateAPIView):
        permission_classes = [IsAuthenticated]
        serializer_class = BucketSerializer
        model = Bucket
    
        def get_queryset(self):
            return (
                self.model.objects.filter(user=self.request.user, deleted_on=None)
                .prefetch_related(
                    Prefetch('tasks', Task.objects.order_by('buckettask__order'))
                )
                .select_related('user')
                .order_by('order')
            )

    Note: Using deleted_on__isnull=True [Django-doc] can be shortened to deleted_on=None, since Django inspects if the value is None, and if that is the case, __exact [Django-doc] acts as isnull=True.