I am looking for an efficient django queryset to stop so many hitting my DB

Question

I am looking for an efficient queryset because my codes are trying to ignite my database. As you can see in the code below, these are too inefficient. But I have no idea how to change them to small lines codes efficiently with select_related and prefetch_related.

Framework : Django 2.2

-------models.py--------

class ProjectModel(models.Model):
    project_name = models.CharField(max_length=100)
    start_date = models.DateField(blank=True)
    end_date = models.DateField(blank=True)
    code = models.CharField(max_length=100, unique=True)
    get_pharm = models.ForeignKey(UserProfile, on_delete=models.CASCADE, blank=True, related_name='get_pharm')
    get_hospitals = models.ManyToManyField(UserProfile, blank=True)
    drugs = models.ForeignKey(DrugModels, on_delete=models.CASCADE, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    status = models.CharField(choices=STATUS, max_length=3, default='on')


class SubjectModel(models.Model):
    project = models.ForeignKey(ProjectModel, on_delete=models.CASCADE)
    sex = models.CharField(choices=SEX, max_length=1, blank=True)
    hospital = models.ForeignKey(UserProfile, on_delete=models.CASCADE)
    age = models.CharField(blank=True, max_length=10)
    status = models.CharField(choices=STATUS, max_length=3, default='on')

class SubjectDateModel(models.Model):
    whose = models.ForeignKey(SubjectModel, on_delete=models.CASCADE)
    will_visit_date = models.DateField(blank=True, null=True)
    visited_date = models.DateField(blank=True, null=True)
    visit_check = models.BooleanField(default=False)

FEELING = (
    (1, 'sobad'),
    (2, 'bad'),
    (3, 'normal'),
    (4, 'good'),
    (5, 'verygood'),
)

FLAG = (
    ('device', 'DEVICE'),
    ('drug', 'DRUG'),
    ('side', 'SIDEEFFECT'),
    ('feel', 'FEELING'),
    ('pain', 'PAIN'),
)

DRUG = (
    ('yes', 'ATE'),
    ('no', 'NO'),
)


class DataModel(models.Model):
    user_id = models.ForeignKey(SubjectModel, on_delete=models.CASCADE)
    beacon_info = models.CharField(max_length=100, blank=True, null=True)
    drug = models.CharField(choices=DRUG, max_length=3, blank=True, null=True)
    side_effect = models.CharField(max_length=50, blank=True, null=True)
    side_effect_more = models.CharField(max_length=255, blank=True, null=True)
    feeling = models.PositiveSmallIntegerField(choices=FEELING, blank=True, null=True)
    pain = models.PositiveSmallIntegerField(blank=True, null=True)
    date = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    flag = models.CharField(choices=FLAG, max_length=6)

-------views.py--------

def subject_detail(request, pk):
    details = get_object_or_404(SubjectModel.objects.select_related('hospital__whose').select_related('project'), pk=pk, status='on')

    dates = SubjectDateModel.objects.filter(whose=details)

    drug_percent = details.project.drugs.when
    count = 0
    for i in drug_percent:
        count = count + 1
    # for chart1
    date_count = details.project.end_date - details.project.start_date
    should_eat = count * date_count.days
    real_eat = details.datamodel_set.filter(flag='drug').count()
    if real_eat:
        percent = round(real_eat / should_eat * 100)
    else:
        percent = 0


    # for bar chart
    get_feel_1 = len(details.datamodel_set.filter(feeling=1))
    get_feel_2 = len(details.datamodel_set.filter(feeling=2))
    get_feel_3 = len(details.datamodel_set.filter(feeling=3))
    get_feel_4 = len(details.datamodel_set.filter(feeling=4))
    get_feel_5 = len(details.datamodel_set.filter(feeling=5))

    # for another bar chart
    pain0 = len(details.datamodel_set.filter(flag='pain', pain=0))
    pain1 = len(details.datamodel_set.filter(flag='pain', pain=1))
    pain2 = len(details.datamodel_set.filter(flag='pain', pain=2))
    pain3 = len(details.datamodel_set.filter(flag='pain', pain=3))
    pain4 = len(details.datamodel_set.filter(flag='pain', pain=4))
    pain5 = len(details.datamodel_set.filter(flag='pain', pain=5))
    pain6 = len(details.datamodel_set.filter(flag='pain', pain=6))
    pain7 = len(details.datamodel_set.filter(flag='pain', pain=7))
    pain8 = len(details.datamodel_set.filter(flag='pain', pain=8))
    pain9 = len(details.datamodel_set.filter(flag='pain', pain=9))
    pain10 = len(details.datamodel_set.filter(flag='pain', pain=10))

    context = {
        'details': details,
        'dates': dates,
        'percent': percent,
        'get_feel_1': get_feel_1,
        'get_feel_2': get_feel_2,
        'get_feel_3': get_feel_3,
        'get_feel_4': get_feel_4,
        'get_feel_5': get_feel_5,
        'pain0': pain0,
        'pain1': pain1,
        'pain2': pain2,
        'pain3': pain3,
        'pain4': pain4,
        'pain5': pain5,
        'pain6': pain6,
        'pain7': pain7,
        'pain8': pain8,
        'pain9': pain9,
        'pain10': pain10,
    }
    return render(request, 'subject_app/subject_detail.html', context)

I need to make some chart in that detail template. So I created lots of contexts for each field value and sending it to the template. but my DB is burning because of these code.

any good ideas to make these efficient?

Answer 1

Maybe you can try like this with Conditional Aggregation:

from django.db.models import Count, Case, When, IntegerField

pains = details.datamodel_set.annotate(
        pain0=Count(Case(
           When(pain=0, then=1),
           output_field=IntegerField(),
        ))
    ).annotate(
        pain1=Count(Case(
           When(pain=1, then=1),
           output_field=IntegerField(),
        ))
)  # and so on

pains.values('pain0', 'pain1')