CODEWITHSUNDEEP
djangodjango-orm
eran
eran

Reputation: 6921

django is there a way to annotate nested object?

I have the following situation. I have three models, Post, User and Friends.

class User(models.Model):
   name = models.CharField(max_length=100)

class Friend(models.Model):
   user1 = models.ForeignKey(User,related_name='my_friends1')
   user2 = models.ForeignKey(User,related_name='my_friends2')

class Post(models.Model):
   subject = models.CharField(max_length=100)
   user = models.ForeignKey(User)

Every time I bring users, I want to bring the number of his friends:

User.objects.filter(name__startswith='Joe').annotate(fc=Count('my_friends1'))

This works fine.

However, I want to make this work when I bring the users as nested objects of Post. I'm using there select_related to minimized DB calls, so I want to do something like:

Post.objects.filter(subject='sport').select_related('user').annotate(user__fc=Count('user__my_friends1'))

However, this creates field user__fc under post, and not field fc under post.user. Is there a way to achieve this functionality?

Upvotes: 10

Views: 4969

Answers (2)

Charlesthk
Charlesthk

Reputation: 9684

You can make use of Prefetch class:

from django.db.models import Count, Prefetch

posts = Post.objects.all().prefetch_related(Prefetch('user', User.objects.annotate(fc=Count('my_friends1'))))

for post in posts:
    print(post.subject)
    print(post.user.fc)

NB : this does two database queries (Django does the join between Post and User in this case) :

'SELECT "myapp_post"."id", "myapp_post"."subject", "myapp_post"."user_id" FROM "myapp_post"


'SELECT "myapp_user"."id", "myapp_user"."password", "myapp_user"."last_login", "myapp_user"."is_superuser", "myapp_user"."username", "myapp_user"."first_name", "myapp_user"."last_name", "myapp_user"."email", "myapp_user"."is_staff", "myapp_user"."is_active", "myapp_user"."date_joined", COUNT("myapp_friend"."id") AS "fc" FROM "myapp_user" LEFT OUTER JOIN "myapp_friend" ON ("myapp_user"."id" = "myapp_friend"."user1_id") WHERE "myapp_user"."id" IN (3, 4) GROUP BY "myapp_user"."id", "myapp_user"."password", "myapp_user"."last_login", "myapp_user"."is_superuser", "myapp_user"."username", "myapp_user"."first_name", "myapp_user"."last_name", "myapp_user"."email", "myapp_user"."is_staff", "myapp_user"."is_active", "myapp_user"."date_joined"

Upvotes: 3

Kian Ahrabian
Kian Ahrabian

Reputation: 921

You can define a custom manger for your models, as described here and then override its get_queryset() method to add the custom column to your model upon query.

In order to use this manager for a reverse relation, you should set the base manager as described in the docs.

Another approach would be something like this, which you specify the manager of the related model with a hard-coded attribute.

Upvotes: 0

Related Questions