The output could look like this, for example:

For context:

(see models below)

I have a commission structure which will have brackets depending on how much a user is earning in a month. Ideally, I need to know in my Commission Bracket model, the bracket index for a given structure.

Here are my models.

class CommissionStructure(APIBaseModel):
    advisor = models.ManyToManyField(AdviserDetail)
    name = models.CharField(max_length=20, blank=True, null=True, default='default')
    company = models.ForeignKey(Company, on_delete=models.CASCADE)
    start_dt = models.DateTimeField(auto_now_add=True)
    end_dt = models.DateTimeField(default=timezone.datetime.max)

    objects = CommissionStructureManager()



class CommissionBracket(APIBaseModel):
    <secondary_id ???>
    commission_structure = models.ForeignKey(CommissionStructure, on_delete=models.CASCADE, related_name="brackets")
    lower_bound = models.DecimalField(decimal_places=2, default=0.00, max_digits=20, null=True, blank=True)
    upper_bound = models.DecimalField(decimal_places=2, default=0.00, max_digits=20, null=True, blank=True)

Please note, I may not have to store it on my model if I can add an annotation to an aggregate set, but my preference is to follow DRY.

Thank you

CodePudding user response：

I've found a way to annotate the queryset, but for interest, my original question still remains: how do I add another field partitioned by the foreign key?

brackets = CommissionBracket.objects.select_related("commission_structure")\
            .prefetch_related(
            'commission_structure__advisor',
            'commission_structure__start_dt__gte',
            'commission_structure__end_dt__lte',
            'commission_structure__company',
            'bracket_values'
            ).filter(
                commission_structure__advisor=advisor,
                commission_structure__start_dt__lte=date,
                commission_structure__end_dt__gte=date,
                commission_structure__company=advisor.user.company,
            ).annotate(index=Window(
                    expression=Count('id'),
                    partition_by="commission_structure",
                    order_by=F("lower_bound").asc()))

CodePudding user response：

My suggestion would be to execute custom SQL directly. You can add the secondary id as an integer field in CommissionBracket. Then, you can implement this:

from django.db import connection

def sample_view(request):
   ...

   with connection.cursor() as cursor:
      cursor.execute('''
         INSERT INTO appname_commissionbracket (
            secondary_id, 
            commission_structure_id
         )
         SELECT CASE 
            WHEN MAX(secondary_id)
               THEN MAX(secondary_id)   1
               ELSE 1
            END AS new_secid, %s
         FROM appname_commissionbracket 
         WHERE commission_structure_id = %s''',
         [1, 1] # Sample foreign key value
      )

   return render(...)

Here we're using INSERT INTO SELECT since we're basing the new record's secondary_id from the same table. We're also adding a CASE so that we can have a fallback value if no records with commission_structure_id value as 1 are returned.

In case you need to populate other columns during create, you can simply include them like so:

INSERT INTO (secondary_id, commission_structure_id, lower_bound, upper_bound)
SELECT CASE ... END AS new_secid, <fk_value>, <lower_bound_value>, <upper_bound_value>