Filter elements of a model by a calculated field NOT possible

Using Django Forms & APIs
1

I added a calculated field in my model and it is working pretty fine.

I want to get only the data with a certain value of this calculated field.

My model:

class Bdc(models.Model):
    ORDRE = (('Nominatif', 'Nominatif'), ('Au porteur', 'Au porteur'))
    EPARGNE = (('3', '3'), ('6', '6'), ('12', '12'), ('24', '24'), ('36', '36'))
    conseiller=models.ForeignKey(Conseiller, null=True, on_delete=models.CASCADE)
    radical = models.CharField(max_length=200, null=True)
    ordre = models.CharField(max_length=200, choices=ORDRE, null=True)
    montant = models.FloatField(null=True)
    taux = models.FloatField(null=True)
    date_emission = models.DateField(null=True)
    dureeE = models.CharField(max_length=200, null=True, choices=EPARGNE)
    date_paiment = models.DateField(null=True)



    def __str__(self):
        return self.radical

    @property
    def Days_till(self):
        today = date.today()
        days_till = self.date_paiment - today
        days_till_stripped = str(days_till).split(" ", 1)[0]
        if days_till_stripped < '0':
            return 0
        a = int(days_till_stripped)

        return a
    @property
    def bpr(self):
        BPR = self.conseiller.bpr
        return BPR

    @property
    def state(self):
        if self.Days_till == 0:
            return 'payƩ'
        return 'non payƩ'

My view

@login_required(login_url='connexion')
@allowed_users(allowed_roles=['Admins'])
def bdc(request):
    bdcs = Bdc.objects.all().order_by('state')
    conseillers = Conseiller.objects.all()
    myFilter = BdcFilter(request.GET, queryset=bdcs)
    bdcs = myFilter.qs
    context = {'conseillers': conseillers, 'bdcs': bdcs,'myFilter':myFilter}
    return render(request,"comptes/bdc.html", context)

The error m getting from django:

Cannot resolve keyword ā€˜stateā€™ into field. Choices are: conseiller, conseiller_id, date_emission, date_paiment, dureeE, id, montant, ordre, radical, taux

In fact I used order but the same error thta i am getting while using ā€˜Filterā€™.
I want to have the data that have Days_till = 0.

Thank you!

2

Correct. You cannot mix a Django method with an SQL query like that.

The Django ORM functions by creating an SQL statement that is sent to the database. The database is only going to have access to the data in the database. It has no way to call a Python function in your Django code.

You would need to convert that function into the appropriate expression as part of your query. See: Query Expressions | Django documentation | Django

1 Like
3

So there is a way to do it, right ?

4

A way to do what exactly?

5

Salut,

You need to use annotations.

With your current code, only the client (i.e., your python code) is even aware of the existence of the property state.

Annotations allow you to make the database aware of computed/derived values, which can then be used to filter, sort, etc.
In other words, your state variable has to be computed database-side before you can use it in the way you want to use it. Annotation can help you do just that ā€“ they are directives to ā€œenrichā€ each element of a queryset at database level.

Have a try here: QuerySet API reference | Django documentation | Django and maybe show us what you came up with?

Bonne chance :slight_smile:

6

to filter by a calculated field

7

Bonjour,
I still donā€™t understand how does this work. I found that annotate go with aggregation func. How can I use it with filter.
Can you share with me the syntax please ?
Merci dā€™avance!

8

The syntax and examples of annotations (and aggregations) along with filters is described and shown here: Aggregation | Django documentation | Django

9

I tried this :

@login_required(login_url='connexion')

@allowed_users(allowed_roles=['Admins'])

def archive(request):
    
today = date.today()
    
bdcs = Bdc.objects.annotate(Days_till=int(str(Bdc.date_paiment - today).split(" ", 1)[0])).filter(Days_till__lt=0)
   
 conseillers = Conseiller.objects.all()
    
myFilter = BdcFilter(request.GET, queryset=bdcs)
    
bdcs = myFilter.qs
    
context = {'conseillers': conseillers, 'bdcs': bdcs,'myFilter':myFilter}
    
return render(request,"comptes/archive.html", context)

I got this error:

unsupported operand type(s) for -: ā€˜DeferredAttributeā€™ and ā€˜datetime.dateā€™

My model is written in the question

10

When youā€™re posting code here, post it all between lines of three backtick - ` characters. That means youā€™ll have a line of ```, then your code, then another line of ```.

1 Like
11

I tried this

@login_required(login_url='connexion')
@allowed_users(allowed_roles=['Admins'])
def archive(request):
    today = date.today()
    bdcs = Bdc.objects.annotate(Days_till=int(str(Bdc.date_paiment - today).split(" ", 1)[0])).filter(Days_till__lt=0)
    conseillers = Conseiller.objects.all()
    myFilter = BdcFilter(request.GET, queryset=bdcs)
    bdcs = myFilter.qs
    context = {'conseillers': conseillers, 'bdcs': bdcs,'myFilter':myFilter}
    return render(request,"comptes/archive.html", context)

I get this error:

unsupported operand type(s) for -: ā€˜DeferredAttributeā€™ and ā€˜datetime.dateā€™

12

Correct.

Bdc is a model. Itā€™s not an instance of a model. That means that a reference to Bdc.date_paiment is a reference to a field, and not a value in a field.

You canā€™t do what you want to do this way.
That sort of calculation within the query must be done with a query expression.

See:

1 Like
13

What function should I be using for that to get the value ?

14

something like this (logic might be wrong but hopefully the idea is conveyed):

bdcs = Bdc.objects.annotate(
    duration_till=ExpressionWrapper(
        F('date_paiment ') - Now(),
        output_field=models.DurationField()
   )
).filter(
    duration_till__gt=timedelta(days=1),
)

and then:

<td>{{ bdc.days_till }} days</td>

But surely the field date_paiment is populated when payment is actually madeā€¦? In which case you could go for far simpler (then again, not aware of all the functional requirements, etc, etc):

bdcs = Bdcs.objects.annotate(
    state=Case(
        When(
            date_paiment__isnull=False,
            then=Value('payƩ')
        ),
        default=Value('non payƩ'),
        output_field=models.Charfield()
    )
).order_by('state')

and in the template for html/json/whatever rendering:

bdc.state
1 Like
15

tu es un hƩro!
Merci :slight_smile: