this is my model:

class PmPrice(models.Model):
    created_at = models.DateTimeField()
    price = models.DecimalField(max_digits=6, decimal_places=2)
    listing = models.ForeignKey("PmListing", models.DO_NOTHING)
    seller = models.ForeignKey("PmSeller", models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = "pm_price"

this is my serializer:

class PmPriceListSerializer(serializers.ModelSerializer):
    url = serializers.ReadOnlyField(source="listing.url")
    shop = serializers.ReadOnlyField(source="listing.shop.name")
    name = serializers.ReadOnlyField(source="listing.product.name")
    ean = serializers.ReadOnlyField(source="listing.product.ean")
    uvp = serializers.ReadOnlyField(source="listing.product.uvp")
    sku = serializers.ReadOnlyField(source="listing.product.sku")
    seller = serializers.ReadOnlyField(source="seller.name")

    class Meta:
        model = PmPrice
        fields = (
            "url",
            "shop",
            "name",
            "ean",
            "sku",
            "uvp",
            "price",
            "created_at",
            "seller",
        )

    def to_representation(self, instance):
        data = super().to_representation(instance)
        return data

and this is a method inside my filters.py class that I’m using on my view, and this is causing me headache:

def get_by_last_two_cheapest(self, queryset, name, value):
        latest_date_subquery = (
            queryset.annotate(datum=TruncDate("created_at"))
            .values("datum")
            .order_by("-datum")
            .annotate(latest_date=Max("datum"))
            .values("latest_date")[:1]
        )

        # Second subquery to get the second latest date
        second_latest_subquery = (
            queryset.filter(
                created_at__date__lt=Subquery(latest_date_subquery),
            )
            .annotate(datum=TruncDate("created_at"))
            .values("datum")
            .order_by("-datum")
            .annotate(latest_date=Max("datum"))
            .values("latest_date")[:1]
        )

        # Concatenate the results of the subqueries to get the dates
        dates = list()
        dates.append(str(latest_date_subquery[0]["latest_date"]))
        dates.append(str(second_latest_subquery[0]["latest_date"]))
        print(dates)
        # Perform the necessary filtering and annotation
        qs = (
            queryset.filter(created_at__date__in=dates)
            .values("listing_id", "created_at__date")
            .annotate(cheapest_price=Min("price"))
        )
        print(qs)
        print(len(qs))
        # Retrieve the corresponding rows with the cheapest prices
        result_queryset = queryset.filter(
            created_at__date__in=dates,
            listing_id__in=qs.values_list("listing_id", flat=True),
            price__in=qs.values_list("cheapest_price", flat=True),
        ).order_by("-created_at", "listing_id")
        print(len(result_queryset))
        return result_queryset

Now I want to retrieve the cheapest price for a listing_id on the last created_at date as well as the second last created_at date. So I query the dates, put them in a list, then I get the cheapest_price for the dates (in qs), and then I want to retrieve the result_queryset with all the objects I need to return.

For a created_at date there can be multiple prices for the same listing_id, but I only want the cheapest one (the Min()), that’s why I create the qs Subquery. The qs subquery contains 60 results, which is correct. But the result_queryset contains 61 and I figured out why.. Because it retrieves an additional object where the created_at date matches, the listing_id matches AND its price is inside qs.values_list("cheapest_price") – just not in combination created_at+listing_id+cheapest_price.

So if I add a single row to my database table that matches a valid listing_id, created_at and any cheapest_price that is inside qs, I will get another additional object. How do I fix this?

thx