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

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.

On Tuesday, September 24, 2013 10:50:54 AM UTC-3, Arthur Silva wrote:

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!

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/e38830e9-51a6-4ed2-afa9-dc2b44c8110f%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.