Is there a way to join two models in Django?-CodePudding

I have a model with four important fields, I want to query this model for sum of values for each month, but these values are calculated differently based on their currency. So for example if currency = USD the formulae will be a * b / c and if it is GBP it will be a * b * c. This is what I have done so far;

# model

class Permit(models.Model):

    CONVERSION = [
        (decimal.Decimal('1.0000'), '1.0000'),
        (decimal.Decimal('0.0006'), '0.0006'),
        (decimal.Decimal('0.0007'), '0.0007'),
        (decimal.Decimal('0.0010'), '0.0010'),
        (decimal.Decimal('0.0015'), '0.0015'),
    ]

    UNITS = [

        ('m2', 'm2'),
        ('m3', 'm3'),

    ]

    CURRENCY = [
        ('USD', 'USD'),
        ('GBP', 'GBP'),
        ('CFA', 'CFA'),
        ('Euro', 'Euro'),

    ]

    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    specie = models.ForeignKey(Specie, on_delete=models.CASCADE)
    grade = models.ForeignKey(Grade, on_delete=models.CASCADE)
    info = models.ForeignKey(Info, on_delete=models.CASCADE)

    # volume
    volume = models.DecimalField(max_digits=19, decimal_places=3)
    conversion_factor = models.DecimalField(
        choices=CONVERSION, max_digits=5, decimal_places=4)
    units = models.CharField(choices=UNITS, max_length=2, default='m3')
    unit_price = models.DecimalField(max_digits=19, decimal_places=3)
    currency = models.CharField(choices=CURRENCY, max_length=5)
    ex_rate = models.DecimalField(
        max_digits=19, decimal_places=7, verbose_name='Exchange Rate')

    def __str__(self):
        return str(self.info.exporter)


    @property
    def euro_value(self):

        if self.volume and self.unit_price and self.ex_rate != None:
            if self.currency == 'USD':
                ev = self.volume * self.unit_price
                ev = ev / self.ex_rate
                ev = round(ev, 2)

            else:
                ev = self.volume * self.unit_price * self.ex_rate
                ev = round(ev, 2)
        else:
            ev = 0
        return ev

# the query
q1 = Permit.objects.filter(info__date_of_transaction__year=year
        ).filter(currency='USD'
        ).values(
        'info__date_of_transaction__month').annotate(
        value=Sum(F('volume') * F('unit_price') / F('ex_rate'))
    ).order_by('info__date_of_transaction__month')

    q2 = Permit.objects.filter(info__date_of_transaction__year=year
        ).exclude(currency='USD'
        ).values(
        'info__date_of_transaction__month').annotate(
        value=Sum(F('volume') * F('unit_price') * F('ex_rate'))
    ).order_by('info__date_of_transaction__month')

# the answer i am expecting
{"labels": ["August", "September"], "data": ["123562.37", "10189.25"]}

Now the question is, how do I perform the query?

CodePudding user response：

You can use the conditional expressions Case and When in an annotation to perform different logic based on a condition

from django.db.models import Case, When, F

Permit.objects.annotate(
    value=Case(
        When(currency='USD', then=F('volume') * F('unit_price') / F('ex_rate')),
        When(currency='GBP', then=F('volume') * F('unit_price') * F('ex_rate'))
    )
)

This doesn't include the grouping that you have in your query but the idea should be the same