Any solution for a join on app engine?

Question

Hi,

I have the following data model:

class StadiumOccupant {
  String stadiumname;
  String username;
}

class Friend {
  String username;
  String friendname;
}

I want to find all users at a stadium that are also my friends. I can do that like this:

List<String> friendsAtStadium;
String stadiumId = 'xyz';

List<Friend> friends = select from Friend where username = 'me';
for (Friend friend : friends) {
  List<StadiumOccupant> friendAtStadium = 
    select from StadiumOccupant where 
    stadiumname = stadiumId and
    username = friend.friendname;
  friendsAtStadium.addAll(friendAtStadium);
}

This can work with a very small number of objects. As soon as a user has more than a handful of friends, this won't work. Is there a solution to this on app engine? I can't think of one that performs well.

Thanks

-------- Some sample data --------

Friend { john, tim }
Friend { john, mary }
Friend { mary, frank }

StadiumOccupant { abc, tim }
StadiumOccupant { abc, frank }
StadiumOccupant { abc, kim }

so if I am user 'john', my friends are: { tim, mary }. if I were to run the stadium query with stadiumname='abc' I should get back { tim }.

Answer 1

For small amounts of data, you can do what you propose. For larger amounts of data, you wouldn't be doing a join anyway, even if you were using a SQL database for your backend.

Your problem is pretty much the typical social networking data problem. Most likely, you will want to denormalize your data, and "push" information to all of a user's friend list whenever they update their status, or possibly pull it as you are doing above. The best solution depends on the shape of your data and what kinds of queries you want to optimize for - retrieval or updates.

See my answer here for more info, including a post by a facebook engineer.