How to construct a "group_by" SQL query in flask-restless?

Question

I am using flask_sqlalchemy and flask_restless to create an API endpoint to access data from a PostgreSQL database. How do I construct the following SQL query in flask-restless?

SELECT AVG("SpeedBand"), "Location"
FROM public.traffic_data
WHERE "RoadName"='ABC ROAD'
GROUP BY "Location"

This returns all the coordinate pairs that belong to "ABC ROAD" and the average speed on each pair of coordinates:

   avg   | Location
 ------------------------
1)'1.52' | 'X1 Y1 X2 Y2'
2)'1.85' | 'X3 Y3 X4 Y4'
 ------------------------

Currently, this query:

http://127.0.0.1:5000/api/traffic_data?q={"filters":[{"name":"RoadName","op":"==","val":"ABC ROAD"}]}

Returns a JSON string of all the data that belong to "ABC ROAD" and ALL the speed data at different times:

{
  "num_results": 26, 
  "objects": [
    {
      "DateTime": "2017-09-07T19:25:37.035325", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 3, 
      "id": 34094
    }, 
    {
      "DateTime": "2017-09-07T19:25:37.035325", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 38003
    }, 
    {
      "DateTime": "2017-09-13T09:57:07.163900", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 92874
    }, 
    {
      "DateTime": "2017-09-13T09:57:07.163900", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 96783
    }, 
    {
      "DateTime": "2017-09-13T09:57:07.163900", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 114755
    }, 
    {
      "DateTime": "2017-09-13T09:57:07.163900", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 114756
    }, 
    {
      "DateTime": "2017-09-13T10:08:17.727949", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 151654
    }, 
    {
      "DateTime": "2017-09-13T10:08:17.727949", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 155563
    }, 
    {
      "DateTime": "2017-09-13T10:19:39.040191", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 210434
    }, 
    {
      "DateTime": "2017-09-13T10:19:39.040191", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 214343
    }, 
    {
      "DateTime": "2017-09-13T10:27:51.307349", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 269214
    }, 
    {
      "DateTime": "2017-09-13T10:42:36.724985", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 327994
    }, 
    {
      "DateTime": "2017-09-13T10:42:36.724985", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 331903
    }, 
    {
      "DateTime": "2017-09-13T10:42:36.724985", 
      "LinkID": "109002906", 
      "Location": "X1 Y1 X2 Y2", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 1, 
      "id": 349875
    }, 
    {
      "DateTime": "2017-09-13T10:42:36.724985", 
      "LinkID": "109002907", 
      "Location": "X3 Y3 X4 Y4", 
      "RoadName": "ABC ROAD", 
      "SpeedBand": 2, 
      "id": 349876
    },

    . . .


  ], 
  "page": 1, 
  "total_pages": 1
}

Can you not define your own endpoint to run that SQL exactly? — OneCricketeer, Sep 21 '17 at 02:16
I can write the same query in SQLAlchemy `result =session.query(func.avg(TrafficData.SpeedBand),TrafficData.Location).group_by(TrafficData.Location).filter(TrafficData.RoadName == 'ABC ROAD').all()` but how should i write that into the endpoint? [link](https://flask-restless.readthedocs.io/en/stable/customizing.html) — green_socks, Sep 21 '17 at 11:22
You can write that into a method... https://flask-restless.readthedocs.io/en/stable/customizing.html#custom-queries — OneCricketeer, Sep 21 '17 at 13:20
It returns a ProgrammingError. In the log messages, the SQL query being run is: `[SQL: 'SELECT avg(traffic_data."SpeedBand") AS avg_1, traffic_data."Location" AS "traffic_data_Location" \nFROM traffic_data \nWHERE traffic_data."RoadName" = %(RoadName_1)s GROUP BY traffic_data."Location" ORDER BY traffic_data.id ASC \n LIMIT %(param_1)s'] [parameters: {'RoadName_1': 'ABC ROAD', 'param_1': 13}]`. The script is attempting to order the response via the "id" field in ascending order by default, which is not possible as we're sorting by "group_by". Any ideas how to remove that default query? — green_socks, Sep 21 '17 at 13:27
Not sure about that, but you can define a plain Flask endpoint to accept / return the necessary data — OneCricketeer, Sep 21 '17 at 13:33

score 0 · Answer 1 · edited Sep 21 '17 at 13:33

0

For group by you need to specify feild in the following format. group_by A list of objects of the form:

{"field": <fieldname>}

Here fieldname is the column to be used by group by.

edited Sep 21 '17 at 13:33

OneCricketeer

179,855
19
132
245

answered Sep 21 '17 at 02:17

4

do you have an example of how this works? i've tried the following `{"filters":[{"name":"RoadName","op":"==","val":"ABC ROAD"}],"group_by":[{"field":"Location"}]}` but get a ProgrammingError response. – green_socks Sep 21 '17 at 03:17

How to construct a "group_by" SQL query in flask-restless?

1 Answers1