What's the best django way to do a query that spans several tables?

Question

I have a reviews/ratings web application, a la Digg. My django app content has the following model:

class Content(models.Model):
    title = models.CharField(max_length=128)
    url = models.URLField(max_length=2048)
    description = models.TextField(blank=True)

class Recommendation(models.Model):
    user = models.ForeignKey(User)
    content = models.ForeignKey(Content)
    review = models.TextField()
    rating = models.PositiveSmallIntegerField()
    class Meta:
        unique_together = ('user', 'content')

class Subscription(models.Model):
    subscriber = models.ForeignKey(User, related_name='subscription_set')
    publisher = models.ForeignKey(User, related_name='publication_set')
    class Meta:
        unique_together = ('subscriber', 'publisher')

I want to construct a page with all the recommendations of all the users to whom a current user (request.user) subscribes.

If I write this in SQL, I believe I'll end up with a query similar to the following:

select content_content.*, content_recommendation.*, auth_user.*
from content_content, content_recommendation, content_subscription, auth_user
where content_content.id = content_recommendation.content_id
 and content_recommendation.user_id = content_subscription.publisher_id 
 and content_subscription.subscriber_id = ?
 and auth_user.id = content_subscription.publisher_id;

How would I express this using Django's query APIs? I've read the docs, but just can't get my head around it.

Answer 1

I think that it's:

  Content.objects.filter(recommendation_set__user__publication_set__subscriber__pk=request.user.pk)/.distinct()/

or

  Recommendation.objects.filter(user__publication_set__subscriber__pk=request.user.pk)/.distinct()/

-- depending on instances of which model you want to get. Distinct() might be needed to avoid duplicates.

Answer 2

I would use:

Recommendation.objects.filter(user__publication_set__subscriber=request.user).select_related()

That will get you all the Recommendation objects as you requested, and the select_related will load all the related User and Content objects into memory so that subsequent access of them won't hit the DB again.

How you'd contruct this query really has a lot to do with your handling of the returned data afterwards though. It may be more or less efficient to go one way vs. another based on what you do with it.