Comprehensive filter and Django order

Question

I have 4 models 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, everyone has many categories, each category has many products. Now the price of a product can change every day, so the price model will keep the price and date of the product.

My problem is that I need a list of site filters by price range. This price range will depend on the get_lowest_price method and can be from Min to Max and from Max to Min. I already used the lambda expression for this, but I think this is not suitable

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

I can also get the whole site in the price range by running a cycle, but this is also not a good idea. Please help my someone make the request correctly.

, , . "Ishtiaque Khan", 100% .

* , .

Answer 1

1.
. :

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)

mm_date min_price - max_price. , :

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. / , post_save. , .

• , . :

    class LowestPrice(models.Model):
        date = models.DateField()
        site = models.ForeignKey(Site)
        lowest_price = models.IntegerField(default=0)

• post_save . ( )

    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()

• :

    LowestPrice.objects.filter(date=mm_date, lowest_price__gte=min_price, lowest_price__lte=max_price)

Answer 2

django

Price.objects.all().order_by('price') #add [0] for only the first object

Price.objects.all().order_by('-price') #add [0] for only the first object

Price.objects.filter(date= ... ).order_by('price') #add [0] for only the first object

Price.objects.filter(date= ... ).order_by('-price') #add [0] for only the first object

Price.objects.filter(date= ... , price__gte=lower_limit, price__lte=upper_limit ).order_by('price') #add [0] for only the first object

Price.objects.filter(date= ... , price__gte=lower_limit, price__lte=upper_limit ).order_by('-price') #add [0] for only the first object

Answer 3

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

, related_name ForeignKey.

-

class Category(models.Model):
    # rest of the fields
    site = models.ForeignKey(Site, related_name='categories')

Similary, , related_name products prices ForeignKey.

:

related_name, .
.
, annotate , the_date. . "-" .

Answer 4

, ORM ...

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')

/ :

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