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.
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:
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.
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:
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: