Flowgear configurable variable bar for sql query via API

Question

I have a datase table with a list of books. Below is my sql statement:

SELECT `Book`.`id` , `Book`.`name` , `Book`.`isbn` , `Book`.`quantity_in_stock` , `Book`.`price` , (`Book`.`quantity_in_stock` * `Book`.`price`) AS `sales`, concat(`Author`.`name`, ' ', `Author`.`surname`) AS `author`  
FROM `books` AS `Book` 
LEFT JOIN authors AS `Author` 
ON ( `Book`.`author_id` = `Author`.`id` ) 
WHERE  (`Book`.`quantity_in_stock` * `Book`.`price`) > 5000.00

The query works fine and the workflow works fine too. However, I am wanting to access this through an API and make the 5000.00 value configurable through a variable bar.

Question is how do I make this possible such that when I call my API with my endpoint below it works?

https://domain.flowgear.io/5000booklist/{sales_value}

What I want is to be able to re-use my workflow via an API and just pass a sales value I want to query the table against. Sales value can be 2000 or 5000 depending on what I want to achieve.

Answer 1

1. Add a variable bar and add a property to it called "salesValue"
2. In the workflow detail pane, provide this url: "/booklist/{salesValue}" - the value in braces must match the name of the property in the variable bar
3. Add a Formatter, put your SQL template including "WHERE (Book.quantity_in_stock * Book.price) > {salesValue}" in the Expression property then add a custom field called salesValue and pin that from the variable bar salesValue property. Set Escaping to SQL.
4. Take the output of the Formatter and plug that into the SQL Query property of a SQL Query Connector.
5. Add another variable bar, and add the special properties FgResponseBody and FgResponseContentType
6. Pin the SQL result to FgResponseBody and set FgResponseContentType to 'text/xml'

If you want to return JSON, convert the result from the SQL Query to JSON using JSON Convert and then pin that to FgResponseBody and set FgResponseContentType to 'application/json'