Relationships are a core part of the Oracle database (that's why it is called a relational database after all) and the structure of business objects in Visual Builder. Accessing simple parent child relationships in Visual Builder is easy, but what if you have deeper levels of relationships? In the example below we'll cover a scenario of three levels of business objects – parent/child/granchild.
The top level is a BO called Locations – a list of cities.
In each location we have Departments – which is our second level BO.
And each Department has Employees – that's the third level BO.
The business objects diagram in Visual Builder will then look like this:
Enable Access Between Business Objects
As you can see in the diagram, each BO has access to its direct children object using a collection object.
At this stage you can, for example, drag the employees object onto a page as a table, and you'll have access to fetch the employee's details including the department they work in.
Note that, by default, you won't be able to go from the parent to the child – so for example if you drop location on the page as a table you won't be able to fetch the department in each location.
But you can fix this if you need such access.
For each BO you'll see the relationships in the BO overview tab:
You can click on the relationship to edit it. In most cases the relationship will only allow you to access the parent from the child so just one of the accessors would be enabled. If you want to also enable the other direction, you need to check to enable the accessor for that. Note that in this dialog you can also see the accessor name – which is useful if you need to issue REST calls as we'll see later.
Once done, you'll be able to drag the locations onto a page as a table and add the department name for each location. Note that in a regular table you'll only get a single department to show up. If you want to show more data you need a different UI component see our past blogs on using tree, a collapsible list, or nested tables.
Enabling Third Level Access
If you enable the accessors both from location to department, and from department to employees – you'll notice that when you come to bind the location table – you will not be able to go to the third level of hierarchy.
To enable this, go into the endpoints section of the locations BO, and click edit endpoints – you'll be able to expand the relationships and check the third level to enable access to it.
Now you can create a table that lists a location, the first department in it, and the first employee in it.
REST Access
If you'll check the REST call issues by the SDP that backs the table you'll see that the fields parameter will have a value like this to enable access across the levels:
city,id;departmentCollection:department;departmentCollection.employeesCollection:name
If you'll use this fields parameter value in a REST call issued to the BO, VB will know to traverse the relationships and get you the data.
Another parameter you could use is the expand parameter, which tells the BO engine to get additional data for the related objects. For a three level fetch you only need to specify the third level for example from the Location end point we can add the REST parameter expand pointing to departmentCollection.employeesCollection to get all three levels.
For more on REST access to business objects and relationship see our documentation section.