CODEWITHSUNDEEP
pythondjango
Emmanuel Mtali
Emmanuel Mtali

Reputation: 4963

How to filter django model based on other non related model

Please refer to the code below

Transaction models

class Transaction(models.Model)
    current_product_code = models.CharField(....)
    previous_product_code = models.CharField(....)
    @property
    def status(self):
        c_price = Product.objects.get(code=self.current_product_code).price
        p_price = Product.objects.get(code=self.previous_product_code).price
        if c_price == p_price:
            return "Due"
        elif c_price > p_price:
            return "Upgrade"
        else:
            return "Downgrade"

Product model

class Product(models.Model):
    code = models.CharField(....)
    price = models.DecimalField(....)

My question: How can i obtain/filter transactions with upgrade/downgrade/due status. I am trying to create a custom admin filter which filter transaction based on their status but i fail what to put inside .filter() , check the method below

def queryset(self, request, queryset):
    value = self.value()
    if value == 'Upgrade':
        return queryset.filter(***** HERE *****)
    elif value == 'Downgrade':
        return queryset.filter(***** HERE *****)
    elif value == 'Unknown':
        return queryset.filter(***** HERE *****)
        return queryset

Upvotes: 1

Views: 113

Answers (3)

GwynBleidD
GwynBleidD

Reputation: 20539

You really should use ForeignKey between Product and Transaction (for both: current_product_code and previous_product_code). This will allow you to use those relations in your querysets with ease.

My proposed models structure looks like this:

class Product(models.Model):
    code = models.CharField(....)
    price = models.DecimalField(....)


class Transaction(models.Model)
    # You have to define related_name for at least one of relations below.
    # Without that, automatically generated ones will clash.
    # Also don't foget to change `on_delete` to suit your needs.
    current_product = models.ForeignKey(Product, related_name="current_transactions", on_delete=models.CASCADE)
    previous_product = models.ForeignKey(Product, related_name="previous_transactions", on_delete=models.CASCADE)

    @property
    def status(self):
        # also, no need to do additional queries here manually. You can improve
        # it further by using `select_related` when querying for transactions.
        c_price = self.current_product.price
        p_price = self.previous_product.price
        if c_price == p_price:
            return "Due"
        elif c_price > p_price:
            return "Upgrade"
        else:
            return "Downgrade"

With that model structure, finding specific types of transactions will be easier:

upgrade_transactions = Transaction.objects.filter(current_product__price__gt=F('previous_product__price'))
downgrade_transactions = Transaction.objects.filter(current_product__price__lt=F('previous_product__price'))
due_transactions = Transaction.objects.filter(current_product__price=F('previous_product__price'))

Upvotes: 2

Josir
Josir

Reputation: 1644

Remember that filter() operation, in the end, is a SQL operation and we should take care on the performance issues.

So my advice is: if you need to filter by status, update the status on Product model everytime a transaction is saved. Your application will be faster and will have a cleaner code.

Upvotes: 0

mfrackowiak
mfrackowiak

Reputation: 1304

I think you could try to use Subquery, OuterRef and .annotate():

if value == 'Upgrade':
    return queryset.annotate(
        previous_price=Subquery(
            Product.objects.filter(
                code=OuterRef("previous_product_code")
            )[:1]
        ),
        current_price=Subquery(
            Product.objects.filter(
                code=OuterRef("current_product_code")
            )[:1]
        ),
    ).filter(current_price__gt=F("previous_price"))
...

Upvotes: 1

Related Questions