CODEWITHSUNDEEP
pythondjangodjango-models
8one6
8one6

Reputation: 13788

In Django ORM: Select record from each group with maximal value of a given attribute

Say I have three models as follows representing the prices of goods sold at several retail locations of the same company:

class Store(models.Model):
    name = models.CharField(max_length=256)
    address = models.TextField()

class Product(models.Model):
    name = models.CharField(max_length=256)
    description = models.TextField()

class Price(models.Model):
    store = models.ForeignKey(Store)
    product = models.ForeignKey(Product)
    effective_date = models.DateField()
    value = models.FloatField()

When a price is set, it is set on a store-and-product-specific basis. I.e. the same item can have different prices in different stores. And each of these prices has an effective date. For a given store and a given product, the currently-effective price is the one with the latest effective_date.

What's the best way to write the query that will return the currently-effective price of all items in all stores?

If I were using Pandas, I would get myself a dataframe with columns ['store', 'product', 'effective_date', 'price'] and I would run 

dataframe\
    .sort_values(columns=['store', 'product', 'effective_date'], ascending=[True, True, False])\
    .groupby('store', 'product')['price'].first()

But there has to be some way of doing this directly on the database level. Thoughts?

Upvotes: 1

Views: 98

Answers (3)

AKS
AKS

Reputation: 19871

If you are using PostgreSQL, you could use order_by and distinct to get the current effective prices for all the products in all the stores as follows:

prices = Price.objects.order_by('store', 'product', '-effective_date')
                      .distinct('store', 'product')

Now, this is quite analogous to what you have there for Pandas.

Do note that using field names in distinct only works in PostgreSQL. Once you have sorted the prices based on store, product and decreasing order of effective date, distinct('store', 'product') will retain only the first entry for each store-product pair and that will be your current entry with recent price.

Not PostgreSQL database:

If you are not using PostgreSQL, you could do it with two queries:

First, we get latest effective date for all the store-product groups:

latest_effective_dates = Price.objects.values('store_id', 'product_id')
                             .annotate(led=Max('effective_date')).values('led')

Once we have these dated we could get the prices for this date:

prices = Price.objects.filter(effective_date__in=latest_effective_dates)

Disclaimer: This assumes that for no effective_date is same for any store-product group.

Upvotes: 1

user2390182
user2390182

Reputation: 73498

Without Postgres' added power (which you should really use) there is a more complicated solution to this (based on ryanpitts' idea), which requires two db hits:

latest_set = Price.objects
    .values('store_id', 'product_id')  # important to have values before annotate ...
    .annotate(max_date=Max('effective_date')).order_by()
    # ... to annotate for the grouping that results from values

# Build a query that reverse-engineers the Price records that contributed to 
# 'latest_set'. (Relying on the fact that there are not 2 Prices
# for the same product-store with an identical date)

q_statement = Q(product_id=-1)  # sth. that results in empty qs
for latest_dict in latest_set:          
    q_statement |= 
        (Q(product_id=latest_dict['product_id']) & 
         Q(store_id=latest_dict['store_id']) & 
         Q(effective_date=latest_dict['max_date']))

Price.objects.filter(q_statement)

Upvotes: 1

Julien Salinas
Julien Salinas

Reputation: 1139

If your DBMS is PostgreSQL you can use distinct combined with order_by this way :

Price.objects.order_by('store','product','-effective_date').distinct('store','product')

It will give you all the latest prices for all product/store combinations.

There are tricks about distinct, have a look at the docs here : https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.query.QuerySet.distinct

Upvotes: 2

Related Questions