Support Topics Documentation Slack YouTube Blog

Distinc count of Parent table records based on the 1:N relation

Hello,

I am working on the algorithm that would randomly retreive a few records from the db. In the other thread on the forum I found that the suggested approach for it is to set pageSize as 1 and do the random offset (based on the count).

The issue that I observed is related to the count itself is not counting the parent record as distinct ones.

As an example:

  1. I have Restaurant table that has 2 records.
  2. Restaurant table has column “types” which is 1:N relation to the RestaurantTypes table.
  3. Now, as I am doing the count on Restaurant table using where clause passing objectIds of each type: “types IN (‘059F119A-BA40-41A3-B3F0-58FF83BD337C’, ‘B018A4A6-3997-4397-B4CA-9D4AC8D92D5A’, ‘CC5D00A2-3C52-4ADC-B6BE-CF264C8BA77A’)”
  4. The result which I get is not 2 (which would be expected) but it is equal to the number of types that restaurants have.

Is there a way to retreive the count of Restaurants that would not be counting based on the relation but just on the Restaurant table for a given example above?

App ID: F7217991-455B-46B8-886F-505F8B71BEBA
Following example, I have created GetHomePageRestaurants for you which shows that (the debug logger is set there so you can see what returns the count with where).
While invoking the API you can log in on user: kuba@test.com/123456
Hope that will help to investigate the issue.

Please let me know if that is something that can be fixed or there is another approach to retreive the distinct count.

Thank you in advance!
Jakub

Hello @Jakub_Patoleta

I just run the query

types IN ('059F119A-BA40-41A3-B3F0-58FF83BD337C', 'B018A4A6-3997-4397-B4CA-9D4AC8D92D5A', 'CC5D00A2-3C52-4ADC-B6BE-CF264C8BA77A')

and seems like it works properly, it returns only two Restaurant objects

Regards, Vlad

Oh, I see

The count API returns wrong result,

https://api.backendless.com/APP_ID/API_KEY/data/Restaurant/count?where=types%20IN%20(%27059F119A-BA40-41A3-B3F0-58FF83BD337C%27%2C%20%27B018A4A6-3997-4397-B4CA-9D4AC8D92D5A%27%2C%20%27CC5D00A2-3C52-4ADC-B6BE-CF264C8BA77A%27)

and it returns 5

I’m going to create an internal ticket to investigate this

the ticket number for reference is BKNDLSS-23651

once it’s done we notify you here

Happy holidays and happy coding!
Regards, Vlad

1 Like

Hello @vladimir-upirov thank you for quick update!
Do you have any estimations when this can be fixed? Even high-level ETA would be appreciated as this blocks major functionality on my side.

Thank you,
Jakub

unfortunately I can’t, however I’ve added your request to the ticket and once our engineer determine ETA and notify you.

1 Like

Okay, thanks!

There is one more issue that I noticed related to where and relations 1:N:
This where clause:
"types != '059F119A-BA40-41A3-B3F0-58FF83BD337C' AND types != 'B018A4A6-3997-4397-B4CA-9D4AC8D92D5A' AND types != 'CC5D00A2-3C52-4ADC-B6BE-CF264C8BA77A' AND types != 'D3A49333-C1BF-44F2-90FB-EB170A73AF16' AND types != 'EAF2F301-6C3A-4126-A699-DA560C471F97'"

Should not return restaurants that have any of above types, however it does:

`“types”:[{___class":“RestaurantTypes”,“ownerId”:null,“updated”:null,“created”:1609188021000,“objectId”:“059F119A-BA40-41A3-B3F0-58FF83BD337C”},{___class":“RestaurantTypes”,“ownerId”:null,“updated”:1605387074000,“created”:1601842651000,“objectId”:“8EB14500-8E1E-42E2-B0B5-80F9FBDABDFA”},{___class":“RestaurantTypes”,“ownerId”:null,“updated”:null,“created”:1609188016000,“objectId”:“B018A4A6-3997-4397-B4CA-9D4AC8D92D5A”},{___class":“RestaurantTypes”,“ownerId”:null,“updated”:1605387072000,“created”:1601842642000,“objectId”:“CC5D00A2-3C52-4ADC-B6BE-CF264C8BA77A”},{___class":“RestaurantTypes”,“ownerId”:null,“updated”:null,“created”:1609188011000,“objectId”:“D3A49333-C1BF-44F2-90FB-EB170A73AF16”},{___class":“RestaurantTypes”,“ownerId”:null,“updated”:null,“created”:1609188027000,“objectId”:“EAF2F301-6C3A-4126-A699-DA560C471F97”}],

@vladimir-upirov can you please take a look and add it to the ticket/create separate internal ticket for this issue?

Thanks,
Jakub

I’ve created a separated ticket BKNDLSS-23655,

Hello @vladimir-upirov

Is there any update on BKNDLSS-23655 and BKNDLSS-23651 bugs?
Do you know when the fixes for those can be expected?

Thanks,
Jakub

Hello @Jakub_Patoleta,

Our developer will look into this issue as soon as possible and we’ll notify you about any updates here.

Regards,
Olha

Hello @Jakub_Patoleta

I create two tables:
Restaurant (name-STRING, types-RELATION 1:N)
RestaurantTypes (name-STRING)
I entered the following data into them:

Restaurant
res1 - type1, type2
res2 - type3, type4
res3 - type5, type6

RestaurantTypes
type1
type2
type3
type4
type5
type6

When executing a request:
https://api.backendless.com/<appId>/<apiKey>/data/Restaurant?where=types in ('<typeObjectId1>','<typeObjectId2>','<typeObjectId3>')
We will get the response:

[
    {
        "created": 1611681972240,
        "name": "res1",
        "___class": "Restaurant",
        "ownerId": null,
        "updated": 1611681978716,
        "objectId": <restObjectId1>
    },
    {
        "created": 1611681970821,
        "name": "res2",
        "___class": "Restaurant",
        "ownerId": null,
        "updated": 1611681983384,
        "objectId": <restObjectId2>
    }
]

But when we add properties to the request, we get the cross-intersection:
https://api.backendless.com/<appId>/<apiKey>/data/Restaurant?where=types in ('<typeObjectId1>','<typeObjectId2>','<typeObjectId3>')&property=name as restName&property=types.name as typeName

[
    {
        "typeName": "type2",
        "___class": "Restaurant",
        "restName": "res1"
    },
    {
        "typeName": "type1",
        "___class": "Restaurant",
        "restName": "res1"
    },
    {
        "typeName": "type3",
        "___class": "Restaurant",
        "restName": "res2"
    }
]

That’s why, when request:
https://api.backendless.com/<appId>/<apiKey>/data/Restaurant/count?where=types in ('<typeObjectId1>','<typeObjectId2>','<typeObjectId3>')
The response will be 3.
To get the result what you expect, you just need to add &distinct = true:
https://api.backendless.com/<appId>/<apiKey>/data/Restaurant/count?where=types in ('<typeObjectId1>','<typeObjectId2>','<typeObjectId3>')&distinct=true
And in the response you will get 2.
This is the solution to your first question (BKNDLSS-23651).

Regarding the second question (BKNDLSS-23655):
Response:
https://api.backendless.com/<appId>/<apiKey>/data/Restaurant?property=name as restName&property=types.name as typeName
Request:

[
    {
        "typeName": "type2",
        "___class": "Restaurant",
        "restName": "res1"
    },
    {
        "typeName": "type1",
        "___class": "Restaurant",
        "restName": "res1"
    },
    {
        "typeName": "type3",
        "___class": "Restaurant",
        "restName": "res2"
    },
    {
        "typeName": "type4",
        "___class": "Restaurant",
        "restName": "res2"
    },
    {
        "typeName": "type6",
        "___class": "Restaurant",
        "restName": "res3"
    },
    {
        "typeName": "type5",
        "___class": "Restaurant",
        "restName": "res3"
    }
]

That’s why when you exclude a restaurant with a certain type from the search results (types != '<typeObjectId1>' and types != '<typeObjectId2>' and types != '<typeObjectId3>') by doing the query:
https://api.backendless.com/<appId>/<apiKey>/data/Restaurant?where=types != '<typeObjectId1>' and types != '<typeObjectId2>' and types != '<typeObjectId3>'&property=name as restName&property=types.name as typeName
You are getting:

[
    {
        "typeName": "type4",
        "___class": "Restaurant",
        "restName": "res2"
    },
    {
        "typeName": "type5",
        "___class": "Restaurant",
        "restName": "res3"
    },
    {
        "typeName": "type6",
        "___class": "Restaurant",
        "restName": "res3"
    }
]

To achieve the result you want you need to use subquery (Search with SubQuery). In the subquery, you search for all the restaurant IDs that contain the type you want to exclude, and in the query itself, you exclude those restaurants:
https://api.backendless.com/<appId>/<apiKey>/data/Restaurant?where=objectId not in (Restaurant[types in ('<typeObjectId1>','<typeObjectId2>','<typeObjectId3>')].objectId)
We receive only those restaurants, none of the types of which are included in the list of excluded types:

[
    {
        "created": 1611670989927,
        "name": "res3",
        "___class": "Restaurant",
        "ownerId": null,
        "updated": 1611681986895,
        "objectId": <restObjectId3>
    }
]

Try these two approaches (for the first question adding a distinct, for the second using a subquery) and please let us know about the result of your check.

Hello @Vladimir_Yalovy,

For the first issue: I am working with codeless and I believe there is no way to invoke “&distinct=true” as the block “Count” allows to pass just “where” clause. I was trying to overcome it somehow but it shows an error each time. Can you please advise whether that can be done or if you could add distinct option to the count block in codeless?

I will validate the second issue soon and let you know, thanks!

Hi, @Jakub_Patoleta

Recently, we added distinct option for Codeless as well. You can find it in the Data API section >> Load Table Objects block.


We would be happy to help you with this, could you please provide us with the codeless logic you are using and the error you received.

Regards, Marina

Hello,

@Vladimir_Yalovy I can confirm that the subquery works as it should (issue BKNDLSS-23655). Thank you so much!
@Marina.Rudenko Yes, the distinct option is available in the Load Table objects block, but all I need is just to do “Get Object Count” here. Would that be possible to add distinct for the count block? (Or if there is a way to pass &distinct=true in the exsiting field “where clause” it would be fine as well but I cound’t manage to make it work.)

Thank you in advance,
Jakub

Hello @Jakub_Patoleta

We are working on internal ticket BKNDLSS-24004 and trying to add this feature in the fewest releases.

Regards, Dima.

Hello @Dima_Vak,

Thank you! Do you have any high level estimation when this feature can be expected?

Hello @Jakub_Patoleta
This feature will be released after it will pass all test stages.

Regards, Denys

Sure - if you will have any news when I can expected that, it would be great.

Thanks,
Jakub

Hello @Jakub_Patoleta

We have released this feature already.
Happy coding with Backendless!

Regard, Viktor

Sorry @viktor.liablin but what was added? I am looking for “distinct” option in the “Count” block in codeless. I believe it is still not there (just checked).