Django order by pviot table column

Question

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}}

Answer 1

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.

Answer 2

Because you have not specified a related_name argument to the bucket FK field on BucketTask model, you can access the 'BucketTasktable from theBuckettable by usingbuckettask_set`.

So, you can do:

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('buckettask_set__order')
    return queryset