Is this possible?
If not, is the best thing to loop the results and run another query? Basically a very manual way of checking each row if a related value exists?
Tim
Hi Tim,
You can check if a related value exists with the following where clause:
relatedColumnName.objectId is not null
You can use that where clause with the data retrieval API (or codeless block). If you get a non-empty array, it will contain the objects matching the query (i.e. the objects that have the related data).
Regards,
Mark
@mark-piller I want to return null data. That’s how I’ve used left joins in the past so I’m not following what you’re suggesting.
Tim
Is your goal to determine if a record in the database has related data?
I want to get some primary data and related data but only related data falls within some parameters. Using a straight join it will limit the primary data returned (by design). A left join bypasses that by returning NULL data.
Simple example.
Users can have Sellers.
I want all users and if they have any sellers, those as well.
SELECT UserId, SellerId
FROM Users
LEFT JOIN Sellers
ON Users.UserId = Sellers.UserId
Results
User1, NULL
User2, Seller1
User2, Seller2
User3, NULL
Otherwise a straight join returns
User2, Seller1
User2, Seller2
And I don’t get User1 or User3 data.
Without a left join, I have to run one query to get the Users, and another query to get the Sellers.
Thinking in terms of joins and traditional RDMBS terminology will not help. It is better to abstract from that when you model your relationship in Backendless. Say you have the Users table with a relation column to the Sellers table. Suppose the relation column name is sellers:
Here’s how to load data from the Users table with related sellers. The example below retrieves objectId from the Users and objectId from the Sellers.
The condition for loading the Users objects is to load only those objects that have related sellers:
Does this answer your question?
Regards,
Mark
I am getting that, but it goes against all my programming (granted not much) experience. I’m used to writing queries to only return what is needed and to minimize round trips. Anyways, that is my problem and I’m enjoying the challenge (sometimes).
This is the exact opposite of what I was trying to do. I want all users and if the users have sellers I want those as well.
I’m realizing that my style is to put a lot of the programming logic in the query so the business logic and display logic is just simple iterations over the returned data.
The way you’re describing backendless is going back and forth between the backend and front end more than I’m used to. Does that seem right to you?
Is this how you’d go about solving for this? You’d get all the Users, iterate through them going back to the DB for each User to see if they have any Sellers. If they do, add the sellers to the Users object, then pass that to the display, which again iterates through them and displays something (button, etc.) if the user has any seller records.
Backendless works that way, there is nothing new here 
This is how you do just that - this will load the Users objects and related Sellers if those are present:
I don’t think so. Could you please be more specific where you believe I described it that way?
Not at all, in all the examples I have shared in this thread, there is only one block that fetches the data based on my understanding of what you’re asking.
Regards,
Mark
I’m thinking I don’t understand how to access the returned data correctly.
Q1: In UI builder if I put this result into a Dynamic Link block, what logic would I build to determine if there is a Seller for a User? Something simple, to toggle the visibility of a button.
Q2: If a User has Sellers, how would I display them? In your Country and City example (here) you went back to the DB for the cities in the country, is that what you’d recommend?
Q3: What happens if you add a WHERE on the sellers? Will it still return all Users and only the sellers that meet the WHERE?
Q4: What if I only want users that have sellers? I assumed that is what adding a relation would do.
If the sellers property is null, there is no related object.
It depends on the UI structure. It is hard for me to answer this question since it is based entirely on what the UI design looks like.
Backendless query always works against one primary table. In all examples above, that table is Users. This means all primary objects in the resulting collection are going to be objects from that table. If there are related seller objects, then the sellers property will contain the related objects from the Sellers table. If you apply a where clause, then the server will return the objects from the Users that match the query condition.
Then this because a condition for the where clause. This is what we started the discussion with. If you want to get only the users that have sellers, then you use the following where clause:
sellers.objectId is not null
Regards,
Mark
I understand everything but this. If the where is only applied to the primary table, how would I get users and only some of the sellers if they meet certain criteria?
Tim
I was thinking about this a bit more and this is why I said what I said. In this example, if I want a list of Users, just the Users, who have Sellers with SellerId greater than 10, Backendless will return the Users and Sellers for those users. I don’t want the Sellers, but they’re returned. I’m guessing there is some way to exclude them (exclude properties?).
My take is that SQL only returns what you ask for explicitly by column name, backendless returns all objects unless you exclude them.
There are several questions here, which one would you like me to handle?
Sorry, Mark. I got excited.
Can you tell me why the null check isn’t working?
What is the name of the relation column in the Users table?
You are also welcome to share you app id with me.
Regards,
Mark
Sellers is the relationship column name. I made them all the same as the table they relate to. Not sure if that’s good or bad.
I recommend adding a print block in Visibility Logic before the if check and print out Users Item Data. This will tell you what you have in Sellers. You will get the output in the Console tab of the browser’s developer tools panel.
I got the logic to work by checking the length. NULL was returning true, even when Sellers returned . I think because the array (object?) is instantiated but empty it doesn’t equal NULL?
If you feel like looking at the output -
https://magicaljelly.backendless.app/api/files/ui-builder/containers/default/index.html?page=testUsersSellers
As always. Thank you, @mark-piller.
Hi. You can consider another approach if you want null value.
In the request to the parent table you may set the dynamic property (Mark mentioned about it) like:
sellers.objectId as sellerId
In the returned object you’ll see either null or arbitrary id (in case of 1:N).