Django ORM generating the wrong query for recent friends of an user

[Arthur Silva]

Here're my models


    class PingUser(AbstractUser):
        friends = models.ManyToManyField("self", through="Friendship", symmetrical=False)
   
    class Friendship(TimestampedModel, SerializeMixin):
        STATUS_CHOICES = (
            ("pending", "Pending"),
            ("friend", "Friend"),
            ("favorite", "Favorite")
        )
        user = models.ForeignKey(PingUser, related_name="friendships")
        friend = models.ForeignKey(PingUser, related_name="friendships_reverse")
        status = models.CharField(max_length=20, default=STATUS_CHOICES[0][0], choices=STATUS_CHOICES)

        class Meta:
                unique_together = [('user', 'friend')]



The code I'm using to get the most recent friends of user 4


    id = 4
    friendships = Friendship.objects.exclude(status="pending").filter(
                ~Q(user__friendships__status="pending"), user__friendships__friend_id=id).order_by("-created_at")


I also tried several combinations like grouping all filtering inside one filter() but the query is the same


Generated query


    SELECT `sample_app_friendship`.`id`,
           `sample_app_friendship`.`created_at`,
           `sample_app_friendship`.`updated_at`,
           `sample_app_friendship`.`user_id`,
           `sample_app_friendship`.`friend_id`,
           `sample_app_friendship`.`status`
    FROM `sample_app_friendship`
    INNER JOIN `sample_app_pinguser` ON (`sample_app_friendship`.`user_id` = `sample_app_pinguser`.`id`)
    INNER JOIN `sample_app_friendship` T5 ON (`sample_app_pinguser`.`id` = T5.`user_id`)
    WHERE (NOT (`sample_app_friendship`.`status` = 'pending')
           AND NOT ((`sample_app_friendship`.`user_id` IN
                       (SELECT U2.`user_id`
                        FROM `sample_app_friendship` U2
                        WHERE (U2.`status` = 'pending'
                               AND U2.`user_id` IS NOT NULL))
                     AND `sample_app_pinguser`.`id` IS NOT NULL))
           AND T5.`friend_id` = 4)



The desired query is actually


    SELECT `sample_app_friendship`.`id`,
           `sample_app_friendship`.`created_at`,
           `sample_app_friendship`.`updated_at`,
           `sample_app_friendship`.`user_id`,
           `sample_app_friendship`.`friend_id`,
           `sample_app_friendship`.`status`
    FROM `sample_app_friendship`
    INNER JOIN `sample_app_pinguser` ON (`sample_app_friendship`.`user_id` = `sample_app_pinguser`.`id`)
    INNER JOIN `sample_app_friendship` T5 ON (`sample_app_pinguser`.`id` = T5.`user_id`)
    WHERE (NOT (`sample_app_friendship`.`status` = 'pending')
           AND (NOT T5.`status` = 'pending')
           AND T5.`friend_id` = 4)

Someone more experienced can shed some light?

Thanks!

[Arthur Silva]

I still can't find the problem.  Django isn't using the same "friendship" joined table for both conditions which makes me wonder if it's related to this https://docs.djangoproject.com/en/1.5/topics/db/queries/#spanning-multi-valued-relationships

Please help.