Relational Data Query Node in Pathways

The Relational Data Query node in FojiSoft Pathways enables workflows to retrieve data from a relational database and store the results in a variable for further processing. This node supports dynamic query customization using either Handlebars Templates or Scripts, making it a versatile tool for data-driven workflows.

---

Node Properties

1. Name

• Description: A unique identifier for the node within the workflow.
• Usage: Provide a clear, descriptive name to indicate the purpose of the query (e.g., "Fetch Pending Claims" or "Retrieve Customer Data").
• Example: "Get Unpaid Invoices"

---

2. Variable Name

• Description: The name of the variable where the query results will be stored.
• Usage: Use a meaningful name to make the variable easy to reference in downstream nodes.
• Example: unpaid_invoices

---

3. Query

• Description: The SQL-like query used to fetch data from a relational database.
• Usage: Write a valid query to retrieve the necessary data. The query can be parameterized using dynamic values defined in the Query Parameters section.

Example:

SELECT * FROM invoices WHERE status = 'Unpaid' AND due_date < NOW()

---

Query Parameters

The Query Parameters section allows you to define dynamic values for use within the query. These parameters can be configured using either Handlebars Templates or Scripts.

3.1 Name

• Description: Assigns a name to the parameter used within the query.
• Usage: Use descriptive names that clarify the purpose of the parameter (e.g., Status, StartDate, EndDate).
• Example: InvoiceStatus

3.2 Expression

• Type: Handlebar Template or Script
• Description: Defines the value of the parameter dynamically.
• Options:
  • Handlebar Template: Use placeholders to inject dynamic values into the query.

Example:

{{status}}

• Where status is a variable from the workflow.

• Script: Write custom logic to generate or calculate the parameter value.

Example (Script):

if overdue:

   return "Unpaid"

else:

   return "Paid"

• Expression Field:
  • Status: Required.
  • Usage: Define the logic or value for each parameter to dynamically adapt the query.

---

How It Works

1. Define Query:
   • Write a SQL-like query in the Query field, using placeholders for dynamic parameters if needed.
2. Add Query Parameters:
   • Define each parameter using either a Handlebar Template or a Script to provide dynamic values.
3. Execute Query:
   • When the node is reached in the workflow, the query is executed, and the results are stored in the variable defined in the Variable Name field.
4. Use Results:
   • The results stored in the variable can be referenced in downstream nodes for further processing, such as loops, notifications, or data updates.

---

Use Case Examples

1. Retrieve Overdue Payments

• Name: Fetch Overdue Payments
• Variable Name: overdue_payments

Query:

SELECT * FROM invoices WHERE due_date < NOW() AND status = {{status}}

• Query Parameters:
  • Name: status
  • Expression (Handlebars): {{payment_status}}
• Outcome: Retrieves all overdue payments and stores them in the overdue_payments variable for further processing.

2. Fetch Recent Orders

• Name: Get Recent Orders
• Variable Name: recent_orders

Query:

SELECT * FROM orders WHERE order_date BETWEEN {{start_date}} AND {{end_date}}

• Query Parameters:
  • Name: start_date

Expression (Script):

return today() - timedelta(days=30)

• Name: end_date
• Expression (Handlebars): {{current_date}}

• Outcome: Retrieves all orders placed in the last 30 days and stores the results in the recent_orders variable.

---

Best Practices

1. Parameterize Queries:
   • Use query parameters to make queries adaptable and reduce hardcoding.
2. Use Descriptive Names:
   • Clearly name the variable and parameters to make the workflow easier to maintain.
3. Validate Queries:
   • Test queries independently to ensure they return the expected results before integrating them into workflows.
4. Optimize Queries:
   • Limit the number of rows retrieved by adding constraints to improve performance.

---

The Relational Data Query node is an essential component in Pathways, enabling dynamic data retrieval for workflows. Its flexibility to use either Handlebar Templates or Scripts for parameterization ensures adaptability for a wide range of use cases.