0

I have two databases, A and B.

B contains a ForeignKey to A.

When I do B.objects.filter(a_id=3).values('bags').count(), I get the number I want, Y.

What is the set of commands I need in order to add this number, Y, as an annotation into database A?

Ideally, this would be an annotate type of command.

The models look like:

class A(models.Model):
    name = models.CharField(max_length=150)

class B(models.Model):
    name = models.CharField(max_length=150)
    a_id = models.ForeignKey(A, on_delete=models.CASCADE)
    bags = models.ManyToManyField(Bags)

class Bags(models.Model):
    name = models.CharField(max_length=150)
Landmaster
  • 1,043
  • 2
  • 13
  • 21

2 Answers2

2

Try to use b__bags lookup in annotation:

from django.db.models import Count
A.objects.annotate(bags_count=Count('b__bags'))
neverwalkaloner
  • 46,181
  • 7
  • 92
  • 100
  • 1
    This works! Thank you so much. Giving you the Check because you answered earlier :) – Landmaster Jul 27 '18 at 04:18
  • Now, if I were to filter the bags counted by some property in the bags, how could I modify it to count just those? – Landmaster Jul 27 '18 at 04:21
  • 1
    @Landmaster you can use Count's `filter` argument. Check this question: https://stackoverflow.com/questions/30752268/how-to-filter-objects-for-count-annotation-in-django – neverwalkaloner Jul 27 '18 at 04:23
  • 1
    champ. That's exactly what I was looking for. Thanks. – Landmaster Jul 27 '18 at 04:26
  • Ok I must be doing something wrong... Check the difference: `B.objects.filter(a_id=4).values('bags').count()` = 1 `B.objects.filter(a_id=4, is_true=True).values('bags').count()` = 0 But then I do this and get different values: `A.objects.annotate(Y=Count('b__bags')).get(a_id=4).Y` = 1 `A.objects.annotate(Y=Count('b__bags', filter=Q(b__is_true=True))).get(a_id=4).Y` = 1 also. If I do `b__is_true=False`, I also get 1. – Landmaster Jul 27 '18 at 06:55
  • 1
    @Landmaster sorry, don't have any idea. Try to use `Case`: https://stackoverflow.com/a/30754520/641249 – neverwalkaloner Jul 27 '18 at 06:58
  • I'll give it a shot.. It seems like the Q Filter isn't working properly. Even if I try something like `A.objects.annotate(Y=Count('b__bags', filter=Q(b__id=99999999)))` I still get 1. – Landmaster Jul 27 '18 at 07:01
  • 1
    LMAO I have Django version 1.11.14 **crying** – Landmaster Jul 27 '18 at 07:07
1
from django.db.models import Count

A.objects.annotate(Y=Count('b__bags'))
JPG
  • 82,442
  • 19
  • 127
  • 206