Django ORM: distinct and annotate

Question

Let's say I have a table like this:

class Developer(models.Model):
    first_name = models.CharField(max_length=255)
    last_name= models.CharField(max_length=255)
    team = models.CharField(max_length=255)
    closed_tickets = models.IntegerField()

    objects = DeveloperManager()

I want to write a query which returns:

Write SQL to show percentage of team tickets closed by every developer and rank developers by contribution level. something like this: is there a way to it in a one query?

I am trying to think in the direction of annotation with distinct clause

class DeveloperManager(models.Manager):
    def rank(self):
        return self.order_by('team').annotate(
            total=models.Sum(
                'closed_tickets'
            ),
            piece=models.F('closed_tickets') / models.F('total'),
        ).distinct('team').values()

but it returns me an error:

NotImplementedError: annotate() + distinct(fields) is not implemented.

Mohammad Jafari · Accepted Answer · 2020-08-20T17:46:11.383

The reason of the NotImplementedError is

To let you know that you can't combine annotate and distinct (with fields) together at this time, which means that it doesn't work right now. (@Chris Pratt says in comments of this question)

Maybe this helps:

Developer.objects.raw("""
    select *, rank() over (partition by team order by percentage desc) as rank
    from (
          select *,
                 closed_tickets * 100 / (
                     select sum(closed_tickets) total
                     from t_developer
                     where team = tmp1.team
                     group by team
                 ) percentage
          from t_developer tmp1
    );
""")

you forget `as foo` in the end. Thanks! – kharandziuk Aug 20 '20 at 20:02 — kharandziuk, Aug 20 '20 at 20:02

Django ORM: distinct and annotate

1 Answers1