Subqueries on Java GAE Datastore

Question

I am trying to create a database of users with connection between users (friends list). There are 2 main tables: UserEntity (main field id) and FriendEntity with fields: - initiatorId - id of user who initiated the friendship - friendId - id of user who has been invited.

Now I am trying to fetch all friends of one particular user and encountered some problems with using subqueries in JDO here.

Logically the query should be something like this: SQL: SELECT * FROM UserEntity WHERE EXISTS (SELECT * FORM FriendEntity WHERE (initiatorId == UserEntity.id && friendId == userId) || (friendId == UserEntity.id && initiatorId == userId))

or SELECT * FROM UserEntity WHERE userId IN (SELECT * FROM FriendEntity WHERE initiatorId == UserEntity.id) OR userId IN (SELECT * FROM FriendEntity WHERE friendId == UserEntity.id)

So to replicate the last query in JDOQL, I tried to do the following:

Query friendQuery = pm.newQuery(FriendEntity.class);
friendQuery.setFilter("initiatorId == uidParam");
friendQuery.setResult("friendId");

Query initiatorQuery = pm.newQuery(FriendEntity.class);
initiatorQuery.setFilter("friendId == uidParam");
initiatorQuery.setResult("initiatorId");


Query query = pm.newQuery(UserEntity.class);
query.setFilter("initiatorQuery.contains(id) || friendQuery.contains(id)");
query.addSubquery(initiatorQuery, "List initiatorQuery", null, "String uidParam");
query.addSubquery(friendQuery, "List friendQuery", null, "String uidParam");
query.declareParameters("String uidParam");

List<UserEntity> friends = (List<UserEntity>) query.execute(userId);

In result I get the following error: Unsupported method while parsing expression.

Could anyone help with this query please?

Answer 1

You are trying to do a join - You cannot do joins across Entities.

Here is an example of doing a simple join if there is an owned relationship http://gae-java-persistence.blogspot.com/2010/03/executing-simple-joins-across-owned.html

Also look at that web site it has a lot of really good information on getting the persistence stuff to work. as well as a couple of work-arounds for joins.

You can set up relationships among Entities, for that see GAE docs.