Dynamic queryset

Using Django
1

I am good at sql but not in Django query set, need you help. Thank you very much in advance.
How do you write below query in django object filters?
Select * from School SC
Join Student ST on ST.SchoolID = SC.SchoolID
Where ( ST.Birth_Date between (“01/01/1980”, "01/03/1980) OR ST.Birth_Date between (“01/08/1980”, “01/12/1980”)) ANd (ST.Grade in (1,2,3))

2

See the docs on “Complex lookups with Q objects” for writing or conditions in your ORM queries.

3

Appreciate your quick reply but one of the where is fully dynamic e.g birth date range could be one range or multiple ranges OR it could be None
. how can i achieve that one?

4

What do you mean by “dynamic” here?

5

Input will be list of dictionaries.
e.g [{“from”:"", “to” : “”,}, {“from”:"", “to” : “”,},{“from”:"", “to” : “”,}]
currently, i am building sql query and using cursor to execute it but i would like to use ORM to execute my query.

6

There are a couple of key concepts that you’ll need to be comfortable with:

  1. The **kwargs facility for passing a dict as a set of keyword arguments.
  2. The filters in a queryset are keyword args to the “filter” function. (This is also true for constructing Q objects).

This means that this query:
Author.objects.filter(birth_year__lte='1960', country='us')

is identical to:
filter_dict = {'birth_year__lte':'1960', 'country': 'us'}
Author.objects.filter(**filter_dict)

So the process of building dynamic queries involves creating the dictionary defining the filter with the appropriate references.

7

Yes. we could use **kwargs but in my query i have lots of OR clauses and And clauses. Can we achieve that using **kwargs.

8

Yes, you can construct any valid filter, with the combination of kwargs and Q objects.