Help with Django ORM query

Question

I have these two Django models (simplified):

class Movie(models.Model):
    title = models.CharField(max_length=255)
    year = models.IntegerField(max_length=4)
    rating = models.DecimalField(max_digits=2, decimal_places=1, default=0)


class Request(models.Model):
    movie = models.ForeignKey(Movie)
    user = models.ForeignKey(User)
    language = models.CharField(max_length=7, choices=settings.APP_LANGUAGES, db_index=True)
    notes = models.CharField(max_length=255, blank=True, null=True)
    added = models.DateTimeField(default=datetime.now)

I want to get all Requests with a count by language for each movie. I actually come up with a solution with an ORM query, which gives me the results I am looking for:

reqs = Request.objects.values('movie', 'langauge').annotate(Count('language'))

However, I will need an access to other Movie attributes while I will be rendering the results in the template. Obviously, the above query returns ValueQuerySet like:

[{'movie': 460, 'language__count': 1, 'language': u'es-mx'}, {'movie': 458, 'language__count': 2, 'language': u'cs'}, {'movie': 459, 'language__count': 1, 'language': u'el'}]

Thus something like reqs[0].movie.rating won't work. So, how should I access other Movie attributes?

Thank you.

Is SubtitleRequest supposed to be the same model as Request? — Bryce Siedschlaw
– Bryce Siedschlaw, Commented May 16, 2011 at 18:11
So, is there a problem with reqs = Request.objects.annotate(Count('language')).select_related('movie')? — Mike DeSimone
– Mike DeSimone, Commented May 17, 2011 at 3:47
@Mike DeSimon the problem with your query is that it doesn't group by language, i.e. I'd get language__count 1 for all results — Daniel Grezo
– Daniel Grezo, Commented May 17, 2011 at 9:05

Sergey Golovchenko · Accepted Answer · 2011-05-19 03:12:27Z

1

You can do something like this:

reqs = Request.objects.values('movie', 'movie__rating', 'movie__title', 'movie__year', 'language').annotate(Count('language'))
print reqs

here is sample output:

[
    {'movie__year': 2010, 'language': u'EN', 'movie__title': u'Foo', 'movie': 1, 'language__count': 1, 'movie__rating': Decimal('1')}, 
    {'movie__year': 2010, 'language': u'ES', 'movie__title': u'Foo', 'movie': 1, 'language__count': 1, 'movie__rating': Decimal('1')}, 
    {'movie__year': 2010, 'language': u'RU', 'movie__title': u'Foo', 'movie': 1, 'language__count': 1, 'movie__rating': Decimal('1')}, 
    {'movie__year': 1998, 'language': u'EN', 'movie__title': u'Bar', 'movie': 2, 'language__count': 3, 'movie__rating': Decimal('9')}, 
    {'movie__year': 1998, 'language': u'RU', 'movie__title': u'Bar', 'movie': 2, 'language__count': 1, 'movie__rating': Decimal('9')}, 
]

you can see that returning extra movie attributes doesn't change the count, here is sample output from your original query:

reqs = Request.objects.values('movie', 'language').annotate(Count('language'))
print reqs

this produces output:

[
    {'movie': 1, 'language__count': 1, 'language': u'EN'}, 
    {'movie': 1, 'language__count': 1, 'language': u'ES'}, 
    {'movie': 1, 'language__count': 1, 'language': u'RU'}, 
    {'movie': 2, 'language__count': 3, 'language': u'EN'}, 
    {'movie': 2, 'language__count': 1, 'language': u'RU'}, 
]

EDIT

If you want to be able to call methods on the movie then you want to select the movies with you query, not the requests:

movies = Movie.objects.annotate(num_lang=Count('request__language')).all()

This will return all the movies, annotated by request's language count, which you can access via property *num_lang* on the movie. You will also be able to call all the methods on the movies objects

edited May 19, 2011 at 3:12

answered May 16, 2011 at 20:02

Sergey Golovchenko

18.8k15 gold badges58 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Daniel Roseman Over a year ago

No, because all those fields will be considered part of the GROUP BY.

Sergey Golovchenko Over a year ago

@Daniel Roseman, actually that's works just fine. The reason is that the 'movie' returns the id, which already uniquely identifies the movie. If we return any extra attributes for the movie, it is not going to affect the "group by". I've added sample output.

Daniel Grezo Over a year ago

OK, this is quite good - seems to work the way I need. But is there some way so that I will be able to access Movie methods as well? I guess I would need to use reqs = Request.objects.values('movie', 'language').annotate(Count('language')) and then select Movies where id__in=[r.movie for r in reqs], and then somehow map those together. But I don't know what would be an optimal way to do such mapping - any ideas?

Sergey Golovchenko Over a year ago

@danger, I've updated my answer to show how you can call Movie methods. Hope this helps.

Daniel Grezo Over a year ago

thanks; I have been there, but that one doesn't actually return counts per language and I still need an access to the value of language without making N db queries (select_related() wouldn't work there, I guess). So I still think I need to do my mapping manually and I haven't come to something optimal yet.

Collectives™ on Stack Overflow

Help with Django ORM query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related