How to get filtered and sorted by a distance data from a data table

matt · 2016-04-01 18:20:10 UTC

Hey,

I’m trying to get filtered and sorted by a distance data from my data table. I’m using REST API.

Following this thread http://support.backendless.com/t/search-data-by-distance-from-a-location-not-working I’ve managed to get geo points with related objects which are sorted by distance - every geo point in my category has related list of objects and I’m requesting geo points with includemetedata=true parameter. Now I want to be able to add a where clause so I can filter search results by some properties which that related objects have.

Example:
A GeoPoint has a related list of objects of type Shop. Every Shop object has it’s opening hours - timeOpen (int) and timeClose (int). I want to find all Shop objects which have timeOpen < 9.

How this can be done using REST API?

Thank you in advance.
Best,
Matt

matt · 2016-04-04 08:58:02 UTC

Hey any updates on this? Maybe there is some other approach I can try?

denys-konakhevych · 2016-04-04 10:55:54 UTC

Hi Matt,

If you have GeoPoint with metadata relation to Shop table and you want find all points that have metadata property ‘shops’ with ‘timeOpen’ value less than 9 you should execute find query with following where clause “shops.timeOpen < 9”.

Regards,

Denys

matt · 2016-04-04 11:59:37 UTC

Excellent. Exactly what I was looking for. Thanks a lot Denys!

matt · 2016-04-05 08:26:32 UTC

Hey there,

I have two more questions similar to the previous one.

Suppose the Shop table mentioned earlier has a one to many relation with Owner table (Owner can have many shops but a Shop can belong to only one Owner).

When I run the previous query with loadRelations=Owner the result doesn’t include the Owner data. When I check the autoload option in Backendless Data Management console it works. So the question is is it OK that loadRelations query param in geo points query doesn’t work or am I doing something wrong?
Let’s assume Owner data is autoloaded when retreving Shop data. I want to get all shops for some particular Owner (sorted by a distance so I’m still using geo points queries). I tried with shops.owner.objectId=‘someObjectId’ where clause but the result is 500 Internal Server Error. How this can be achieved?

Thank you for your support.

Best,
Matt

alexandr-navara · 2016-04-05 09:07:44 UTC

Hi, Matt!

In loadRelations list you should declare names of the relations to preload, but not the related tables. Assuming the relation from Shop to Owner is called “shopOwner”, your clause should look like “loadRelations=shopOwner”. Also, if Owner can have many Shops, then the relation one to many should be declared from Owner to Shops, not vice versa. Of course, another way to implement it is to declare one to one relation from Shop to Owner.
It looks like a bug on our side. I shall start investigation.
best regards,
Alex

alexandr-navara · 2016-04-05 09:52:17 UTC

Matt, I’ve reproduced your second issue and opened internal task for it with tag 12248.
Thank you for reporting!
Alex Navara

matt · 2016-04-05 10:04:01 UTC

Thanks for fast response Alexandr,

Let me please clarify and switch to real names of my data tables. Hope this will help in your investigation.

I have Cities, Companies and Rooms data tables with relations:

one to one from Companies to Cities,
one to one from Rooms to Companies.
I have Geolocation with “rooms” category and for each geo point established relation to one row from Rooms table.
I’m trying to get all rooms sorted by a distance for a particular company.

Still can’t figure it out. My relation is called ‘company’ and when I’m using REST console in Data Management and check to load company relation it automatically adds ‘loadRelations=company’ to the request URL. Query executes correctly. When I try to do the same for geo query (adding ‘loadRelations=company’) I can’t see this data in the request result.

Adding curl request so you can see exactly what I’m talking about.

So this query works as expected - related companies are included.

 
 
 
 
 
 
 
 
curl -H application-id:"CCD7530E-CD7B-3B6E-FF2E-B153FE232B00" -H secret-key:"&lt;secret_REST_API_key&gt;" -H Content-Type:"application/json" -H application-type:REST -X GET -v "[url=https://api.backendless.com/v1/data/Rooms?loadRelations=company]https://api.backendless.com/v1/data/Rooms?loadRelations=company"[/url];

Lets switch to Geolocation. Response from this request doesn’t have companies included (I have also tried with loadRelations=rooms.company but the result is the same):

 
 
 
 
 
 
 
 
curl -H application-id:"CCD7530E-CD7B-3B6E-FF2E-B153FE232B00" -H secret-key:"&lt;secret_REST_API_key&gt;" -H Content-Type:"application/json" -H application-type:REST -X GET -v "[url=http://api.backendless.com/v1/geo/points?lat=52.12&lon=16.05&r=1000&units=KILOMETERS&categories=rooms&includemetadata=true&loadRelations=company]http://api.backendless.com/v1/geo/points?lat=52.12&lon=16.05&r=1000&units=KILOMETERS&categories=rooms&includemetadata=true&loadRelations=company"[/url];

As for the second question let me also include some curl request.
This works fine:

 
 
 
 
 
 
 
 
curl -H application-id:"CCD7530E-CD7B-3B6E-FF2E-B153FE232B00" -H secret-key:"&lt;secret_REST_API_key&gt;" -H Content-Type:"application/json" -H application-type:REST -X GET -v "[url=http://api.backendless.com/v1/geo/points?lat=52.12&lon=16.05&r=1000&units=KILOMETERS&categories=rooms&includemetadata=true&where=rooms.objectId%3D%271083BD06-2A41-12E1-FFF0-463568C2AC00%27]http://api.backendless.com/v1/geo/points?lat=52.12&lon=16.05&r=1000&units=KILOMETERS&categories=rooms&includemetadata=true&where=rooms.objectId%3D%271083BD06-2A41-12E1-FFF0-463568C2AC00%27"[/url];

This returns 500 Internal Server Error (no difference if autoload company relation is checked in Data Management console or not)

 
 
 
 
 
 
 
 
curl -H application-id:"CCD7530E-CD7B-3B6E-FF2E-B153FE232B00" -H secret-key:"&lt;secret_REST_API_key&gt;" -H Content-Type:"application/json" -H application-type:REST -X GET -v "[url=http://api.backendless.com/v1/geo/points?lat=52.12&lon=16.05&r=1000&units=KILOMETERS&categories=rooms&includemetadata=true&where=rooms.company.objectId%3D%27A5611FEE-7D11-5710-FF67-2B7E4549DB00%27]http://api.backendless.com/v1/geo/points?lat=52.12&lon=16.05&r=1000&units=KILOMETERS&categories=rooms&includemetadata=true&where=rooms.company.objectId%3D%27A5611FEE-7D11-5710-FF67-2B7E4549DB00%27"[/url];

Similar request to above but directly for Rooms table works as expected:

 
 
 
 
 
 
 
 
curl -H application-id:"CCD7530E-CD7B-3B6E-FF2E-B153FE232B00" -H secret-key:"&lt;secret_REST_API_key&gt;" -H Content-Type:"application/json" -H application-type:REST -X GET -v "[url=https://api.backendless.com/v1/data/Rooms?where=company.objectId%3D%27A5611FEE-7D11-5710-FF67-2B7E4549DB00%27]https://api.backendless.com/v1/data/Rooms?where=company.objectId%3D%27A5611FEE-7D11-5710-FF67-2B7E4549DB00%27"[/url];

Hope this helps.

Best,
Matt

matt · 2016-04-05 10:25:05 UTC

Thanks Alexandr. Is there any way I can see those internal tasks and monitor status of this one? It’s extremely important to me.

alexandr-navara · 2016-04-05 10:34:26 UTC

Matt, thank you for provided information, it is really useful for us.

Instead of declaring “loadRelations” try to set autoload for “company” relation and repeat request. It should help, and you’ll be able to receive rooms together with related companies.
This issue occurs when searching through geo with clauses which includes deep relations. For example, “rooms.objectId=<room_id>” would work well, but “rooms.company.objectId=<company_id>” would not. I’ve already opened an internal task for this bug and we’re working on it.
best regards,
Alex

matt · 2016-04-05 10:44:29 UTC

Hey Alexandr,

Yeah it works when autoload is checked. The reason why I try to avoid autoload is I don’t want do download data that isn’t useful for my app in some particular moments. Is there a way to get for instance only objectId from a related table and ignore the rest of the columns?
Thanks!

alexandr-navara · 2016-04-05 10:54:43 UTC

Unfortunately, not.

The only way to monitor is to ask us here, in this topic. Of course, we shall notify you when it’s fixed.

alexandr-navara · 2016-04-05 11:19:58 UTC

Ok, I understand. The feature you’re speaking about is available for data-to-data relationships, but not for relations from geo.

If it’s critical for you - open please another topic and describe the feature you’d like to see so I can open a separate task for it.

matt · 2016-04-05 14:33:39 UTC

Thanks I will do that.

In the meantime, how can I load only specific columns in data-to-data relationships?

alexandr-navara · 2016-04-05 17:02:16 UTC

You can do it only for parent object declaring “props=property1,property2” as described here: https://backendless.com/documentation/data/rest/data_search_and_query.htm