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.
