A time when REST APIs were written without current day MVC/REST API frameworks — must have been era of dinosaur-development. However, things have gone a little stagnant with our expectations in terms of APIs provided by current day REST API frameworks.
Most frameworks provide the expected REST APIs of CRUD, List, Count, Relations etc. However, one of the oft overlooked and most used REST API(s) are the ones which abstract ‘GROUP BY’ operation on a database.
Xmysql* makes an attempt to solve this by having ‘GROUP BY’ APIs out of the box.
( *Xmysql : One command to generate REST API for any MySql Database )
1. Simple Group By API
/api/offices/groupby?_fields=country[
{"country": "USA","_count": 3},
{"country": "UK","_count": 1},
{"country": "France","_count": 1},
{"country": "Japan","_count": 1},
{"country": "Australia","_count": 1}
]
_fields : takes in column name(s) to be grouped by returning count for each distinct column.
Multiple columns can be provided as input to _fields (see below).
/api/customers/groupby?_fields=country,city[
{"country": "Spain","city": "Madrid","_count": 5},
{"country": "USA","city": "NYC","_count": 5},
{"country": "New Zealand","city": "Auckland ","_count": 3}
..
]
2. Union of multiple group by
Instead of calling simple group by APIs multiple times for a different column s— a union of group by can be called like below.
/api/customers/ugroupby?_fields=country,city{
"country":
[
{"Australia": 5},
{"Austria": 2},
{"Belgium": 2},
...
],
"city": [
{"Auckland ": 3},
{"Barcelona": 1},
{"Bergamo": 1},
{"Boston": 2}, ...
]
}
3. Chart API for Numeric Column in table
Having group by for numeric columns doesn’t make sense most of times (unless its a foreign key..).
However, numericColumn between val1 and val2 functionality based on distribution of various values of numericColumn are very helpful.
Eg: API to get count of payments between price of : $10–$100, $101–200 etc.
Chart API (attempts) to provide this functionality out of box with few flavours.
3a. Chart API with steparray
Below API counts ‘payments’ between ‘0–10000’, ‘10001–20000’, ‘20001–70000’ and ‘70001–140000’
/api/payments/chart?_fields=amount&steparray=0,10000,20000,70000,140000
Response
[
{
"amount": "0 to 10000",
"_count": 42
},
{
"amount": "10001 to 20000",
"_count": 36
},
{
"amount": "20001 to 70000",
"_count": 183
},
{
"amount": "70001 to 140000",
"_count": 12
}
]3b. Chart API with min, max, step
Chart api can take a minimum, maximum and step values. Responses will have count from min to max for each step as shown below.
/api/payments/chart?_fields=amount&min=0&max=131000&step=25000
Response
[
{
"amount": "0 to 25000",
"_count": 107
},
{
"amount": "25001 to 50000",
"_count": 124
},
{
"amount": "50001 to 75000",
"_count": 30
},
{
"amount": "75001 to 100000",
"_count": 7
},
{
"amount": "100001 to 125000",
"_count": 5
},
{
"amount": "125001 to 150000",
"_count": 0
}
]3c. Chart API with step pairs
Chart also can take pairs of ‘from’ and ‘to’ values (steps)-between which number of records can be counted.
/api/payments/chart?_fields=amount&steppair=0,50000,40000,100000Response[
{"amount":"0 to 50000","_count":231},
{"amount":"40000 to 100000","_count":80}
]
3d. Chart API with no params
This is for lazy while prototyping. Min, max, step are calculated automatically by Xmysql. Step size is calculated by stddev function.
/api/payments/chart?_fields=amount
Response
[
{
"amount": "-9860 to 11100",
"_count": 45
},
{
"amount": "11101 to 32060",
"_count": 91
},
{
"amount": "32061 to 53020",
"_count": 109
},
{
"amount": "53021 to 73980",
"_count": 16
},
{
"amount": "73981 to 94940",
"_count": 7
},
{
"amount": "94941 to 115900",
"_count": 3
},
{
"amount": "115901 to 130650",
"_count": 2
}
]3e. Chart API with steparray + Range
Specifying Range with above three variation of Chart API — gets the response from min to each step size.
Note : each count is done from minimum step array value.
/api/payments/chart?_fields=amount&steparray=0,10000,20000,70000,140000&range=1
[
{
"amount": "0 to 10000",
"_count": 42
},
{
"amount": "0 to 20000",
"_count": 78
},
{
"amount": "0 to 70000",
"_count": 261
},
{
"amount": "0 to 140000",
"_count": 273
}
]Similarly, Chart API works with range option when used with (min,max,step) and (no params) versions of this api too.
4. Autochart API — for super lazy while prototyping
Autochart invokes ‘Chart API with no params’ for every numeric column and returns its response.
Xmysql generates REST API for any MySql database — which means it does not rely on conventions of database design. However having similar group by APIs when applied to popular MVC frameworks with conventions around database design— I am sure better APIs can be derived.
