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'

Answer 2

@sanjay I will try to give you an overview of what I did back then when I was experimenting with Flowgear through PHP following instructions from here.

I am not sure if you are also invoking the Flowgear REST API through PHP or any other language but regardless I presume logic should remain the same.

What I did was to wrap the PHP CURL sample code in a class so that I can be able to reuse it. Below is a code I wrote for a simple select query:

<?php

 //Require the FlowgearConnect class
 require_once '/path/to/flowgear_class_with_api_call.php';

try{

   $workflow = new FlowgearConnect(return include 'endpoints.php');

   $serial = $_POST['serial'];
   $clientId = $_POST['client_id'];

   //Get the results
   $sql = '';
   if(empty($serial)){
     $conditions = sprintf(' `a`.`client_id` = %s AND `a`.`serial` > -1 ORDER BY `a`.`serial` ASC', $clientId);
    }else{
      $conditions = ' `a`.`serial` = ' . $serial;
   }

   /**
    In your workflow you will most probably have a VARIABLE BAR that holds your request parameters which is what $conditions speaks to.
   */
    $conditions = array('conditions' => $conditions);

    $results = $workflow->getResults('orders', 'orders', $conditions);
}catch(catch any exceptions thrown by the API here){
  //Log the exceptions here or do whatever
}

The listing above should be self explanatory. Below I will show you the functions I have made use of from my FlowgearConnect class. This is not a standard way as you may configure your code differently to suite your needs.

//FlowgearConnect constructor
class FlowgearConnect
{
 protetced $endpoints = [];

  protected $domain = "https://your-domain.flowgear.io";

  public function __construct(array $endpoints)
  {
    $this->endpoints = $endpoints;
   }

  public function getResults($model, $workflow, $options= array())
  {
     $endpoint = $this->getEndpoint($model, $workflow);

     $results = array();
     if(!empty($endpoint)){
        $results = FlowgearInvoke::run($authOpts, $endpoint, $options, array('timeout' => 30)); 
      }
      return $results;
   }

   ....
}

The enpoints.php file, as mentioned before, just returns an array of configured endpoints and/or worflow names from within flowgear console. Below is a excerpt of how mine looked like:

return array(
    'orders' => array(
         'shipped_orders' => '/shipped_orders',
         //etc
       ),
     'items' => array(
        'your_model' => '/workflow_name_from_flowgear_console',
       ),
  );

This is just a basic select query with Flowgear's REST API using PHP. If you are lucky you should get your records the way you have configured your response body for your workflow.

Below is a typical testing of a workflow and what you should get back in your API.

I advice you to first create your workflows on your flowgear console and make sure that the produce the desired output and the extract the parts that you want changed no your query, move them to a variable bar for your request and have them injected at run-time based on what you looking to achieve. This explanation can be substituted for other operations such as update and/or delete. Best thing is to understand flowgear first and make sure that you can have everything working there before attempting to create a restful interactive application.

Caution: It's over a year that I have since worked with this platform so you might find errors in this but I am hoping that it will lead you to finding a solution for your problem. If not then perhaps you can create a repo and have me check it out to see how you are configuring everything.