CODEWITHSUNDEEP
google-app-enginenosqlgoogle-cloud-datastoregoogle-cloud-sql
David M
David M

Reputation: 21

AppEngine Filter Inequality with list alternatives

Currently, I have the following model structure for an AppEngine application using the DataStore (abbreviated for clarity):

class User(db.Model):
    name = db.StringProperty()
class Game(db.Model):
    name = db.StringProperty()
class UserGame(db.Model):
    user = db.ReferenceProperty(User)
    game = db.ReferenceProperty(Game)
    high_score = db.IntegerProperty()

There are many games, and there are many users. I'm trying to query the list of games that a user hasn't joined, similar to the (pseudo) SQL equivalent of SELECT * from Game where name NOT IN (SELECT * from UserGame where user = <<current_user>>)

My first approach was to get a list of UserGames and iterate through that list, adding .filter('__key__ !=',<<user_key>>) but it's not possible to chain inequality filters.

My question is, are there any better modeling ideas people can imagine that would get the data I need or should I migrate everything to CloudSQL? I don't see how I can grab this information at the datastore level with the current models I'm using.

I'm aware that I could do this programmatically by keeping the list of UserGame for that user, grab Game.all(), and programmatically filter out the results, but this wouldn't be ideal since I'd like to use a Cursor to paginate results.

Thanks for your help everyone!

Upvotes: 0

Views: 258

Answers (3)

David M
David M

Reputation: 21

Ok, I figured this out. It turns out you do have to use the undocumented __key__ property to filter your query. Basically, you use the DataStore to query the entities you want to filter out first, similar to the following:

joined_list = models.UserGame.all()
joined_list.filter('user =', <<current_user>>)

Then you iterate through that and filter out using !=, like so:

for joined in joined_list:
    games.filter('__key__ !=', joined.key())

The only catch here is that you can't use an inequality on any other property (DataStore limitation).

That will give you a list of all games that a user hasn't joined yet.

Hope this helps someone else out there!

Upvotes: 0

Thanos Makris
Thanos Makris

Reputation: 3115

There could be a "different" way to do this if your games are fixed. Specifically, if the games do not change in time, then you could initialize the User model with a property having all games, and for each game the User joins, remove it from the list. Thus, you could always have the list of non joined games.

Also, in order to avoid the huge amount of write operations, you could have this property as unindexed.

Upvotes: 0

voscausa
voscausa

Reputation: 11706

You can use a fast projection query to get a list of game keys for a user. And when you have a list of all the game keys you can use Python sets to find your answer. To get a list of all the games keys, you can do a fast key_only query and save (pickle) this list if you need to optimize further.

And here is a nice video about appengine: SQL vs NoSQL: Battle of the Backends https://developers.google.com/events/io/sessions/gooio2012/306/

Upvotes: 1

Related Questions