Django queryset attach or annotate related object field

Question

It is needed to attach to queryset results related object field.

Models:

class User(models.Model):
    name = models.CharField(max_length=50)
    friends = models.ManyToManyField('self', through='Membership',
        blank=True, null=True, symmetrical=False)


class Membership(models.Model):
    status = models.CharField(choices=SOME_CHOICES, max_length=50)
    from_user = models.ForeignKey(User, related_name="member_from")
    to_user = models.ForeignKey(User, related_name="member_to")

I can do this:

>>> User.objects.all().values('name', 'member_from__status')
[{'member_from__status': u'accepted', 'name': 'Ann'}, {'member_from__status': u'thinking', 'name': 'John'}]

'member_from__status' contains information, that i need. But together with it, i need a model instance also.

What i want is:

>>> users_with_status = User.objects.all().do_something('member_from__status')
>>> users_with_status
[<User 1>, <User 2>]

>>> users_with_status[0] # <-- this is an object, so i can access to all its methods

Every instance in queryset has a 'member_from__status' field with corresponding value:

>>> users_with_status[0].member_from__status
u'accepted'

How this could be achieved?

unfortunately, this will create a DB query at each users_with_status[n].member_from.status access — stalk
– stalk, Commented Nov 24, 2013 at 12:45

gogaz · Accepted Answer · 2020-06-28 12:41:07Z

You can use annotate and F

>>> from django.db.models import F
>>> users = User.objects.all().annotate(member_from__status=F('member_from__status'))
>>> users[0].member_from__status
'accepted'

Tested with Django versions 1.11 and 2.2

In answer to comments

Here I guess we are rather interested by member_to__status than member_from__status (we want the user being requested to accept the friendship, not the other way around)

Here's the content of my membership table:

status   |  from|   to
---------+------+-----
accepted |     1|    2
accepted |     2|    3
 refused |     3|    1

Since "it's not possible to have a symmetrical, recursive ManyToManyField" (e.g. user 1 accepted user 2 so user 1 is now in user 2's friend list but user 2 is also in user 1's friends), this is how I would retrieve all actual friends:

>>> for user in User.objects.all():
...     friends = user.friends.filter(member_to__status='accepted')
...     print(str(user.id) + ': ' + ','.join([str(friend.id) for friend in friends]))
1: 2
2: 3
3:

If you want to have <User 1> in <User 2>'s friends, I'd suggest to add another Membership when the friendship from 1 to 2 has been accepted.

Here we assume that a Membership can only be accepted once, so it could be a good idea to add the unique-together option. Nonetheless, if we have several Membership with same from_user, this is what happens:

>>> # <Insert several Membership from one to another User>
>>> Membership.objects.filter(from_user__id=1, to_user__id=2).count()
3
>>> users = User.objects.annotate(member_from__id=F('member_from__id'))
>>> print('User Membership')
... for user in users:
...    print("%4d %10d" % (user.id, user.member_from__id))
User Membership
   1          1
   2          2
   1          3  # User 1 again
   1          4  # User 1 again
   3          None

So the value "accepted" come from the latest Membership instance of the User ?

stalk · Accepted Answer · 2013-11-24 12:46:14Z

4

Currently i found a solution only using raw query.

Simplified query for user.friends.all() is:

SELECT "users_user"."id", "users_user"."name", FROM "users_user" INNER JOIN "users_membership" ON ("users_user"."id" = "users_membership"."to_user_id") WHERE "users_membership"."from_user_id" = 10;

As we can see, users_membership table is already joined. So, i copy this query and just add a "users_membership"."status" field. Then, i create a methon in my model friends_with_status and insert new SQL query into raw queryset method. My User models:

class User(models.Model):
    name = models.CharField(max_length=50)
    friends = models.ManyToManyField('self', through='Membership',
        blank=True, null=True, symmetrical=False)


    def friends_with_status(self):
        return User.objects.raw('SELECT "users_membership"."status", "users_user"."id", "users_user"."name", FROM "users_user" INNER JOIN "users_membership" ON ("users_user"."id" = "users_membership"."to_user_id") WHERE "users_membership"."from_user_id" = %s;', [self.pk])

Now, i use this:

>>> user = User.objects.get(name="John")
>>> friends = user.friends_with_status()
>>> friends[0].status
'accepted'
>>> friends[1].status
'thinking'

P.S.

Of course, this includes all disadvantages of raw query: it is not possible to apply any further queryset methods on it, i.e. this will not work:

>>> friends = user.friends_with_status().filter()
>>> friends = user.friends_with_status().exclude()

and so on. Also, if i modify model fields, i have to modify the raw query also.

But at least, such approach gives me what i need in one query.

I think, it will be useful to write some annotation method, like Count or Avg, that will allow to attach fields from joined table. Something like this:

>>> from todo_my_annotations import JoinedField
>>> user = User.objects.get(name="John")
>>> friends = user.friends.annotate(status=JoinedField('member_from__status'))

edited Nov 24, 2013 at 12:46

answered Nov 24, 2013 at 11:27

stalk

12.1k4 gold badges39 silver badges58 bronze badges

3 Comments

Ian weisberger Over a year ago

I'm glad to see I'm not the only person with this issue.... I had thought that the only way to do this was with raw SQL and you confirmed my suspicion. I wish there was a better way...

Mark Over a year ago

Maybe you could use .extra(select={"status": my_query}) to not lose the queryset methods?

ofey Over a year ago

I also have this problem. It is surprising that it can't be solved with raw sql and a pity also as I would like to be able to use further chaining. stackoverflow.com/questions/37901376/…

Collectives™ on Stack Overflow

Django queryset attach or annotate related object field

2 Answers 2

In answer to comments

1 Comment

P.S.

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

In answer to comments

1 Comment

P.S.

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related