Support Topics Documentation Slack YouTube Blog

Distance always = 0

Hello!
I set whereClause for getting objects in my iOS App:
whereClause = “distance((myLatitude), (myLongitude), location.latitude, location.longitude ) < km(50000) and ownerId != ‘(userId)’ and isActive = true and LikeEntity[fromMoment].toMoment.ownerId = ‘(userId)’ and LikeEntity[fromMoment].toMoment.isActive = true”
It works fine.
But if I add
whereClause += " and objectId not in (Moment[likes.ownerId = ‘(userId)’].objectId)",
distance of objects always = 0
Tell me, please, how get objects with this where clause and right distance?
My AppID: 3192997C-6C59-42C4-B974-66B4C2A029DA
Method: getMoments in codeless in my APIService with name “MomentService”

Hi @Petr_Kibukevich !
Did you try to test the sql query in Data browser? If so - please share the exact query and name of the table against which you are running it.
It will fasten investigation

Anton

if I do very simple request in Data browser in table “Moment”, I cannot see distance in “location”. But I see distance if I do request from my iOS App

@Petr_Kibukevich
I would need the following in order to help faster:

  • SDK you are using (Android, JS, iOS etc)
  • its version
  • exact where clause (text, not image) so I could simply copy-paste the value instead manually typing the where clause :wink:
    When done I’ll get back to you as soon as I have any results

I am using iOS SDK version 5.4.1
whereClause = “distance((47.498807201765445), (42.313292438807025), location.latitude, location.longitude ) < km(50000) and ownerId != ‘(“77EAE901-3BD8-2F27-FF0D-D664A5D7E300”)’ and isActive = true and LikeEntity[fromMoment].toMoment.ownerId = ‘(“77EAE901-3BD8-2F27-FF0D-D664A5D7E300”)’ and LikeEntity[fromMoment].toMoment.isActive = true and objectId not in (Moment[likes.ownerId = ‘(“77EAE901-3BD8-2F27-FF0D-D664A5D7E300”)’].objectId)”
This whеreclause get distance = 0.
whereClause = “distance((47.498807201765445), (42.313292438807025), location.latitude, location.longitude ) < km(50000) and ownerId != ‘(“77EAE901-3BD8-2F27-FF0D-D664A5D7E300”)’ and isActive = true and LikeEntity[fromMoment].toMoment.ownerId = ‘(“77EAE901-3BD8-2F27-FF0D-D664A5D7E300”)’ and LikeEntity[fromMoment].toMoment.isActive = true” get right distance
These whereClauses for method getMoments in MomentService

I’m a bit stuck because non of the above mentioned where clauses produces result. When copy-pasting the where clause I get “Invalid where clause” error. I edited the string a bit so it matches requirements, btw before using the where clause in client code - please test in REST console in advance, very often it helps to identify the root of the issue.
I tried to remove condition one by one and here is what I got:

  1. This one
    distance(47.498807201765445, 42.313292438807025, location.latitude, location.longitude ) < km(50000) and ownerId != '("77EAE901-3BD8-2F27-FF0D-D664A5D7E300")' and isActive = true
    is working and I get a result. Distance is counted correctly: https://monosnap.com/file/srO32hEcACV08bk3lGQLQLxvbZwvZw
  2. The the rest of conditions result in an empty array of objects. F.e:
  • distance(47.498807201765445, 42.313292438807025, location.latitude, location.longitude ) < km(50000) and ownerId != '77EAE901-3BD8-2F27-FF0D-D664A5D7E300' and isActive = true and LikeEntity[fromMoment].toMoment.ownerId = '("77EAE901-3BD8-2F27-FF0D-D664A5D7E300")' and LikeEntity[fromMoment].toMoment.isActive = true and objectId not in (Moment[likes.ownerId = '("77EAE901-3BD8-2F27-FF0D-D664A5D7E300")'].objectId) - result is empty array
  • same here distance(47.498807201765445, 42.313292438807025, location.latitude, location.longitude ) < km(50000) and ownerId != '77EAE901-3BD8-2F27-FF0D-D664A5D7E300' and isActive = true and LikeEntity[fromMoment].toMoment.ownerId = '("77EAE901-3BD8-2F27-FF0D-D664A5D7E300")'

Proof :wink: - https://monosnap.com/file/M490o0YqvyV6v5ZtBQkVJhp2QTIpYz
I ran all the request against your app’s data. So I would kindly ask you to provide a tested in REST console where clause which produces result as well as “0” distance. The fact that it was tested in REST console instead of iOS SDK doesn’t actually matter cause it uses REST requests under the hood. Any SDK for any lang requires same syntax of where clause anyway.

Anton

There is very simple whereClause for demonstrate this problem:
whereClause = “distance(47.49880520893741, 42.31329102213582, location.latitude, location.longitude ) < km(50000) and objectId not in (Moment[likes.ownerId = ‘77EAE901-3BD8-2F27-FF0D-D664A5D7E300’].objectId)” get always distance = 0
whereClause = “distance(47.49880520893741, 42.31329102213582, location.latitude, location.longitude ) < km(50000)” get right distance
It is possible use these two conditions? If it is impossible, how I can exclude objects in whereClause?

Ok, now I see. Even this one produces result but the distance property is “lost” somewhere:

objectId not in (Moment[likes.ownerId = '77EAE901-3BD8-2F27-FF0D-D664A5D7E300'].objectId)

Looks like a bug on our side. We’ll look into it and notify you as soon as it’s fixed. For referencing purposes ticket ID - BKNDLSS-19505.
Sorry for inconveniences and thanks for pointing this out!

Anton

A bit more of additional info:
this happens due to this part of the where clause -
objectId not in (Moment[likes.ownerId = '77EAE901-3BD8-2F27-FF0D-D664A5D7E300'].objectId)

As a temporary workaround you may paste the value of objectId directly like this:
objectId not in ('<value1>', '<value2>')
In this case the distance value will be returned properly. This is a temporary workaround for this case

Anton

Thanks, but I cannot know value for list of excluded objectId, because I want exclude objects “Moment” which has child “LikeEntity” with owner current user. Have You any ideas how I can do it?

You may run a separate call against moments table with a whereClause = "objectId not in (Moment[likes.ownerId = '77EAE901-3BD8-2F27-FF0D-D664A5D7E300'].objectId)"

In foreach cycle extract objectIds from the resulting objects set. At least this is first obvious solution I came up with. You may think of other approaches if you don’t like this one or wait for a fix. I believe it will be available after the next release (in about 2 weeks)

Anton