How to order by the field stored in the separate m

Question

Here is simplified version of my datastore structure:

class News(db.Model):
    title = db.StringProperty()

class NewsRating(db.Model):
    user = db.IntegerProperty()
    rating = db.IntegerProperty()
    news = db.ReferenceProperty(News)

Now I need to display all news sorted by their total rating (sum of different users ratings). How can I do that in the following code:

news = News.all()
# filter by additional parms
# news.filter("city =", "1")
news.order("-added") # ?
for one_news in news:
    self.response.out.write(one_news.title()+'<br>')

Answer 1

To do that you would need to run a query fetching every single NewsRating referencing your News entity and sum all the ratings (as the datastore does not provide JOINs). This will be a huge task both time and cost wise. I'd recommend to take a look at just-overheard-it example as a reference point.

Answer 2

Queries only have access to the entity you're querying against, if you have a property from another entity (or some aggregate calculation based on fields from other entities) that you want to use to order results, you're going to need to store it in the entity you're querying against.

In the case of ratings, that might mean a periodic task that sums up ratings and distributes them to articles.