Don't know how to query a complex SQL in django-CodePudding

I have these two models below

class Category(models.Model):
    
    id = models.AutoField(primary_key=True)
    cate_id = models.CharField(max_length=16, editable=False, default=utils.generate_random_str)
    name = models.CharField(max_length=64, default="")

class Record(models.Model):
    id = models.AutoField(primary_key=True)
    record_id = models.CharField(max_length=16, editable=False, default=utils.generate_random_str)
    count = models.IntegerField(blank=True)
    user = models.ForeignKey(Profile, on_delete=models.CASCADE, null=True)

And I want to make SQL Query distinct cate_id and Sum their values and Group them like this:

SELECT 
  B.name ,
  SUM(A.count) AS count_sum 
FROM 
  app_record AS A, 
  app_category AS B 
WHERE 
  A.`user_id` = 1 
AND 
  B.cate_id IN (
    SELECT 
      DISTINCT(B.cate_id) 
    FROM 
      app_record AS C, 
      app_category AS D 
    WHERE 
      C.category_id = D.id 
  ) 
AND 
  A.category_id = B.id 
GROUP BY 
  B.cate_id;

I expect the results should be like this:

 --------- ----------- 
| name    | count_sum |
 --------- ----------- 
| Test1   |    494    |
| Test2   |    18     |
| Test3   |    269    |
 --------- ----------- 
3 rows in set (0.001 sec)

I want to make that query in Django but I couldn't work out on my own. I have tried this but it didn't seem to work out:

Record.objects.filter( 
    user=user
).distinct().annotate(
    record__category_id=F('category__id')
).aggregate(
    count_sum=Sum('count')
)

I don't know how to do that, hope someone can solve this.Thanks a lot.

CodePudding user response：

If you want to count the sum of all records, and need an aggregate, you could do this. I'm not sure if the totalcount is the actual total though, because of .distinct(), so if the code doesn't suit your needs; please try removing .distinct() first, and then try again. :)

Record.objects.filter(user=user)\
    .distinct()\
    .annotate(total_count=Count('count'))\
    .aggregate(count_sum=Sum('total_count'))

The following code is not needed. Django auto-includes related models' id's:

.annotate(record__category_id=F('category__id'))

as stated in this doc

To still get distinct values from fields in mysql, without using `.distinct('category__id')`

Record.objects.filter(user=user).values(category__id)\
    .annotate(
        count_ref=models.F('count')
    ).aggregate(
        total_count=models.Sum('count_ref')
    )
# maybe using just 'count' works, instead of count_ref, without the annotation. This is how I use the code, this is how it works for me.

To still get distinct values from fields in mysql, without using .distinct('category__id')

To still get distinct values from fields in mysql, without using `.distinct('category__id')`