DRF中GET请求返回指定行及前后相邻行的实现问询（含特殊字段处理）

Alright, let's work through how to implement this adjacent rows feature in Django Rest Framework, while accounting for the is_thumbnail field constraint you mentioned. Here's a step-by-step solution:

1. Understand the Core Requirements #

We need to:

• Handle GET requests targeting a specific video via uid
• When with_adjacent=true is present in query params, return the target video plus its previous and next rows (sorted by file_created_time descending)
• Account for the is_thumbnail field, especially if switching to id as the primary key

2. Adjust Your View (Using ViewSet Example) #

Assuming you're using a ReadOnlyModelViewSet (the logic works for RetrieveAPIView too), override the retrieve method to add the adjacent row logic:

from rest_framework import viewsets
from rest_framework.response import Response
from django.db.models import Q
from .models import Video
from .serializers import VideoSerializer

class VideoViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Video.objects.all()
    serializer_class = VideoSerializer
    lookup_field = 'uid'  # Use uid as the lookup field instead of default id

    def retrieve(self, request, *args, **kwargs):
        # Get the target video instance
        target_video = self.get_object()
        with_adjacent = request.query_params.get('with_adjacent', 'false').lower() == 'true'

        # Default case: return just the target video
        if not with_adjacent:
            serializer = self.get_serializer(target_video)
            return Response(serializer.data)
        
        # Build base queryset: match the target's is_thumbnail status (adjust if needed)
        # This ensures we only compare against videos of the same type (thumbnail vs non-thumbnail)
        base_qs = Video.objects.filter(is_thumbnail=target_video.is_thumbnail)
        target_time = target_video.file_created_time
        target_id = target_video.id  # Use id if it's your primary key; swap to uid if needed

        # Get previous video (comes before target in descending sort: newer or same time with higher id)
        previous_video = base_qs.filter(
            Q(file_created_time__gt=target_time) |
            Q(file_created_time=target_time, id__gt=target_id)
        ).order_by('-file_created_time', '-id').first()

        # Get next video (comes after target in descending sort: older or same time with lower id)
        next_video = base_qs.filter(
            Q(file_created_time__lt=target_time) |
            Q(file_created_time=target_time, id__lt=target_id)
        ).order_by('-file_created_time', '-id').last()

        # Structure the response (customize this to fit your API needs)
        response_data = {
            'target': self.get_serializer(target_video).data,
            'adjacent': {
                'previous': self.get_serializer(previous_video).data if previous_video else None,
                'next': self.get_serializer(next_video).data if next_video else None
            },
            # Optional: return all three (or two/one) in sorted order
            'sorted_results': self.get_serializer([
                *([previous_video] if previous_video else []),
                target_video,
                *([next_video] if next_video else [])
            ], many=True).data
        }

        return Response(response_data)

3. Handle Primary Key Switch to id #

If you switch your primary key to id (the default AutoField):

• Remove the lookup_field = 'uid' line unless you still want to allow lookup via uid (add a custom lookup if needed)
• The logic above already uses id to handle ties in file_created_time, which avoids ambiguous adjacent rows when multiple videos share the same creation time
• The base_qs filter ensures we don't mix thumbnail and non-thumbnail videos in adjacent results (adjust this filter if your business logic requires different behavior)

4. Serializer Setup #

Your serializer can be a standard ModelSerializer—no special changes needed unless you want to exclude certain fields:

from rest_framework import serializers
from .models import Video

class VideoSerializer(serializers.ModelSerializer):
    class Meta:
        model = Video
        fields = '__all__'  # Or list specific fields like ['uid', 'id', 'file_created_time', 'is_thumbnail']

5. Test the Endpoint #

Use your example URL to test:
https://<domain>/api/videos/cbf02e8c-b2f5-4cd8-b3ec-87417eae2f7d?with_adjacent=true

The response will include the target video, its previous/next adjacent rows (if they exist), and a sorted list of all relevant videos.

Key Notes #

• Time Tie Handling: Using id alongside file_created_time ensures we have a consistent sort order even when multiple videos share the same creation time.
• Flexibility: Adjust the base_qs filter if you don't need to match is_thumbnail status (e.g., remove the filter to include all videos regardless of thumbnail status).
• Edge Cases: The code handles cases where the target video is the first or last in the sorted list (returning None for missing adjacent rows).

内容的提问来源于stack exchange，提问作者brewcrazy