On Wed, Mar 24, 2010 at 2:57 PM, Henrik Genssen <[email protected]> wrote: > Hi, > > thanks for your answer! > >>>>Now is there a way to force select_related tables any way in a count? >> >>select_related() is an optimization for data retrieval. It allows you >>to expand a select row to include related objects in a single query. >> >>It only follows the "1" side of "1-N" relations. That is, if a book >>has a foreign key on author, Book has a single Author. If you request >>N books, you will need to issue N+1 SQL queries. With select_related, >>this becomes 1 SQL query, and each row in the result set includes the >>details of the book and the details of the single related author. > Yes, this is the way and intention, I use related, too. > > >>This *does not* affect the row count. The number of books doesn't >>change as a result of including related authors in the result set. >>Hence, select_related() clauses are removed prior to a call to >>count(). > But if you have a publisher foreign key on books, too, and you want to know > the amount of books one author has published at one publisher, > would I not do: > > data = author.objects.select_related('books', > 'books__publisher').filter(name__exact='Django Reihnard', > books__publisher__name__exact='music express') > amount = data.count() > > if now a have a 4th table locations with addresses of the publisher, I would > do: > data = author.objects.select_related('books', 'books__publisher', > 'books__publisher__locations').filter(name__exact='Django Reihnard', > books__publisher__name__exact='music express') > amount = data.count()
> For just getting the count, the join to locations is omitted, as the number > of rows stays the same without locations, as there is no filter on tlocations > (publisher has a foreign key on locations). > If I expand the filter to: > books__publisher__locations__country__exact='germany', the join is done on a > count, too > > (I use the variable data, as I first do a count on a query, afterwards I do > retrieve a *chunk* of data from the result set, so I do need to get the whole > amount of rows via the count()) > > Is this the wrong way to do it? Yes. As I said, select_related() is an optimization for data retrieval for the "1" side of "1-N" relations. It isn't designed to control query joins. If you're looking to do counts of related objects and you require joins, you should investigate the aggregate() and annotate() functions. Alternatively, you can use 'tables' and 'where' in the extra() clause to force include additional table joins. Another alternative is to use a raw SQL query. Django's ORM is nice for simple queries, but if you have a complex query requirement (and it sounds like you do), don't be afraid to go straight to the source. Yours, Russ Magee %-) -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
