CODEWITHSUNDEEP
sqldjangopostgresqldjango-modelsdjango-queryset
Minuddin Ahmed Rana
Minuddin Ahmed Rana

Reputation: 1144

Django complex filter and order

I have 4 model like this

class Site(models.Model):
    name = models.CharField(max_length=200)

    def get_lowest_price(self, mm_date):
        '''This method returns lowest product price on a site at a particular date'''

class Category(models.Model):
    name = models.CharField(max_length=200)
    site = models.ForeignKey(Site)

class Product(models.Model):
    name = models.CharField(max_length=200)
    category = models.ForeignKey(Category)

class Price(models.Model):
    date = models.DateField()
    price = models.IntegerField()
    product = models.ForeignKey(Product)

Here every have many category, every category have many product. Now product price can change every day so price model will hold the product price and date.

My problem is I want list of site filter by price range. This price range will depends on the get_lowest_price method and can be sort Min to Max and Max to Min. Already I've used lambda expression to do that but I think it's not appropriate

sorted(Site.objects.all(), key=lambda x: x.get_lowest_price(the_date))

Also I can get all site within a price range by running a loop but this is also not a good idea. Please help my someone to do the query in right manner.

If you still need more clear view of the question please see the first comment from "Ishtiaque Khan", his assumption is 100% right.

*In these models writing frequency is low and reading frequency is high.

Upvotes: 6

Views: 4538

Answers (4)

Nathan Do
Nathan Do

Reputation: 2035

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)

Upvotes: 4

franckfournier
franckfournier

Reputation: 364

I think this ORM query could do the job ...

from django.db.models import Min

sites = Site.objects.annotate(price_min= Min('category__product__price'))
            .filter(category__product__price=mm_date).unique().order_by('price_min')

or /and for reversing the order :

sites = Site.objects.annotate(price_min= Min('category__product__price'))
            .filter(category__product__price=mm_date).unique().order_by('-price_min')

Upvotes: 0

Sachin
Sachin

Reputation: 3664

Solution:

from django.db.models import Min

Site.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.

Upvotes: 1

Max M
Max M

Reputation: 581

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

Upvotes: 0

Related Questions