How express SQL query using Django ORM?

[Artem Malyshev]

Hi,

I have an example application of the shop.

Full application code is available at https://github.com/dry-python/tutorials/tree/master/django

In this example, User can buy a subscription for different categories of
the content.

The user can subscribe for one day, one month or one year.

Each period has its own price.

Price can change in time, so Price model stores not the actual prices
but the whole Price history.

The Price with the most recent from_date value is the actual one.

This is the Price model.

class Price(models.Model):

    category = models.ForeignKey(Category, related_name="prices", on_delete=models.CASCADE)

    from_date = models.DateField()

    cost = models.DecimalField(max_digits=10, decimal_places=2)

    period = models.IntegerField()

I want to select the cheapest (from actual) price for each category.

I can express this with following SQL query.

SELECT "category_id",
       MIN("cost")
  FROM "example_price"
 WHERE "id" IN (
   SELECT "id"
     FROM "example_price"
    WHERE "category_id" IN (1, 2, 3, 4)
    GROUP BY "category_id", "period"
   HAVING "from_date" = MAX("from_date")
 )
 GROUP BY "category_id"

How I can express it with Django ORM?

Regards, Artem.

[v.kiselicy...@gmail.com]

Price.objects.annotate(_sel=Max('from_date')).filter(from_date=F('_sel')).values("category_id", "price")

[Artem Malyshev]

Hi, thanks for the reply!

This gives me following query:

SELECT "example_price"."category_id",
       "example_price"."cost"
  FROM "example_price"
 GROUP BY "example_price"."id",
          "example_price"."category_id",
          "example_price"."from_date",
          "example_price"."cost",
          "example_price"."period"
HAVING "example_price"."from_date" = (MAX("example_price"."from_date"))

It's not quite right.

Is there a way to have different fields in the SELECT and in the GROUP BY?

Regards, Artem.