I'm having trouble getting the Django ORM request to work correctly. I have a friendship model:
class Friendship(models.Model): user1 = models.ForeignKey(User, related_name='friendships1') user2 = models.ForeignKey(User, related_name='friendships2') class Meta: unique_together = ('user1', 'user2',)
To find friends for a given user, we need to check user1 and user2 because we can never be sure which side of the relationship they will be on. So, to get all the friends for this user, I use the following query:
user = request.user User.objects.filter( Q(friendships1__user2=user, friendships1__status__in=statuses) | Q(friendships2__user1=user, friendships2__status__in=statuses) )
It seems to me that this should work, but it is not. This gives me duplicates. Here is the SQL that it generates:
SELECT auth_user.* FROM auth_user LEFT OUTER JOIN profile_friendship ON (auth_user.id = profile_friendship.user1_id) LEFT OUTER JOIN profile_friendship T4 ON (auth_user.id = T4.user2_id) WHERE ( (profile_friendship.status IN ('Accepted') AND profile_friendship.user2_id = 1 ) OR (T4.user1_id = 1 AND T4.status IN ('Accepted')) );
Here is the SQL I want that gives the correct results:
SELECT f1.id as f1id, f2.id AS f2id, u.* FROM auth_user u LEFT OUTER JOIN profile_friendship f1 ON (u.id = f1.user1_id AND f1.user2_id = 1 AND f1.status IN ('Accepted')) LEFT OUTER JOIN profile_friendship f2 ON (u.id = f2.user2_id AND f2.user1_id = 1 AND f2.status IN ('Accepted')) WHERE f1.id IS NOT NULL OR f2.id IS NOT NULL
I know that I can do this in a raw request, but then I don’t think I can connect. Is there a good clean way to do this without going into raw?
source share