How to filter a parent entity using properties of child entity in datastore

Question

I am using Google App Engine (Java) for my REST backend and google-datastore as the database and using objectify to access the database.

I want to create a unit entity to store Units where a unit can be a component or an assembled unit , basically an assembled unit is made up of multiple components and also has some properties of its own. There can be multiple types of assembled units and multiple types of components. The Entity class would be something like

public class UnitEntity {
Long unitId;
String serialNumber;
String state;
String unitType;
Long parentId;// -> this would be null for all assembled units and in case of components, if they are part of any assembled unit, it will be the Id of the assembled unit (This is added so that I can list all components of a particular assembled unit)
UnitParameters unitParameters;
}

Here UnitParameters would be a polymorphic class to contain properties specific to a unit type, that is, based on the value of "unitType", there would be different classes which extend "UnitParameters".

Let's assume one the components (let's say component1, that is , unitType=component1) has a property called modelNumber. This property would be stored in the unitParameters of the all the entities where unitType=component1.

Now I want to able to list units where unitType=assembledUnit1 and which have a child component1 whose modelNumber is 2.0.

(I can easily get list units of type component1 where modelNumber is 2.0 , but I want to be able to get the parent entity also)

So basically here I am trying to get parent entities by filtering on the properties of children. I want to know whether this is possible with datastore and objectify? Is there any way to achieve this functionality?

Update - Follow-up question based on the Answer by @stickfigure:

If I go with google cloud sql (which is based on mysql) for my use case, then how should I model my data ?

I initially thought of having a table for each unitType. Let's say there are 3 unitTypes - assembledUnit1, assembledUnit2 and component1. Now if I want to have an API which lists the details of each unit , how can I achieve this with cloud sql.

This is something I could have done with datastore since all the entities were of the same "kind".

I can obviously have separate APIs to list all units of type assembledUnit1, assembledUnit2 etc., but how can I have a single API which could list could list all the units ?

Also in this approach, if someone calls the REST API GET /units/{unitId} , I suppose I would have to check for the unitId in each of the tables which doesn't seem correct?

I suppose one way by which this could be solved is to just have one table called "Unit" whose columns would be a superset of the columns of all the unitTypes. However I don't think this is a good way of designing since there would be a lot of empty columns for each row and also the schema would have to be changed if a new unitType is added.

stickfigure stickfigure · Accepted Answer · 2017-12-28T17:04:24

The datastore doesn’t do joins. So you’re left with two options, either 1) do the join yourself via fetching or 2) denormalize some of the child data into the parent and index it there. Which strategy works best will vary depending on the shape of your data and performance/cost considerations.

I should add there is a third option which is “store an external index of some of your data in another kind of database, such as the search api or an RDBMS”.

This is not always a very satisfying answer - the ability to do joins and aggregations in an RDBMS is incredibly useful. If you have highly relational data with modest size/traffic/reliability requirements, you may want to use something like Cloud SQL instead.

How to filter a parent entity using properties of child entity in datastore

1 Answers