Using calculated fields
(Created page with "=== Tutorial === Calculated fields provide a simple way to perform standard SQL calculations (count, sum, min, max, etc.) in an API call. Try these examples in the 'API Docs...") |
Toddappleton (Talk | contribs) |
||
| (3 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
Calculated fields provide a simple way to perform standard SQL calculations (count, sum, min, max, etc.) in an API call. | Calculated fields provide a simple way to perform standard SQL calculations (count, sum, min, max, etc.) in an API call. | ||
| Line 11: | Line 9: | ||
=== API Docs Screenshot === | === API Docs Screenshot === | ||
| − | [[File:Swagger- | + | [[File:Swagger-calculated-field.png|1000px]] |
=== Example - Use a calculated field to calculate the sum of the 'amount' field in a transactions table. === | === Example - Use a calculated field to calculate the sum of the 'amount' field in a transactions table. === | ||
| Line 19: | Line 17: | ||
All we need to do is set up a "virtual" calculated field that defines the calculation. To set up a calculated field, navigate to the Schema tab and click to add a new field. In this example, we will add a field called 'amount_total' and fill in the field definition as shown in the screenshot below. | All we need to do is set up a "virtual" calculated field that defines the calculation. To set up a calculated field, navigate to the Schema tab and click to add a new field. In this example, we will add a field called 'amount_total' and fill in the field definition as shown in the screenshot below. | ||
| − | [[File: | + | [[File:Calculated-field-one.png|1000px]] |
| − | [[File: | + | [[File:Calculated-field-two.png|1000px]] |
Now we can make API calls to the 'transaction' table and GET the total amount for the returned set of records. | Now we can make API calls to the 'transaction' table and GET the total amount for the returned set of records. | ||
| Line 27: | Line 25: | ||
* Fields parameter in API call: <pre>amount_total</pre> | * Fields parameter in API call: <pre>amount_total</pre> | ||
* Request URL: <pre>https://foo.com/api/v2/mysql/_table/transaction?fields=amount_total</pre> | * Request URL: <pre>https://foo.com/api/v2/mysql/_table/transaction?fields=amount_total</pre> | ||
| + | |||
| + | === Example - Use a calculated field to calculate the sum of the 'amount' field and group by email address. === | ||
| + | |||
| + | This example is similar to the example above, except we need to group 'amount_total' by another field in the transaction table, say 'email'. We want the API call to return distinct email addresses and calculate total_amount grouped by each email address. | ||
| + | |||
| + | The "virtual" calculated field set up is identical to the example above. | ||
| + | |||
| + | To group amount_total by email address, you simply request both 'email' and 'amount_total' in the fields parameter and 'email' as the field to group by. | ||
| + | |||
| + | [[File:Swagger-calculated-field-group-one.png|1000px]] | ||
| + | [[File:Swagger-calculated-field-group-two.png|1000px]] | ||
| + | |||
| + | * Table name: transaction | ||
| + | * Fields parameter in API call: <pre>email, amount_total</pre> | ||
| + | * Group parameter in API call: <pre>email</pre> | ||
| + | * Request URL: <pre>https://foo.com/api/v2/mysql/_table/transaction?fields=email%2C%20amount_total&group=email</pre> | ||
Latest revision as of 20:14, 22 June 2016
Calculated fields provide a simple way to perform standard SQL calculations (count, sum, min, max, etc.) in an API call.
Try these examples in the 'API Docs' tab of the DreamFactory Admin Console or from the command line with cURL.
Contents
API Endpoint
GET https://{url}/api/v2/{api_name}/_table/{table_name}?fields={calculated_field}
API Docs Screenshot
Example - Use a calculated field to calculate the sum of the 'amount' field in a transactions table.
Say we have a 'transaction' table with an 'amount' field. We need to perform an API call on a set of records in the transaction table that returns the total amount.
All we need to do is set up a "virtual" calculated field that defines the calculation. To set up a calculated field, navigate to the Schema tab and click to add a new field. In this example, we will add a field called 'amount_total' and fill in the field definition as shown in the screenshot below.
Now we can make API calls to the 'transaction' table and GET the total amount for the returned set of records.
- Table name: transaction
- Fields parameter in API call:
amount_total
- Request URL:
https://foo.com/api/v2/mysql/_table/transaction?fields=amount_total
Example - Use a calculated field to calculate the sum of the 'amount' field and group by email address.
This example is similar to the example above, except we need to group 'amount_total' by another field in the transaction table, say 'email'. We want the API call to return distinct email addresses and calculate total_amount grouped by each email address.
The "virtual" calculated field set up is identical to the example above.
To group amount_total by email address, you simply request both 'email' and 'amount_total' in the fields parameter and 'email' as the field to group by.
- Table name: transaction
- Fields parameter in API call:
email, amount_total
- Group parameter in API call:
email
- Request URL:
https://foo.com/api/v2/mysql/_table/transaction?fields=email%2C%20amount_total&group=email
