CODEWITHSUNDEEP
mysqlsqldjango
suneet
suneet

Reputation: 400

JOIN two tables containing same foreign key in Django

So I have two models containing primary key 'User'

class Reviews(models.Model):
    subject_user = models.ForeignKey(User,related_name='reviewed_user')
    actor = models.ForeignKey(User)                         ## irrelavent 
    text = models.TextField()

class Friendship(models.Model):
    head_user = models.ForeignKey(User,related_name='followed')
    tail_user = models.ForeignKey(User)                     ## irrelavent

Now I want to find all the reviews of users who are also in the Friendsip.head_user. For example, say User '1' wrote a review each for User '3' and User '5'

Reviews:
subject_user_id     actor_id     text
3                   1            LoremIpsum1
5                   1            LoremIpsum2

Friendship:
head_user           tail_user
3                   9
8                   9      ### Irrelavent

Now I output should be reviews object corresponding to 'LoremIpsum1' i.e. the review object whose subject_user is also present in Friendship.head_user. I tried the following

reviews = Reviews.objects.all().select_related('friendship__head_user')

But it is giving me all the reviews

Edit:

This is how I insert the review:

review, created = Reviews.object.get_or_create(subject_user=target, actor=actor)
review.text = NewReviewText
review.save()

Upvotes: 2

Views: 2171

Answers (1)

user764357
user764357

Reputation:

If I understand you correctly, this should work:

heads = (f.head_user for f in Friendship.objects.all())
reviews = Reviews.objects.all().filter("subject_user__in"=heads)

First you grab all the head users in Friendships and then find all their reviews, which seems to match your requirement:

I want to find all the reviews of users who are also in the Friendship.head_user

The reason you can't use select_related is that it expects that there will be an explicit relationship between the two models, which in your case there is not. There is no foreign key defined between a Review and a Friendship, so in this case the most optimal way is probably what I've proposed.

However, until you've tested this in anger and done some analysis to confirm is is a critical slow point in your system, I'd heed the words of Donald Knuth:

We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.

Upvotes: 1

Related Questions