Django - joining via the most recent through table row on a many-to-many

Question

If I have a many-to-many relationship, with a custom through table, how can I join via only the most recent through table row? For example:

class Price(models.Model):
    amount = models.DecimalField(max_digits=21, decimal_places=2)


class PricingSchedules(models.Model):
    name = models.CharField(max_length=100)
    price = models.ForeignKey(Price, on_delete=models.CASCADE)



class Product(models.Model):
    name = models.CharField(max_length=100)
    pricing_schedulesfd = models.ManyToManyField(
        PricingSchedules,
        related_name='pricing_schedule_set',
        through='ProductPricingSchedule',
    )


class ProductPricingSchedule(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    pricing_schedule = models.ForeignKey(
        PricingSchedule,
        on_delete=models.CASCADE,
    )

    class Meta:
        unique_together = ('product', 'pricing_schedule')

I realize these models are a little contrived, but the question still stands.

Say I want to select all products whose price is equal to "5.00", but I only want to consider the most recent row in the ProductPricingSchedule table, if any exists at all?

It should be noted that I don't actually care about the price as much as I care about being able to join through the most recent row for a Case/When annotation, something like this:

Product.objects.annotate(
     is_match=Case(
         When(
         productpricingschedule__pricing_schedule__product__price__price=5.00,
         then=Value(True),
        ),
    default=Value(False),
    output_field=BooleanField(),
)).filter(is_match=True)

question from:https://stackoverflow.com/questions/65850750/django-joining-via-the-most-recent-through-table-row-on-a-many-to-many

Answer 1

Add related_name= to product field in ProductPricingSchedule model

class ProductPricingSchedule(models.Model):
    ...
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name="product_pricing_schedule")
    ...

and try

Product.objects.filter(pricing_schedulesfd__price__amount="5.00").order_by('-product_pricing_schedule__created_at')