title: How express SQL query using Django ORM?
body: <p>I have an example application of the shop.</p>
  
  <p>Full application code is available at <a href="https://github.com/dry-python/tutorials/tree/master/django" rel="nofollow noreferrer">https://github.com/dry-python/tutorials/tree/master/django</a></p>
  
  <p>In this example, User can buy a subscription for different categories of
  the content.</p>
  
  <p>The user can subscribe for one day, one month or one year.</p>
  
  <p>Each period has its own price.</p>
  
  <p>Price can change in time, so Price model stores not the actual prices
  but the whole Price history.</p>
  
  <p>The Price with the most recent from_date value is the actual one.</p>
  
  <p>This is the Price model.</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>I want to select the cheapest (from actual) price for each category.</p>
  
  <p>I can express this with following SQL query.</p>
  
  <pre><code>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"
  </code></pre>
  
  <p>How I can express it with Django ORM?</p>
  
  <p>Regards, Artem.</p>