django annotate qs by objects using object

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.

CodePudding user response：

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]))