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

To make best use of Django, you have to avoid trying to do joins.

A "left outer join" is actually a list of objects with optional relationships.

It's simply a list of Events, Event.objects.all(). Some Event objects have a rating, some don't.

You get the list of Eents in your view. You handle the optional relationships in your template.

{% for e in event_list %}
    {{ e }}
    {% if e.rating_set.all %}{{ e.rating_set }}{% endif %}
{% endfor %}

is a jumping-off point.

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.

Answer 3

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 4

I think you have to do something like this.

events=Event.objects.filter(rating__user=request.user.id)
ratings='(select rating from ratings where user_id=%d and event_id=event_events.id '%request.user.id
events=events.extra(select={'rating':ratings})

Answer 5

Hey,

I have a similar problem, except to rebuild the dict is a little more complex.

Is it not possible for the events "relational manager" to just act on the child table (using RatingManager.)

N