Subqueries on Java GAE Datastore
- by Dmitry
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?
