Web service with custom Sql request

REST API
1

Hello,
i have a very common problem and I wonder how to resolve it.

I have a database full of places with their coordinates. I have created a service (places) as a remote sql database in my dsp.

I Know the sql request to find the 5 nearest place given some coordinates…

How can I do à web service in dreamfactory that would return the 5 nearest place given some coordinates ? How can I launch some spécific sql request via the api ?

I am not sure that it is possible… But Is it ?

2

There are a few ways you can do this.

  1. You may be able to query the data directly with a REST API call from the client. It depends on how you’ve stored the coordinates data. The REST API lets you pass in a filter parameter with AND / OR clauses across multiple fields, same as SQL. See the docs here https://github.com/dreamfactorysoftware/dsp-core/wiki/Database-Retrieving-Records and this short screencast https://www.youtube.com/watch?v=BkRkI1BoMis. You can try filters in the API docs tab in the admin console…e.g. in the filter field of API docs for a GET on a table, just enter the table name and filter, like age < 30 AND last_name = “Smith”. You’ll see how the API call is formed.

  2. You can create a View in your remote SQL database. Views are exposed to the REST API and you can call views directly.

  3. You can create a stored procedure in your remote SQL database and call the stored procedure form the REST API. More info here https://github.com/dreamfactorysoftware/dsp-core/wiki/SQL-Stored-Procedures

3

Thank you for your answer.
I am really sorry for the time between your answer and now, but I haven’t been home for a while.

Anyway, what I see is that if I try to search by fields and I put that into fields, it works:

 ( 6371 * acos( cos( radians(45.012448) ) * cos( radians( lat ) )  * cos( radians( lng ) - radians(0.000812) ) + sin( radians(45.012448) ) * sin(radians(lat)) ) ) * 1000

BUT it returns me this as a field name…
What I would like to do is something like:

 ( 6371 * acos( cos( radians(45.012448) ) * cos( radians( lat ) )  * cos( radians( lng ) - radians(0.000812) ) + sin( radians(45.012448) ) * sin(radians(lat)) ) ) * 1000 AS distance

And when I put the ‘AS distance’, it returns me something like:

{
  "error": [
    {
      "context": null,
      "message": "Failed to retrieve records from 'places'.\nSQLSTATE[HY000]: General error: Did not find column name '( 6371 * acos( cos( radians(45.012448) ) * cos( radians( lat ) )  * cos( radians( lng ) - radians(0.000812) ) + sin( radians(45.012448) ) * sin(radians(lat)) ) ) * 1000 AS distance' in the defined columns; it will not be bound",
      "code": 500
    }
  ]
}

is there a way to change the fields name (to use the ‘AS something’) ?

The Idea is to be able to add in the filters something like:

distance < 2000
4

The REST API can fetch data for pre-defined fields…in other words if Distance is a field in your database, then you can easily pass in a filter to the API call, like distance < 2000.

It seems like you need to calculate distance programmatically. Calculating distance at run-time for every record on a large data set is expensive. I’d recommend having a distance field in your database and use a stored procedure / trigger to calculate distance based on coordinates when each place record is created and updated. Also probably index the distance field.

Then query the distance field directly with the REST API.

5

Well … thank you for the advice… but I have ton compute the distance field at runtime…
because I don’t know where I will be when I want to check where is the nearest place :smile:

SO, I can’t have a distance field.
in other word… what I want to do is not possible ?

Something like that:

Select comp
From table
Where (table.col + 2) as comp

is not possible ?

So what I have to do is a procedure that return a computed table right ?

6

Ok, got it. So returning the computed value with a stored procedure is a good way to do it.

The other option is to use a server-side Javascript script, but that will probably be slower than using a stored procedure.

7

By the way, we’re thinking about how to run raw SQL from an API call in a future release, but there are security issues with this.

For now, stored procedures and server-side scripts should both handle your use case.

8

That’s some great news!

Keep up the good work, I love your product :slight_smile:

1 Like
9

Thanks! Glad it’s helping you.

10

Wondering if this was ever solved? I have the same issue. I can used the stored procedure, but in my case (as well as this authors) the lat/long needs to be passed in to the stored procedure. Can we use parameters for the stored procedure?

11

Nevermind, seems you can put parameters on the stored proc call. Awesome!