0

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.

Brian Destura
  • 11,487
  • 3
  • 18
  • 34
leo
  • 82
  • 1
  • 6

1 Answers1

1

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.
nigel239
  • 1,485
  • 1
  • 3
  • 23
  • 1
    Probably you misunderstand my attention, I mean I want distinct category instead of distinct record, like the SQL mentioned at the original question : ```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 simply want to translate it to django synax – leo Aug 23 '22 at 23:58
  • django does not support `distinct()` on fields in MySQL. @leo – nigel239 Aug 23 '22 at 23:58
  • 1
    So basically I'm unable to use django models to execute the SQL I wanted and I have to use Record.objects.raw(MY SQL HERE)?@nigel239 – leo Aug 24 '22 at 00:01
  • @leo I updated my answer to provide a solution. – nigel239 Aug 24 '22 at 00:02
  • but how to GROUP BY ... @nigel239 – leo Aug 24 '22 at 00:08
  • https://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django – nigel239 Aug 24 '22 at 00:16
  • 1
    I found the answer, thanks a lot: Eventually I found the answer: ```Record.objects.filter(user=user).values('category__id').annotate(sum=Sum('count')).order_by()``` – leo Aug 24 '22 at 00:23