Complex Queries in Django

[bioscom]

I have the following Models in my application

class Workstream(models.Model):
    workstreamname = models.CharField(max_length=250)
    
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='rel_workstream_owner')
    user_workstreamsponsor = models.ForeignKey(User, related_name='workstream_sponsor', blank=True,null=True, on_delete=models.CASCADE)
    user_workstreamlead = models.ForeignKey(User, related_name='workstream_wslead', blank=True,null=True, on_delete=models.CASCADE)
    user_financesponsor = models.ForeignKey(User, related_name='workstream_finance', blank=True,null=True, on_delete=models.CASCADE)
    
    workstreamtarget = models.DecimalField(max_digits = 20, decimal_places = 2, blank=True,null=True)
    workstreamambition = models.DecimalField(max_digits = 20, decimal_places = 2, blank=True,null=True)
    workstreamdescription = models.TextField(blank=True,null=True)
    
    date_created = models.DateTimeField(auto_now_add=True)
    date_updated = models.DateTimeField(auto_now=True)
    
    created_by = models.ForeignKey(User, related_name='rel_created_set', on_delete=models.CASCADE)
    last_modified_by = models.ForeignKey(User, related_name='rel_modified_set', on_delete=models.CASCADE)

    class Meta:
        ordering = ('-workstreamname',)
    
    def __str__(self):
        return self.workstreamname

class Initiative(models.Model):
    #<------ Required Fields ----->
    initiative_id = models.CharField(max_length=10)
    initiative_name = models.CharField(_('initiative name'), max_length=80)
    Workstream = models.ForeignKey(Workstream, on_delete=models.CASCADE, related_name='initiative_workstream')
    actual_Lgate = models.ForeignKey(Actual_L_Gate, related_name='initiative_Actual_LGate', on_delete=models.CASCADE)
    currency = models.ForeignKey(Currency, blank=True, null=True, on_delete=models.CASCADE, related_name='initiative_impact_currency')
    
    overall_status = models.ForeignKey(overall_status, blank=True, null=True, related_name='initiative_overall_status', on_delete=models.CASCADE)
    slug = models.SlugField(_('slug'), max_length=130, blank=True)
    
    approval_status_visual = models.ForeignKey(approvalStatusVisual, on_delete=models.CASCADE, blank=True, null=True, related_name='initiative_approvalStatus') 
    next_Lgate_Comment = models.TextField(null=True, blank=True)
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='initiative_owner')
    #<---- End Required Fields ------>
    
    description = models.TextField(null=True, blank=True) 
    problem_statement = models.TextField(null=True, blank=True) 
    overallstatuscommentary = models.TextField(null=True, blank=True)
    discipline = models.ForeignKey(Discipline,  on_delete=models.CASCADE, blank=True, null=True, related_name='initiative_Discipline')
    Function = models.ForeignKey(Functions,  on_delete=models.CASCADE, blank=True, null=True, related_name='initiative_Function')
    SavingType = models.ForeignKey(SavingsType,  on_delete=models.CASCADE, blank=True, null=True, related_name='initiative_SavingsType')
    workstreamsponsor = models.ForeignKey(User, related_name='initiative_wssponsor', blank=True, null=True, on_delete=models.CASCADE)
    workstreamlead = models.ForeignKey(User, related_name='initiative_wslead', blank=True, null=True, on_delete=models.CASCADE)
    financesponsor = models.ForeignKey(User, related_name='initiative_finance', blank=True, null=True, on_delete=models.CASCADE)
    initiativesponsor = models.ForeignKey(User, related_name='initiative_sponsor', blank=True, null=True, on_delete=models.CASCADE)
    
    Yearly_Planned_Value = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Yearly_Forecast_Value = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Yearly_Actual_value = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    
    enabledby = models.ManyToManyField(EnabledBy, blank=True, related_name='initiative_enabledby')
    Plan_Relevance = models.ManyToManyField(PlanRelevance, blank=True, related_name='initiative_planrelevance')
    
    L0_Completion_Date_Planned = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L1_Completion_Date_Planned = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L2_Completion_Date_Planned = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L3_Completion_Date_Planned = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L4_Completion_Date_Planned = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L5_Completion_Date_Planned = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    
    L0_Completion_Date_Actual = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L1_Completion_Date_Actual = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L2_Completion_Date_Actual = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L3_Completion_Date_Actual = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L4_Completion_Date_Actual = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    L5_Completion_Date_Actual = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    
    Planned_Date = models.DateField(auto_now_add=False, blank=True, null=True, db_index=True)
    Approval_Status  = models.CharField(max_length=255, blank=True, null=True)
    HashTag =  models.TextField(null=True, blank=True) 
    Created_Date  = models.DateTimeField(auto_now_add=True, blank=True, null=True, db_index=True)
    updated = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    mark_as_confidential = models.BooleanField(default=False)
    
    DocumentLink = models.CharField(max_length=2000, blank=True, null=True)
    SharepointUrl = models.CharField(max_length=2000, blank=True, null=True)
    
    YYear = models.IntegerField(blank=True, null=True)
    
    class Meta:
        ordering = ('-initiative_id',)
        
    def save(self, *args, **kwargs):
        if not self.slug:
            self.slug = slugify(self.initiative_name)
        super().save(*args, **kwargs)
    
    def __str__(self):
        return self.initiative_name
    
    
    def get_absolute_url(self):
        return reverse('Fit4:initiative_details', args=[self.initiative_id])
    
    
    @property
    def InitiativeOwnerFullName(self):
        return str('' if self.author.first_name is None else self.author.first_name)  + ', ' + str('' if self.author.last_name is None else self.author.last_name)
        #return self.author.first_name + ', ' + self.author.last_name
    
    @property
    def InitiativeSponsorFullName(self):
        return str('' if self.initiativesponsor.first_name is None else self.initiativesponsor.first_name)  + ', ' + str('' if self.initiativesponsor.last_name is None else self.initiativesponsor.last_name)
        #return self.initiativesponsor.first_name + ', ' + self.initiativesponsor.last_name

    @property
    def WorstreamSponsorFullName(self):
        return str('' if self.workstreamsponsor.first_name == None else self.workstreamsponsor.first_name)  + ', ' + str('' if self.workstreamsponsor.last_name == None else self.workstreamsponsor.last_name)
        #return self.workstreamsponsor.first_name + ', ' + self.workstreamsponsor.last_name

    @property
    def WorkstreamLeadFullName(self):
        return str('' if self.workstreamlead.first_name is None else self.workstreamlead.first_name)  + ', ' + str('' if self.workstreamlead.last_name is None else self.workstreamlead.last_name)
        #return self.workstreamlead.first_name + ', ' + self.workstreamlead.last_name

    @property
    def FinanceSponsorFullName(self):
        return str('' if self.financesponsor.first_name is None else self.financesponsor.first_name)  + ', ' + str('' if self.financesponsor.last_name is None else self.financesponsor.last_name)
        #return self.financesponsor.first_name + ', ' + self.financesponsor.last_name

class InitiativeImpact(models.Model):
    initiativeId = models.ForeignKey(Initiative, on_delete=models.CASCADE, related_name='initiative_initiative_impact')
    benefittype = models.ForeignKey(BenefitType, blank=True, null=True, on_delete=models.CASCADE, related_name='initiative_benefittype')
    frequency = models.ForeignKey(Frequency, blank=True, null=True, on_delete=models.CASCADE, related_name='initiative_frequency')
    
    YYear = models.IntegerField(blank=True, null=True, choices=get_years)
    
    Jan_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Feb_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Mar_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Apr_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    May_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Jun_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Jul_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Aug_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Sep_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Oct_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Nov_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Dec_Plan = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    
    Jan_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Feb_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Mar_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Apr_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    May_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Jun_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Jul_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Aug_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Sep_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Oct_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Nov_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Dec_Forecast = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    
    Jan_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Feb_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Mar_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Apr_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    May_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Jun_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Jul_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Aug_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Sep_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Oct_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Nov_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)
    Dec_Actual = models.DecimalField(max_digits=18, decimal_places=2, blank=True, null=True)

    @property
    def sum_plan(self):
        return float(0 if self.Jan_Plan is None else self.Jan_Plan) + float(0 if self.Feb_Plan is None else self.Feb_Plan) + float(0 if self.Mar_Plan is None else self.Mar_Plan) + float(0 if self.Apr_Plan is None else self.Apr_Plan) + float(0 if self.May_Plan is None else self.May_Plan) + float(0 if self.Jun_Plan is None else self.Jun_Plan) + float(0 if self.Jul_Plan is None else self.Jul_Plan) + float(0 if self.Aug_Plan is None else self.Aug_Plan) + float(0 if self.Sep_Plan is None else self.Sep_Plan) + float(0 if self.Oct_Plan is None else self.Oct_Plan) + float(0 if self.Nov_Plan is None else self.Nov_Plan) + float(0 if self.Dec_Plan is None else self.Dec_Plan)

    @property
    def sum_forecast(self):
        return float(0 if self.Jan_Forecast is None else self.Jan_Forecast) + float(0 if self.Feb_Forecast is None else self.Feb_Forecast) + float(0 if self.Mar_Forecast is None else self.Mar_Forecast) + float(0 if self.Apr_Forecast is None else self.Apr_Forecast) + float(0 if self.May_Forecast is None else self.May_Forecast) + float(0 if self.Jun_Forecast is None else self.Jun_Forecast) + float(0 if self.Jul_Forecast is None else self.Jul_Forecast) + float(0 if self.Aug_Forecast is None else self.Aug_Forecast) + float(0 if self.Sep_Forecast is None else self.Sep_Forecast) + float(0 if self.Oct_Forecast is None else self.Oct_Forecast) + float(0 if self.Nov_Forecast is None else self.Nov_Forecast) + float(0 if self.Dec_Forecast is None else self.Dec_Forecast)
    
    @property
    def sum_actual(self):
        return float(0 if self.Jan_Actual is None else self.Jan_Actual) + float(0 if self.Feb_Actual is None else self.Feb_Actual) + float(0 if self.Mar_Actual is None else self.Mar_Actual) + float(0 if self.Apr_Actual is None else self.Apr_Actual) + float(0 if self.May_Actual is None else self.May_Actual) + float(0 if self.Jun_Actual is None else self.Jun_Actual) + float(0 if self.Jul_Actual is None else self.Jul_Actual) + float(0 if self.Aug_Actual is None else self.Aug_Actual) + float(0 if self.Sep_Actual is None else self.Sep_Actual) + float(0 if self.Oct_Actual is None else self.Oct_Actual) + float(0 if self.Nov_Actual is None else self.Nov_Actual) + float(0 if self.Dec_Actual is None else self.Dec_Actual)
    
    def __str__(self):
        return self.benefittype

The Initiative Model has InitiativeImpact looking up to it, and the Initiaive Model is looking up to Workstream Model. I want to write a query to calculate the total sum of all InitiativeImpact under a Workstream.

Now it is showing several workstreams and not the aggregate of all InitiativeImpact in a workstream.

I will be very glad to get a respons to this request.

I have this query oInitiative = InitiativeImpact.objects.select_related('initiativeId') but it is not giving me the expected output.

Thank you.

[KenWhitesell]

Welcome @bioscom !

Side Note: When posting code here, enclose the code between lines of three
backtick - ` characters. This means you’ll have a line of ```, then your code,
then another line of ```. This forces the forum software to keep your code
properly formatted. (I have taken the liberty of correcting your original posts.
Please remember to do this in the future.)

@bioscom

I want to write a query to calculate the total sum of all InitiativeImpact under a Workstream.

Total sum of what? (What field?)

@bioscom

oInitiative = InitiativeImpact.objects.select_related('initiativeId')

Your query isn’t doing anything to calculate a sum.

See the docs for Aggregation.

Keep in mind that your @property methods exist in Django and not in the database. This means they’re not available to the orm for queries.

This means that you would need to write the code in Python to call any of those methods, if they’re part of what you’re trying to calculate.

I’d seriously consider normalizing your models. Having these repeated fields like this is a general indication that they should exist in separate models with a foreign key to the base model.

[bioscom]

Thank you very much for the immediate response, I was not expecting the response to be as swift as it was. And thanks for pointing at normalizing my Models. I have seen those fields and I will do the needful.

I was able to get the problem solved. Thanks for pointing me to the Aggregation documentation, it was helpful.

I arrived at the query below and was able to get the job done

annotated_queryset = Workstream.objects.annotate( Jan_Plan=Sum('initiative_workstream__initiative_initiative_impact__Jan_Plan'), Feb_Plan=Sum('initiative_workstream__initiative_initiative_impact__Feb_Plan'), Mar_Plan=Sum('initiative_workstream__initiative_initiative_impact__Mar_Plan'), Apr_Plan=Sum('initiative_workstream__initiative_initiative_impact__Apr_Plan'), May_Plan=Sum('initiative_workstream__initiative_initiative_impact__May_Plan'), Jun_Plan=Sum('initiative_workstream__initiative_initiative_impact__Jun_Plan'), Jul_Plan=Sum('initiative_workstream__initiative_initiative_impact__Jul_Plan'), Aug_Plan=Sum('initiative_workstream__initiative_initiative_impact__Aug_Plan'), Sep_Plan=Sum('initiative_workstream__initiative_initiative_impact__Sep_Plan'), Oct_Plan=Sum('initiative_workstream__initiative_initiative_impact__Oct_Plan'), Nov_Plan=Sum('initiative_workstream__initiative_initiative_impact__Nov_Plan'), Dec_Plan=Sum('initiative_workstream__initiative_initiative_impact__Dec_Plan'))

Thank you very much. I will be coming here for more solutions.