Wrong output from Many-To-Many relationship queryset in Django models

Question

I have the following Django models:

class User(models.Model):
    username = models.CharField(max_length=50, unique=True)
    name = models.CharField(max_length=100)

class Url(models.Model):
    user = models.ManyToManyField(User)
    url = models.URLField()

class Keywords(models.Model):
    url = models.ManyToManyField(Url)
    keyword = models.CharField(max_length=100)

Now, I want all the common keywords for any two users from the database:

username1 = 'user1'
username2 = 'user2'

Attempt 1:

Keyword.objects.filter(url__user__username=username1).filter(url__user__username=username2).values('keyword', 'url__url').distinct()

// Returns empty list []   [ Wrong ]

Attempt 2:

k1 = [ k.keyword for k in Keyword.objects.filter(url__user__username=username1) ]
k2 = [ k.keyword for k in Keyword.objects.filter(url__user__username=username2) ]

common_k = list(set(k1).intersection(set(k2)))
print common_k

// Return list of common keys (As Expected) [ Correct ]

What am I doing wrong in Attempt 1?

Please note: at first .filter(url__user__username=username1).filter(url__user__username=username2) seems wrong, but it has a many-to-many relationship in which it should work.

Test input for Attempt 1

newuser1 = User.objects.get(username='newuser1')
newuser2 = User.objects.get(username='newuser2')

url = Url(url='http://common.com/')
url.save()
url.user.add(newuser1)
url.user.add(newuser2)

key = Keyword(keyword='common')
key.save()
key.url.add(url)

Now, I tried Attempt 1 and Attempt 2 on this and got correct result as expected. I get common as keyword for newuser1 and newuser2.

Now, Attempt 2 is definitely correct, then what am I doing wrong in Attempt 1?

I think _Attempt1_ should also work. Can you run that query step by step in django shell and verify. — Rohan, Aug 06 '12 at 11:06
@Rohan Actually, in django shell I was trying to debug and then came across Attempt2. Attempt1 has only 1 step. — Yugal Jindle, Aug 06 '12 at 11:13
I tried similar query and that works as expected, hence I asked to review step by step if there is some typo or other mistake. — Rohan, Aug 06 '12 at 13:23

Vic Smith · Answer 1 · 2012-08-06T13:55:23.440

2

Attempt1 is filtering the urls down to those with a user with name username1, and then filtering the results of that query to those with user with name username2. But the first list can only contain those with username1, so the result of the second filter will always be empty.

However, you find an intersection between these two queries in Attempt2, rather than applying them in sequence. This is quite different, and will give the correct answer.

You could add this method to your user class, so that you can do user1.commonKeywordsWithUser(user2), ie

class User(models.Model):
    username = models.CharField(max_length=50, unique=True)
    name = models.CharField(max_length=100)

    def commonKeywordsWithUser(user):
        k1 = [ k.keyword for k in Keyword.objects.filter(url__user__username=self.name) ]
        k2 = [ k.keyword for k in Keyword.objects.filter(url__user__username=user.name) ]

        return list(set(k1).intersection(set(k2)))

edited Aug 06 '12 at 13:55

answered Aug 06 '12 at 10:39

Vic Smith

3,477
1
18
29

Please see the update.. and help correcting Attemp1. I want to keep the query concise and not like Attempt2. – Yugal Jindle Aug 06 '12 at 10:43
I don't think the Django ORM supports the kind of intersection operation that you require, but you can do it in straight python after doing the queries as you have found. Is there a reason you don't wish to use the second method? – Vic Smith Aug 06 '12 at 10:50
What you suggest that it takes the url of user1 - If that is the case then its a serious issue in django queryset. Since this info is not getting reflected in the query. Please double-check. – Yugal Jindle Aug 06 '12 at 11:15
No, I would suggest a method using the exact code from **Attempt2** added to the user model class. Have updated the answer with example. – Vic Smith Aug 06 '12 at 13:56
It may also be worth checking out this answer: http://stackoverflow.com/questions/108193/union-and-intersect-in-django – Vic Smith Aug 06 '12 at 14:13
Well, :) You are presenting your answer as a method. Well, I would do the same. But the problem is I want to achieve the intersection on database level rather then on Python's end. – Yugal Jindle Aug 06 '12 at 17:09
I have already seen the question that you suggested, my problem is because I have 2 many-to-many relationships chained together. – Yugal Jindle Aug 06 '12 at 17:11

Zubair Afzal · Answer 2 · 2012-08-07T04:57:59.177

0

set_1 = set(Keyword.objects.filter(url__user__username=username1).values_list('keyword'))
set_2 = set(Keyword.objects.filter(url__user__username=username2).values_list('keyword'))
common_keywords = list(set_1 & set_2)

Solution with single Query:

from django.db.models import Q
Keyword.objects.filter(url__user__username=username1).exclude(~Q(url__user__username=username2)).values('keyword', 'url__url').distinct()

Solution with single Query(different urls from 2 users yielding same keyword not removed):

from django.db.models import Q
Keyword.objects.filter(url__user__username=username1).exclude(~Q(url__user__username=username2)).values('keyword', 'url__url').distinct('keyword', 'url__url')

edited Aug 07 '12 at 04:57

answered Aug 06 '12 at 11:07

Zubair Afzal

2,016
20
29

That is logically wrong, since you are taking the `union` and then performing distinct. I need the intersection. – Yugal Jindle Aug 06 '12 at 11:12
@YugalJindle Sorry, I didn't understood your question. Now Please check this solution. – Zubair Afzal Aug 06 '12 at 11:53
This solution is not what was desired, since this intersects in python. Imagine a situation with 10 millions rows. – Yugal Jindle Aug 06 '12 at 13:04
This is what I want to achieve but in db itself. – Yugal Jindle Aug 06 '12 at 13:05
Well.. problem is in Attempt1 that contains only 1 step. I broke it down into steps and that formed Attempt2 that is working fine. But I don't want to intersect in python, so lies Attempt1 with no explanations ? – Yugal Jindle Aug 06 '12 at 13:25
@YugalJindle I have updated my answer with your required conditions (solution with single query). – Zubair Afzal Aug 06 '12 at 13:33
Doesn't work, you are doing the same mistake as I did. Read Vic Smith's answer. He has pointed out the mistake at least. But not able to solve it with Attempt1. – Yugal Jindle Aug 06 '12 at 13:44
Case when 1 url is common to 2 users is working, but when different urls from 2 users yielding same keyword is getting lost in the query. – Yugal Jindle Aug 06 '12 at 13:45
@YugalJindle you can use distinct for getting keywords with different urls. I have updated my answer. – Zubair Afzal Aug 07 '12 at 05:00

Sandip Agarwal · Accepted Answer · 2012-08-07T06:05:11.687

0

You could try something like the following (for Attempt 1):

Keyword.objects.filter(url__user__username=username1, keyword__in=Keyword.objects.filter(url__user__username=username2).values_list('keyword', flat=True)).values_list('keyword', flat=True).distinct()

edited Aug 07 '12 at 06:05

answered Aug 07 '12 at 05:25

Sandip Agarwal

1,890
5
28
42

Wrong output from Many-To-Many relationship queryset in Django models

3 Answers3