Unable to use Case, When, Then in django queryset-CodePudding

my models are as follows

class Loan(models.Model):
  loan_value = models.IntegerField()
  channel_seller = models.ForeignKey(ChannelSeller, on_delete=models.CASCADE)


class ChannelSeller(models.Model):
    LEVEL_CHOICES = (
        ('1','Business Consultants'),
        ('2','Unit Managers'),
        ('3','Account Managers'),
        ('4','Branch Managers'),
    )
    level = models.CharField(max_length=2, choices = LEVEL_CHOICES, null = True, blank = True)
    pin_no = models.CharField(max_length=255)
    unit_manager = models.ForeignKey('self', limit_choices_to = {"level": '2'}, on_delete=models.DO_NOTHING, null = True, blank = True, related_name='unit_manager_2')

Loan can be created by business_consultant or unit_manager. Each business_consultant will have a unit_manager, however, unit_manager will have unit_manager as blank

with that said,

I'm trying to sort my query by unit_manager field using case, when, then as follows

transactions = Loan.objects.annotate(unit_manager_pin = Case(When('channel_seller__level' == '2', then='channel_seller_pin_no'), When('channel_seller__level' == '1', then='channel_seller__unit_manager__pin_no'))).filter(channel_seller__level__in = ['1','2']).order_by('channel_seller__level')

This query however throws me error __init__() takes either a Q object or lookups as keyword arguments

CodePudding user response：

You are coding up a query to be executated by the DBM. You can't use Python code in that context ('channel_seller__level' == '2' ). See the doc on conditional expressions.

Please format your code legibly! Use line breaks inside brackets to continue.

In this case, you just change == to =, but it's important to understand why. The argument to When is similar to the argument to .filter. So to test for greater-than, it would be When( field__gt = 2, ...)

You also need to use F to refer to the value in the database. Without F it would (probably, not sure) be an error. (If you wanted a fixed value you would use Value('fixed-value'))

transactions = Loan.objects.annotate(unit_manager_pin =
    Case(When('channel_seller__level' = '2', 
             then=F('channel_seller_pin_no')), 
         When('channel_seller__level' = '1',
             then=F('channel_seller__unit_manager__pin_no'))
         )
    ).filter(
        ...

CodePudding user response：

The condition needed to be applied is like a filter and also get the value using F function. Once try this.

transactions = Loan.objects.annotate(unit_manager_pin = Case(When(channel_seller__level='2', then=F('channel_seller__pin_no')), When(channel_seller__level='1', then=F('channel_seller__unit_manager__pin_no')))).filter(channel_seller__level__in = ['1','2']).order_by('channel_seller__level')