CODEWITHSUNDEEP
djangodjango-orm
zelenov aleksey
zelenov aleksey

Reputation: 398

filter statement based on calculations django orm

how can I translate query like this in django orm?

select id, year, month 
where (year*100 + month) between 201703 and 201801

Thanks in advance for any help

Upvotes: 2

Views: 107

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476659

You can first create an annotation, and then filter on that:

from django.db.models import F

(Modelname.objects
          .annotate(yearmonth=F('year')*100+F('month'))
          .filter(yearmonth__range=(201703, 201801)))

So here we construct an annotation yearmonth (you can use another name if you like), and make it equal to the year column times 100 plus the month column. Next we can filter on that annotation, and do so by specifying a __range here with two bounds.

Normally this will work for any database system that performs the operations you here perform (multiplying a column with a constant number, adding two values together), as well as do the __range filter (in MySQL this is translated into <var> BETWEEN <min> AND <max>). Since we use Django ORM however, if we later decide to use another database, the query will be translated in the other database query language (given of course that is possible).

Upvotes: 2

burning
burning

Reputation: 2586

How about using something similar to this.

Did you try filter and __range

Created_at will be the field in your DB 

ModelName.objects.filter(created_at__range=(start_date, end_date))

Later you can do calculation in your view this is just a workaround.

If you want to run the exactly same query then probably you can run using.

ModelName.objects.raw("select id, year, month 
where (year*100 + month) between 201703 and 201801")

Upvotes: 2

Related Questions