How to use variable column name in filter in Django ORM?

I have two tables BloodBank(id, name, phone, address) and BloodStock(id, a_pos, b_pos, a_neg, b_neg, bloodbank_id). I want to fetch all the columns from two tables where the variable column name (say bloodgroup) which have values like a_pos or a_neg… like that and their value should be greater than 0. How can I write ORM for the same?

SQL query is written like this to get the required results.

sql="select * from public.bloodbank_bloodbank as bb, public.bloodbank_bloodstock as bs where bs."+blood+">0 and bb.id=bs.bloodbank_id order by bs."+blood+" desc;"
cursor = connection.cursor()
cursor.execute(sql)
bloodbanks = cursor.fetchall()

Answer

You could be more specific in your questions, but I believe you have a variable called blood which contains the string name of the column and that the columns a_pos, b_pos, etc. are numeric.

You can use a dictionary to create keyword arguments from strings:

filter_dict = {bloodstock__blood + '__gt': 0}
bloodbanks = Bloodbank.objects.filter(**filter_dict)

This will get you Bloodbank objects that have a related bloodstock with a greater than zero value in the bloodgroup represented by the blood variable.

Note that the way I have written this, you don’t get the bloodstock columns selected, and you may get duplicate bloodbanks. If you want to get eliminate duplicate bloodbanks you can add .distinct() to your query. The bloodstocks are available for each bloodbank instance using .bloodstock_set.all().

The ORM will generate SQL using a join. Alternatively, you can do an EXISTS in the where clause and no join.

from django.db.models import Exists, OuterRef

filter_dict = {blood + '__gt': 0}
exists = Exists(Bloodstock.objects.filter(
    bloodbank_id=OuterRef('id'),
    **filter_dict
)
bloodbanks = Bloodbank.objects.filter(exists)

There will be no need for a .distinct() in this case.