How to check if one-to-many relations have certain relation

Hello! I’am looking for solution:

Suppose I have a table “Article” that has 1:N property “alreadyRead” to Users.
When user read the article, i add user to this relation.
When I download Articles i don’t need to get all Users (Array of Users) who have read this article (it could be a million or more), i have to know only if my 1:N property already has relation to my User…

Thanks!

hello @Dmitry_Kalenkov

Thank you for a very nice question, we have created an internal ticket BKNDLSS-31539 to investigate and discuss it.

Greetings @sergey.kuk! Thanks for your answer. How can I find out the result of your discussion? Could you please report the results inside this thread?

Hello @Dmitry_Kalenkov,

we will let you know in this thread about the results.

Regards,
Stanislaw

1 Like

hello @Dmitry_Kalenkov

we have investigated the issue and found inconsistency with one relation. It is hard to say what cause this inconsistency, however, we have fixed the DB and now the result is correct, please check if everything is fine from your side

Hello @sergey.kuk, it seems you misunderstood me. My question is HOW can I check if a 1:N relations has already a certain relation to User (i know userId).

It can be just a boolean true/false value that indicates whether the user is added to the relations property or not.
I am looking for a way using ios sdk.

hello @Dmitry_Kalenkov

sorry, it is my mistake, it is an answer for another topic, I’ve just mixed up the topic. Investigation according to your question is still in progress.

1 Like

Hello @sergey.kuk. It’s been 3 weeks since my question, do you have any ideas?
My thought is would be cool something like this:
Aggregate Functions “Count” could check certain id (Count(alreadyRead.objectId = ‘personId’) as isRead) and return whether 0 or 1.
Maybe you can offer another approch. Thanks.

Hello @Dmitry_Kalenkov

Unfortunately, there is no simple way to do( or to fix/add) the case you have described. If we use direct MySQL I would use join with criteria, like the following:

select * from Article as a
left join ReadedArticles as ra on a.id=ra.artcileId and ra.userId=<my-userId>

So if ra is null the user does not read the article, not null then it was read.

And 1:n relation in Backendless is actually is n:n relation, like I have described in MySQL example, so we have the intermediate table for relations like ReadedArticles, but there is no way to provide additional join criteria. Without it, you will get cartesian intersection of the records for Articles and Users and you will not be able filter it in correct way.

Lets back to Backendless way. I have created the following schema

Now when I find the Article I can see the following

You can easily find the articles which were read by the user with the following query:

alreadyRead.objectId = <your-user-id>

But if you want to find Articles that was not read by the user, the following query will give you wrong result:

alreadyRead.objectId != <user-id>

as you can see you get both, but should get only one, because blog one was read by the user. And it is not a bug of Backendless it is just how n:n relation works. And the behavior might be improved if we add join criteria, but it is really long story.

As workaround to get articles that are not read by the user you can get Articles ids with wrist query:

➜  ~ curl -X GET -H 'user-token: 6B30E7AB-F531-4059-9C49-1746FF8B333A' 'https://api.backendless.com/23298D6F-EA2F-7BE6-FFBF-7507875E1E00/1C2B5588-DDCA-418F-A202-185385180687/data/Article?where=alreadyRead.objectId%20%3D%20%27FB5463F2-C33C-4794-B70F-8A46AC0D76D4%27&property=alreadyRead.objectId%20as%20readedBy&property=text&property=objectId' -i
HTTP/1.1 200 OK
server: nginx
date: Wed, 15 Mar 2023 10:05:41 GMT
content-type: application/json
content-length: 145
access-control-allow-origin: *
access-control-allow-methods: POST, GET, OPTIONS, PUT, DELETE, PATCH
strict-transport-security: max-age=86400

[{"___class":"Article","text":"Blog post 1","readedBy":"FB5463F2-C33C-4794-B70F-8A46AC0D76D4","objectId":"3C47539D-8761-44BE-8717-19B9566B476F"}]%

And the select articles that has no ids from first query:

objectId NOT in ('<objectId-1>', '<objectId-2>', '<objectId-3>',...)

Sorry for the long read

Thank you very much @sergey.kuk for your answer. It’s really helpful for understanding, but don’t really solve my problem…
The only thing I understood is: I have to perform one more request to server, if i want to check already added relation for the certain article…

If anyone ever run into this topic in the future, I also find a good post by Vladimir Upirov How to Build a Social App Backend With Backendless (Part 2) | MBaaS.
In this post Vladimir explains how to get true/false value for 1:n property (likes) via JS and Cloud Code.
I’m going to try this way)

@Dmitry_Kalenkov

yes, you should make an additional request. And approach in the Vladimir’s article supposes you to make at least 2 requests too