Here’s an example (simplified) of what I’m trying to understand:
Table Organizations
OrganizationId
Name
Table OrganizationDetails
1:1 Relationship to OrganizationId
DetailInfo
In UI Builder I want to retrieve the Organization Names, and DetailInfo where DetailInfo equals some value
In SQL I would select Organization Name, DetailInfo from Organizations and join OrganizationDetails on OrganizationId where DetailInfo equals value.
In UI Builder if I load a table object from table Organization, there is no relationship to OrganizationDetails, as that relationship lives in OrganizationDetails.
Am I building my relationships the wrong way? I could have a 1:many Relationship in the Organizations table.
Do I need to load the table object OrganizationDetails and then use the relationship to the Organization table to get the Organization Name? That feel like getting the data backwards?
If there’s some documentation or a video that explains how to architect the schema using relationships, especially where there are multiple levels, that would be very helpful.
Data service supports queries with inverted relations. So your query will look in next way OrganizationDetails[organization].DetailInfo = 'some value'.
Am I building my relationships the wrong way?
It seems to me that relation from Organizations to OrganizationDetails will be more natural (Organization has organization details).
I could have a 1:many Relationship in the Organizations table.
Do you mean that one organization can have several details?
If there’s some documentation or a video that explains how to architect the schema using relationships, especially where there are multiple levels, that would be very helpful.
It is hard question to answer since there are a lot of details which should be considered.
Talking about general hints for Backendless. At first you should list all your tables. Then you should define relations between them. After that you should look carefully at each relation and define what table should be the main table in relation. This main table should contain relation column. That is it.
For example, in relation “organization-details” organization looks like a main table since OrganizationDetails can’t exist without Organization.
I hope it will help you to build correct database scheme.
In a typical table structure for my example the ID of the parent would be in the child table. So the child contains the relationship. This feels backwards to me, which is why I wanted the clarification.
Your suggestion is basically adding a join table to every relationship that isn’t 1:1.
My Organizations have multiple details so it’s a 1:many relationship. All of my joins are this way, otherwise, for the size of my application, any 1:1 would be in a single table.
You are talking about relationships from perspective of database. From this point of view you are absolutely right. But in perspective of entities this approach looks a little bit backward.
Relations in Backendless implemented in entity-oriented manner. Under the hood there will be database-oriented implementation which is different from what you can see in Data service. I would recommend you to think about relations in Backendless from perspective of entities.
If in most of the cases you will work with Organization table and load additionally OrganizationDetails I would recommend you to create 1:N relation from Organization table.
If I use Load Table Object it looks like I’m returned a structured object like this:
OrgA → Details:Detail1, Detail2
OrgB → Details:Detail3, Detail4, Detail5
How do I take that and display a tabular list like this
OrgA - Detail1
OrgA - Detail2
OrgB - Detail3
OrgB - Detail4
OrgB - Detail5
I’m used to running a SQL query that returns a uniform list of rows/columns and don’t understand how to interact with the data object correctly.
In @mark-piller examples (here and here), he’s either showing a 1:1 relationship or a nested relationship.
Thanks for lending a hand here. My question is how to render the data in the UI when it is from multiple tables in a one-to-many relationship.
My mental block is from previous programing experience where data from the DB always comes back in uniform columns.
The data returned in UI-Builder is JSON? I don’t know how to build tabular data from that using the Dynamic List Behavior, or anything else in UI-Builder for that matter.
The data in UI Builder will be returned as a collection of primary (Org) objects. The objects can be sorted if you specified a sorting column in the request when you load data. Each primary (Org) object will have related objects, also, again if you requested the related objects to be returned.
I am sure you will have follow on questions, please do not hesitate to ask.
Thanks, @mark-piller. I don’t want to abuse your time but am feeling really stuck on this one. I’ve watched a ton of your videos, but I can’t find one that shows (at least not that I understand) that addresses table joins and multi-table where cluases.
I’ll run through this from the top just so it’s perfectly clear what I’m trying to do and hopefully, you can point out what I’m missing. This is what I would build in SQL:
Query:
SELECT OrgnaizationName
FROM Organizations
JOIN OrganizationDetails
ON Organizations.OrganzationId = OrganizationDetails.OrganizationId
WHERE Organizations.OrganizationName LIKE ‘%Name%’
AND OrganizationDetails.OrganizationDetail = ‘Active’
On the Organizations table the where would return both rows, but because of the join and AND in the where it would only return OrgName1
In UI-Builder do I need to do this in the logic? In the video below you do something sort of like this with a function at the 8 min mark.
I could Load a Table Object with all the Organizations WHERE OrganizationName LIKE ‘%Name%’ and then loop through the returned object. For each row, I could use another Load Table Object with a WHERE OrganizationDetail = ‘Active’ and remove items from the Object if they’re not active, but that seems really inefficient.
Backendless works with objects. That means when you retrieve data from the Organizations table, you get a collection of Organization objects. If an Organization has a related table such as Detail and you request a related table to be loaded, you will get an object representing a record from that table.
What is the app id of your app? I will show you specifically with your tables what the responses will look like.
Thanks for the offer to take a look at the specifics. My app ID is EEE25B20-17FA-97DD-FF29-EC45A5072A00. It’s just a scaffolding right now, but might give you a better idea of what I’m trying to do.
I understand how objects and objects within work (at least somewhat). What I don’t understand is how to display objects of objects as tabular data and do multi-table where clauses.
I really appreciate your involvement. I love what I’ve seen so far, once I get over this hump I should be off and running.
By requesting specific relations to be returned. In the screenshot below, I am requesting that related Fundraisers to be included with each Organization. As a result, we get a collection of related fundraisers for each organization:
You could also include related FundraiserGoals, however, there are no related objects between Fundraiser objects and FundraiserGoals, therefore the response is the same as above:
As an alternative to (1), you could request specific properties from the related objects. In the example above, I am retrieving data from Organizations and am requesting the Fundraisers.Name property to be included. Since you have only one Organization with 2 related fundraisers, you get back 2 objects, which are the same organization but with different fundraiser names:
Notice that when you ask for a specific property, you get only that property (in addition to objectId and ___class which are system level fields). To get all other properties, request *:
In some cases, you may want to assign an alias to a specific property you’re retrieving. For instance in the example below, I am assigning an alias of FundraiserName:
