EXPAND Functionality on relational entities
When working with relational databases, Onesait Platform Entities/Ontologies are mapped to tables, and tables are related to each other (1-1, 1-N, N-N relationships). The Platform allows Entities to be related by means of a new functionality that we have incorporated in this 5.3.0-Ultimate version: the EXPAND functionality.
This new functionality will allow us that when making a query, insertion or update, the Platform automatically knows how to work with the Entities related to the parent Entity and returns the data of these.
This functionality is especially interesting to use together with the Platform Forms (OP Forms), since it allows us to work with several Entities from the same user interface.
How to use the functionality?
To understand how the EXPAND functionality works, we will use an example with the tables USER → USER_TOKEN, which have a 1-N (ONE TO MANY) relationship.
Step 1: Creating relationships
Parent entity
First, we will create the relationships between the Entities in the Platform; the same ones that exist in our Java model or in the relational database itself.
To do this, we will go to the parent Entity (USER table), and we will add the relationship with USER_TOKEN through the already existing “Linked data” functionality.
Once the reference is added, we close the pop-up and the “_references” attribute will be added to the JSON Schema.
Child entity
In case it is a bidirectional relationship of the same level (access USER from USER_TOKEN) , we could add the relationship in the child Entity in the same way.
Step 2: Queries on the Entities
If we make a conventional selection, we will get the following:
Having created the relationships in the Entity model, now when doing a “JOIN” of the two tables, we will get the nested relationship, in this case as it is a ONE TO MANY it will be an array:
Step 3: Queries with EXPAND function
We have created a new function called “EXPAND” with which we can make queries that bring us all the data of the related entities without the need to make the query with the JOINS:
The EXPAND() function can receive arguments, in case you want to limit the nesting. For example, for the previous case, if we put EXPAND(users), which is the name of the Entity that relates the API table and the USER table in the database, it will only bring us expanded that attribute and the “api_operation” will not come:
If we indicate arguments in the EXPAND() function, we must take into account that we will only have one nesting level, in case the related entities have other relationships, these will not be shown, as it is the case of API->API_OPERATION->API_QUERY_PARAMETER.
Step 4: Insertions
For the example, we will use the Entity CRUD, although this applies equally for insertions through the API Manager, IoT Broker, etc.
We will be able to create with the same nested JSON structure the instances of both entities with a single insertion, for example:
We check the creation:
In case of trying to insert a record with an ID that already exists, an error will be returned.
Paso 5: Updates
For the example we will update a record from the CRUD of entities.
As in the insert, we can update the main record and the children in a single operation, nesting the instances in the JSON.
In the children, the “UPSERT” operation is contemplated; that is, if the record to be updated does not exist, it will be generated. Similarly, if a child related to the parent, which exists in the database, is not sent, it will be deleted.
For example, we are going to add to the instance that we have created before of user “administrator_test2” one more token and we are going to change the value “token” of the existing one:
If we check, we will see:
Now let’s update the user, leaving only the last token added “Token2“:
If you are interested in learning more aboutthis new EXPAND Functionality on Onesait Platform, feel free to leave us a comment or send us a notice on our Onesait Platform support channel.
Header Image: Dan Dennis at Unsplash.
✍🏻 Author(s)
