django annotate qs by objects using object_id field

Question

Got user and role. How could i get annotated roles qs by users?

from django.db import models, F, Q

class User(models.Model):
    pass


class Role(models.Model):
    ...
    user_id = models.PositiveIntegerField(
        verbose_name='Related object ID'
    )

I want to do something like this:

roles = Role.objects.all()
roles = roles.annotate(user=Q(User.objects.get(id=F('user_id'))))

for role in roles:
    print(role.user.id) => 123

Traceback:

FieldError: Cannot resolve keyword 'user_id' into field

PS: I don't need ForeignKey or ManytoMany here, I need exactly this relation.

You won't be able to do this with a simple annotation because there is no relationship between your models. Why do you not want to use a ForeignKey and what do you want to do with the annotated User instances (would just some values from the user table work instead)? — Iain Shelvington, Jan 23 '23 at 13:40
That's because I have 2 models I want to be related. That's why I have 2 fields: first contains ContentType object, second got object ID. And I use @property to get related object. That's why I can't get object fields via "join". Also, I mentioned in my question that I don't need ForeignKey and ManyToMany relations for some reasons. But okey, here is the answer. But I have specific question, want to find specific answer. And yes, I want to get some fields from related User instances. — Vlady, Jan 23 '23 at 14:48
Sounds like you are using or should be using a generic relation - https://docs.djangoproject.com/en/4.1/ref/contrib/contenttypes/#generic-relations? — Iain Shelvington, Jan 23 '23 at 14:53
I was looking for annotation not generic relation, @ruddra gave an answer. But thank you for contribution ;) — Vlady, Jan 23 '23 at 15:48

score 2 · Accepted Answer · answered Jan 23 '23 at 14:06

The solution you have mentioned in the question won't work here, because the F(...) query points to User model itself, not Role model. Hence it can not resolve the field.

Also, I would recommend what @Iain has mentioned in the comments section, because in that way you can directly access the user object from Role object. You can consider OneToOne relation for that.

On the other hand, if you do not want to have relation in models, then at best you can access one of the fields from the User model like this (using subquery):

from django.db.models import OuterRef, Subquery

roles = roles.annotate(username=Subquery(User.objects.filter(id=OuterRef('user_id')).values('username')[:1]))

Thanks, @ruddra, this is exactly what I wanted to find -> using Subquery + OuterRef. — Vlady, Jan 23 '23 at 15:27

django annotate qs by objects using object_id field

1 Answers1