JPA and many, many relationships in the google engine

Question

I have entities A and B, and A can have many B. The same instance of B can belong to several A. Thus, there is a classic many-to-many relationship.

GAE does not directly support many-to-many relationships; instead, they offer the ability to use key sets for related relationships. Therefore, in I will save the set of record keys in B.

Now the problem is this: how can I request objects of type B belonging to this object of type A and match specific criteria? In simple SQL, I would do this:

select B.* from B inner join A on B.A_ID=A.ID where B.property0=criteria1 and B.property1=criteria2 ... and ... 

but since I cannot execute JOIN, I need to do something like

 select B.* from B where B.A_ID in ( ... ) and B.property0=criteria1 and B.property1=criteria2 ... and ... 

therefore, the request itself can be very long due to the number of identifiers.

Is there a better way?

Answer 1

If you reorganize your relationship mapping, you may get a better request. Instead of storing the key set in A, save the key set in B. Then you can query with

 select * from B where a_id = {idOfRelevantA} and property0 = {criterion0} and property1 = {criterion1}... 

This way you avoid the few queries that the in statement creates.

Also, be careful: in will only work for a list of 30 items or less.