장고 쿼리셋 최적화 예제 복기(django queryset optimization)

쿼리셋을 클래스로 다듬고, 줄일 수 있는것은 줄이고 조금더 가독성을 좋게 다듬었다.

 

class SummaryQuerysets(object):
    def __init__(self, year, month, coating_company_id):
        self.angle_list = ['ANGLE', 'ANGLE ', 'angle', 'Angle']
        end_day = calendar.monthrange(year, month)[1]
        data_range_query = Q(
            worked_date__range=[f"{year}-{month}-01",
                                f"{year}-{month}-{end_day}"])
        self.current_obj = UploadedExcelFiles.objects.filter(data_range_query, coated_company_id=coating_company_id)
        self.category_list = self.current_obj.annotate(
                category=F('base_data__one_district__category')
            ).values_list('category', flat=True).distinct()

        self.coated_company = CoatCompanyModel.objects.get(id=coating_company_id).coated_company
        if self.coated_company == '국민':
            self.is_BC_option = True
        else:
            self.is_BC_option = False

    def categorial_result(self):
        categorial_result = []
        for cat in self.category_list:
            qs = self._categorial_queryset(cat)
            values = self.current_obj.prefetch_related('base_data__one_district')\
                    .aggregate(**qs)
            if values:
                values['category'] = cat
                values['coat_company'] = self.coated_company
                categorial_result.append(values)
        return categorial_result

    def total_result(self):
        if self.is_BC_option:
            queryset = {
            'new_made':self._total_field_sum('A_Area')
                     - self._total_angle_sum(),
            'recovery_made': self._total_field_sum('B_Area')
                           + self._total_field_sum('C_Area'),
            'angle_sum': self._total_angle_sum(),
            'total_sum':self._total_field_sum('A_Area')
                      + self._total_field_sum('B_Area')
                      + self._total_field_sum('C_Area')
                      - self._total_angle_sum(),
        }
        else:
            queryset = {
                'new_made':self._total_field_sum('A_Area')
                        + self._total_field_sum('C_Area')
                        - self._total_angle_sum(),
                'recovery_made': self._total_field_sum('B_Area'),
                'angle_sum': self._total_angle_sum(),
                'total_sum':self._total_field_sum('A_Area')
                        + self._total_field_sum('B_Area')
                        + self._total_field_sum('C_Area')
                        - self._total_angle_sum(),
            }
        return self.current_obj.prefetch_related('base_data__one_district').aggregate(**queryset)
        
    def _total_angle_sum(self):
        return Coalesce(Sum(
                            Case(
                                When(
                                    base_data__one_district__detailData__data_rows__product_name__product__in=self.angle_list,
                                    then=F('base_data__one_district__detailData__data_rows__A_Area')
                                    )
                                ), 
                            output_field=DecimalField(), default=0),Value(0)
                        )
    
    def _total_field_sum(self, field):
        return Coalesce(Sum('base_data__one_district__detailData__data_rows__' + field), Value(0))

    def _category_field_sum(self, category, field):
        # field = A_Area or B_Area or C_Area
        return Coalesce(Sum(
                            Case(
                                When(
                                    base_data__one_district__category=category, 
                                    then=F('base_data__one_district__detailData__data_rows__' + field)
                                )
                            ), output_field=DecimalField(), default=0
                        ),
                        Value(0)
                    )
    
    def _category_field_angle_sum(self, category):
        return Coalesce(Sum(
                            Case(
                                When(
                                    Q(base_data__one_district__detailData__data_rows__product_name__product__in=self.angle_list)
                                    & Q(base_data__one_district__category=category),
                                    then=F('base_data__one_district__detailData__data_rows__A_Area')
                                ), output_field=DecimalField(), default=0)
                            ),Value(0)
                        )

    def _categorial_queryset(self, category):
        # options for '국민'
        # 국민 슬라브는 B+C를 회수재로 잡음
        if self.is_BC_option:
            return {
                'new_made': self._category_field_sum(category, 'A_Area')
                            - self._category_field_angle_sum(category)
                            ,
                            
                'recovery_made': self._category_field_sum(category, 'B_Area')
                            + self._category_field_sum(category, 'C_Area')
                            ,
                'total_sum': self._category_field_sum(category, 'A_Area')
                           + self._category_field_sum(category, 'B_Area')
                           + self._category_field_sum(category, 'C_Area')
                           - self._category_field_angle_sum(category),
                'angle_sum': self._category_field_angle_sum(category),
            }
        else:
            return {
                'new_made': self._category_field_sum(category, 'A_Area')
                            + self._category_field_sum(category, 'C_Area')
                            - self._category_field_angle_sum(category)
                            ,
                            
                'recovery_made': self._category_field_sum(category, 'B_Area')
                            ,
                'total_sum': self._category_field_sum(category, 'A_Area')
                        + self._category_field_sum(category, 'B_Area')
                        + self._category_field_sum(category, 'C_Area')
                        - self._category_field_angle_sum(category),
                'angle_sum': self._category_field_angle_sum(category),
            }

 

그리고 암이 나았다.