# Left join with User

**URL:** <https://forum.djangoproject.com/t/left-join-with-user/16934>\
**Category:** Using the ORM\
**Created:** [November 10, 2022, 6:53pm UTC](https://forum.djangoproject.com/t/left-join-with-user/16934 "2022-11-10T18:53:09Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jmvermee](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/jmvermee/32/8093_2.png) [@jmvermee](https://forum.djangoproject.com/u/jmvermee)\
**Post date:** [November 10, 2022, 6:53pm UTC](https://forum.djangoproject.com/t/left-join-with-user/16934/1 "2022-11-10T18:53:09Z")

</div>

I’ve got these models:

```auto
class User(AbstractUser):
    friends = models.ManyToManyField('self', through='Friend')
    friends_count = models.PositiveIntegerField(default=0)
    followers_count = models.PositiveIntegerField(default=0)

class Friend(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='user_friends', related_query_name='user_friend')
    friend = models.ForeignKey(User, on_delete=models.CASCADE, related_name='followers', related_query_name='follower')

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['user', 'friend'], name='unique_friend')
        ]

```

In a function based view I want to retrieve all the users except the logged in user:

```auto
users = models.User.objects.exclude(id=request.user.id)

```

In the template I want to list the users and add a follow or unfollow button depending on whether the logged in user already follows the user or not. I think I need to join the users with the friend objects where user\_id is the id of the logged in user. I had a look at aggregation but wasn’t able to figure out how to get this right.

Kind regards.

Johanna

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [November 10, 2022, 8:24pm UTC](https://forum.djangoproject.com/t/left-join-with-user/16934/2 "2022-11-10T20:24:51Z")

</div>

Is a “follow” the same as making an entry in `Friend`?  
If so, then one way to do this is to get a list of friends and check to see if the users you are iterating over are in that list or not.

In other words, for a given `User` `a_user`, then the list of friends is `a_user.friends.all()`.  
If `other_user` is in `a_user.friends.all()`, then `other_user` is a `friend` of `a_user`. If `other_user` is not in that list, then no such association exists.

---

<div class="post-metadata">

**Author:** ![jmvermee](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/jmvermee/32/8093_2.png) [@jmvermee](https://forum.djangoproject.com/u/jmvermee)\
**Post date:** [November 11, 2022, 11:00am UTC](https://forum.djangoproject.com/t/left-join-with-user/16934/3 "2022-11-11T11:00:47Z")

</div>

Hi Ken,

Thanks for your reply.

Yes, follow is the same as making an entry into friend.

I had a look at the Queryset API again and wonder whether this could be done using

Based on the pizza example, I tried:

```auto
users = models.User.objects.annotate(has_friend=FilteredRelation('friends', condition=Q(friends__user_id=request.user.id)),).filter(has_friend__isnull=True)

```

This raises an error in the html file: `Exception Value: Related Field got invalid lookup: user_id` The html files contains a simple {{ users }} tag. Comparing my code to the pizza example I don’t see what causes this error.

Kind regards,  
Johanna

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [November 11, 2022, 11:48am UTC](https://forum.djangoproject.com/t/left-join-with-user/16934/4 "2022-11-11T11:48:31Z")

</div>

That’s because this clause:

> [@jmvermee](#):
>
> `friends__user_id=request.user.id`

Isn’t valid.

In a query, `friends` _is_ the reference to the table related by the many-to-many relationship and not a reference to the join table.

(If you wanted to reference the join table in the query, you would use the query\_name, `user_friend`.)

Django knows that when you’re referencing `friends` in a query that it needs to build the join through the join table.

This means that what you need is `friends__id=request.user.id`.

See the examples for Article and Publication at [Many-to-many relationships | Django documentation | Django](https://docs.djangoproject.com/en/4.1/topics/db/examples/many_to_many/#many-to-many-relationships)