Selecting only the latest post for each url

Question

I have an app on GAE that lets user add\edit posts to any arbitrary path (like a wiki). I am storing all the posts in a single table. The table is structured as follows:

class WikiPosts(db.Model):
    path = db.StringProperty(required = True)
    content = db.TextProperty(required = True)
    date_created = db.DateTimeProperty(auto_now_add = True)

On the home page I want to display the latest post for each path.

My question is similar to this one ( Select first row in each GROUP BY group? ) but the answers involve using join which is not possible in GAE.

I can have a dedicated field to keep track of the latest post for each url but is it possible to do it using gql query?

As of now, I am using this query which returns all the versions of all the wiki posts sorted by their creation time.

db.GqlQuery("SELECT * FROM WikiPosts ORDER BY date_created DESC limit=10")

Do you have a unique list of all paths stored somewhere else? If so, you could do a query for each path and get the latest. This would result in greater number of operations, but might be faster than trying to compute unique paths on the fly. — Sologoub, Jul 30 '12 at 18:08
No, I don't have such list. I only have the WikiPosts table. — Nitish Parkar, Jul 31 '12 at 11:07

score 1 · Accepted Answer · edited May 23 '17 at 12:25

Since you do not have a unique list of paths, and since GAE does not support the equivalent of SQL's SELECT DISTINCT (see here, here, and here), you'll have to

generate that list every time the home page is displayed (not recommended once you exceed a few hundred posts), or
create another table/model to keep track of unique paths as new posts are added (then use this new table in combination with Shay's answer), or
as you stated, keep track of the latest post per path.

I think option 3 is your best bet, since (as is often the case with GAE), you would be putting into the datastore exactly what you want to get out (i.e., make writes more complicated in favor of quick reads.)

Thanks, I'll add a new field then – Nitish Parkar Aug 01 '12 at 14:51 — Nitish Parkar, Aug 01 '12 at 14:51

score 0 · Answer 2 · answered Jul 30 '12 at 18:26

0

You can call:

def get_latest_posts(amount=10):
  return db.GqlQuery("SELECT * FROM WikiPosts where path =:1 ORDER BY date_created DESC limit=:2", path, amount)

This will return for each path the latest posts.

answered Jul 30 '12 at 18:26

Shay Erlichmen

31,691
7
68
87

What value should I assign to the path variable? I do not have any list of paths. – Nitish Parkar Jul 31 '12 at 11:13

score 0 · Answer 3 · answered Aug 03 '12 at 04:01

There are a lot of issues related to doing a query for every page visit. Certainly you may have higher number of instances and will have to contend with eventual consistency for the index supporting the query. Since your page is just a top ten list, why not simply manage it with a memcache object? If the object is not found, then do your query and write the missing memcache object. Posting list update would write/rewrite memcache before the entity put() -- which might be good candidate for a deferred function (task queue).

Selecting only the latest post for each url

3 Answers3