Django complex filter and order
1. Using query
If you just wanna query using a specific date. Here is how:
q = Site.objects.filter(category__product__price__date=mm_date) \ .annotate(min_price=Min('category__product__price__price')) \ .filter(min_price__gte=min_price, min_price__lte=max_price)
It will return a list of Site with lowest price on mm_date
fall within range of min_price
- max_price
. You can also query for multiple date using query like so:
q = Site.objects.values('name', 'category__product__price__date') \ .annotate(min_price=Min('category__product__price__price')) \ .filter(min_price__gte=min_price, min_price__lte=max_price)
2. Eager/pre-calculation, you can use post_save
signal. Since the write frequency is low this will not be expensive
- Create another Table to hold lowest prices per date. Like this:
class LowestPrice(models.Model): date = models.DateField() site = models.ForeignKey(Site) lowest_price = models.IntegerField(default=0)
- Use
post_save
signal to calculate and update this every time there. Sample code (not tested)
from django.db.models.signals import post_save from django.dispatch import receiver @receiver(post_save, sender=Price) def update_price(sender, instance, **kwargs): cur_price = LowestPrice.objects.filter(site=instance.product.category.site, date=instance.date).first() if not cur_price: new_price = LowestPrice() new_price.site = instance.product.category.site new_price.date = instance.date else: new_price = cur_price # update price only if needed if instance.price<new_price.lowest_price: new_price.lowest_price = instance.price new_price.save()
- Then just query directly from this table when needed:
LowestPrice.objects.filter(date=mm_date, lowest_price__gte=min_price, lowest_price__lte=max_price)
Solution:
from django.db.models import MinSite.objects.annotate( price_min=Min('categories__products__prices__price')).filter( categories__products__prices__date=the_date,).distinct().order_by('price_min') # prefix '-' for descending order
For this to work, you need to modify the models by adding a related_name
attribute to the ForeignKey fields.
Like this -
class Category(models.Model): # rest of the fields site = models.ForeignKey(Site, related_name='categories')
Similary, for Product and Price models, add related_name
as products
and prices
in the ForeignKey fields.
Explanation:
Starting with related_name
, it describes the reverse relation from one model to another.
After the reverse relationship is setup, you can use them to inner join the tables.
You can use the reverse relationships to get the price of a product of a category on a site and annotate
the min price, filtered by the_date
. I have used the annotated value to order by min price of the product, in ascending order. You can use '-' as a prefix character to do in descending order.
Do it with django queryset operations
Price.objects.all().order_by('price') #add [0] for only the first object
or
Price.objects.all().order_by('-price') #add [0] for only the first object
or
Price.objects.filter(date= ... ).order_by('price') #add [0] for only the first object
or
Price.objects.filter(date= ... ).order_by('-price') #add [0] for only the first object
or
Price.objects.filter(date= ... , price__gte=lower_limit, price__lte=upper_limit ).order_by('price') #add [0] for only the first object
or
Price.objects.filter(date= ... , price__gte=lower_limit, price__lte=upper_limit ).order_by('-price') #add [0] for only the first object