python - Django ORM 不同查询，其中顺序由带注释的字段完成，您需要不同的 ('id' )

Question

商家有位置。地点有位置坐标。我需要通过 API 调用 (DRF) 返回唯一商家列表，该列表按与其关联的任何地点的邻近距离进行过滤，并返回距离最近地点的距离值。现在我收到重复的信息(即，如果附近有多个商家地点，商家会多次返回)。

如果我尝试 annotate(distance=...).distinct('pk') 我会收到错误消息

django.db.utils.ProgrammingError: 
    SELECT DISTINCT ON expressions must match initial ORDER BY expressions
    LINE 1: SELECT COUNT(*) FROM (SELECT DISTINCT ON ("merchants_merchan

如果我添加 .order_by('pk') 我可以使用 .distinct('pk')，但是我无法按距离对返回的查询集进行排序.

这是我到目前为止所做的:

查询集

class MerchantQuerySet(models.QuerySet):    
    def nearby(self, latitude, longitude, proximity=None):
        """Get nearby Merchants.

        Custom queryset method for getting merchants associated with
        nearby places.

        Returns:
            A queryset of ``Merchant`` objects.

        """
        point = Point(latitude, longitude)

        # we query for location nearby for places first and then
        # annotate with distance to the same place
        return self.filter(
            places__location__distance_lte=(point, D(ft=proximity))).\
            annotate(distance=Distance(
                'places__location', point)).distinct()

型号

class Merchant(TimeStampedModel):

    name = models.CharField(max_length=255, verbose_name=_('Name'))
    description = models.TextField(
        blank=True,
        verbose_name=_('Description'),
    )
    logo = imagekitmodels.ProcessedImageField(
        max_length=512,
        upload_to=get_upload_path_for_model,
        processors=[ResizeToFill(300, 300)],
        format='PNG',
        options={'quality': 100},
        editable=True,
        null=True,
        blank=True,
        verbose_name=_('Company logo'),
        help_text=_('Image will be resized to 300x300px.')
    )
    categories = models.ManyToManyField(
        'categories.Category',
        blank=True,
        related_name='merchants',
        verbose_name=_('Categories'),
    )
    address = models.TextField(blank=True, verbose_name=_('Address'))
    contact = models.CharField(
        max_length=32,
        blank=True,
        verbose_name=_('Contact phone'),
    )
    url = models.URLField(blank=True, verbose_name=_('Site URL'))
    social_urls = ArrayField(
        models.URLField(blank=True),
        blank=True,
        null=True,
        verbose_name=_('Social URLs'),
    )
    budget_tips = models.TextField(
        blank=True,
        verbose_name=_('Budget tips'),
        help_text=_('Recommendations to determine merchant budget.'),
    )

    objects = query.MerchantQuerySet.as_manager()

    class Meta:
        verbose_name = _('Merchant')
        verbose_name_plural = _('Merchants')


class Place(TimeStampedModel):

    id = models.CharField(
        max_length=32,
        primary_key=True,
        unique=True,
        verbose_name=_('ID'),
        help_text=_('Forsquare ID of the venue.'),
    )
    merchant = models.ForeignKey(
        'merchants.Merchant',
        related_name='places',
        verbose_name=_('Merchant'),
        help_text=_('Merchant, business owner.'),
    )
    categories = models.ManyToManyField(
        'categories.Category',
        blank=True,
        related_name='places',
        verbose_name=_('Categories'),
    )
    name = models.CharField(
        max_length=255,
        blank=True,
        verbose_name=_('Name')
    )
    address = models.TextField(blank=True, verbose_name=_('Address'))
    contact = models.CharField(
        max_length=32,
        blank=True,
        verbose_name=_('Contact phone')
    )
    location = PointField(blank=True, null=True, verbose_name=_('Location'))

    objects = PlaceQuerySet.as_manager()

    class Meta:
        verbose_name = _('Place')
        verbose_name_plural = _('Places')

DRF View

class MerchantViewSet(

            mixins.ListModelMixin,
            mixins.RetrieveModelMixin,
            favorites_api_mixins.UserFavoritesMixin,
            viewsets.GenericViewSet,
    ):

        queryset = models.Merchant.objects.all()
        serializer_class = serializers.MerchantSerializer
        filter_backends = (MerchantOrderingFilter,
                           filters.DjangoFilterBackend,
                           filters.SearchFilter,
                           utils_filters.LocationDistanceFilter)

        ordering_fields = ('name', 'created', 'distance')
        ordering = ('-created')
        search_fields = ('name',)

utils_filters.LocationDistanceFilter

class LocationDistanceFilter(BaseFilterBackend):
    """Location distance filter.

    Class for filtering objects by distance. Takes three GET params ``lat``,
    ``long`` and ``dist``:

        .../?dist=300&lat=55.56&long=98.01

    """
    dist_param = 'dist'

    def get_filter_point(self, request):
        """Get filter point.

        Get point for filtering by distance to this point.

        Args:
            request (Request): A ``Request`` object.

        Returns:
            A ``Point`` object or None.

        Raises:
            Parse error if point is invalid.

        """
        latitude = request.query_params.get('lat')
        longitude = request.query_params.get('long')

        if latitude and longitude:
            try:
                latitude = float(latitude)
                longitude = float(longitude)
            except ValueError:
                raise ParseError(
                    'Invalid geometry string supplied for '
                    'latitude or longitude'
                )

            return Point(latitude, longitude)

        else:
            return None

    def filter_queryset(self, request, queryset, view):
        """Filter queryset.

        Filter queryset by ``lat``, ``long`` and ``dist` params. Queryset
        should have method ``nearby``.

        Args:
            request (Request): A ``Request`` object.
            queryset (QuerySet): A ``QuerySet`` object.
            view (ViewSet): Current API view instance.

        Returns:
            A query set of objects filtered by latitude, longitude
            and distance.

        """
        distance = request.query_params.get(self.dist_param)
        point = self.get_filter_point(request)

        if not point:
            return queryset

        return queryset.nearby(
            latitude=point.x,
            longitude=point.y,
            proximity=distance,
        )

有什么想法吗？

Answer 1

如果你看一下distinct()文档(正如@Todor建议的那样)，你会发现这个:

Any fields used in an order_by() call are included in the SQL SELECT columns. This can sometimes lead to unexpected results when used in conjunction with distinct(). If you order by fields from a related model, those fields will be added to the selected columns and they may make otherwise duplicate rows appear to be distinct. Since the extra columns don’t appear in the returned results (they are only there to support ordering), it sometimes looks like non-distinct results are being returned.

所以无论你做什么，都要牢记这一点。

<小时/>

让我们尝试解决这个问题:

annotate(distance=...) 创建一个名为 distance 的列，您可以使用它对查询进行排序。您需要不同的商家，这些商家可以通过不同的pks来保证:

...annotate(distance=...).order_by('pk', 'distance').distinct('pk')

这将首先按pk对您的查询集进行排序，然后按距离排序，最后它将仅返回具有不同pk的商家。