Django - filtering on related objects

Question

For my Django app I have Events, Ratings, and Users. Ratings are related to Events and Users through a foreign keys. When displaying a list of Events I want to filter the ratings of the Event by a user_id so I know if an event has been rated by the user.

If I do:

event_list = Event.objects.filter(rating__user=request.user.id)

(request.user.id gives the user_id of the current logged in user) ...then I only get the events that are rated by the user and not the entire list of events.

What I need can be generated through the custom SQL:

SELECT *
FROM `events_event`
LEFT OUTER JOIN (
  SELECT *
  FROM `events_rating`
  WHERE user_id = ##
  ) AS temp 
ON events_event.id = temp.user_id

Is there an easier way so I don't have to use custom SQL?

Answer 1

The filter method is for filtering which objects are returned based on the specified criteria, so it's not what you want here. One option is to do a second query to retrieve all ratings for given Event objects for the current User.

Models:

import collections

from django.db import models

class RatingManager(models.Manager):
    def get_for_user(self, events, user):
        ratings = self.filter(event__in=[event.id for event in events],
                              user=user)
        rating_dict = collections.defaultdict(lambda: None)
        for rating in ratings:
            rating_dict[rating.event_id] = rating
        return rating_dict

class Rating(models.Model):
    # ...
    objects = RatingManager()

View:

events = Event.objects.all()
user_ratings = Rating.objects.get_for_user(events, request.user)
context = {
    'events': [(event, user_ratings[event.id]) for event in events],
}

Template:

{% for event, user_rating in events %}
  {% if user_rating %} ... {% endif %}
{% endfor %}

Answer 2

In addition to S.Lott's suggestion, you may consider using select_related() to limit the number of database queries; otherwise your template will do a query on each event's pass through the loop.

Event.objects.all().select_related(depth=1)

The depth parameter is not required, but if your other models have additional foreign keys it will limit the number of joins.