Overview

In Bhuma, you can enhance your components, such as tables, by embedding SQL queries directly into them. This feature lets you dynamically load data based on the component's context. JavaScript templating can incorporate component data into the SQL query for more complex queries.

Example: Customer Data Table

Objective

Create a dynamic table displaying customer data, which can be filtered based on user inputs from various widgets.

Query Structure

The SQL query will join multiple tables (customer, nation, region) and allow dynamic filtering based on user inputs.

SQL Query with Templating

SELECT c.*, r.name AS regionName, n.name AS nationName
FROM customer c
JOIN nation n ON n.nationkey = c.nationkey
JOIN region r ON r.regionkey = n.regionkey
WHERE c.name LIKE '%{{ widget['658c35c6d83241778b7826c9']?.value ? widget['658c35c6d83241778b7826c9']?.value : '' }}%'
{{(widget['65120371a03d36913219b90c'].value?.length > 0 ? 'AND c.mktsegment IN (' + widget['65120371a03d36913219b90c'].value.map(s => `'${s}'`).join(', ') + ')' : '') + (widget['658c27cdd83241778b7826a7'].value?.length > 0 ? 'AND r.regionkey IN (' + widget['658c27cdd83241778b7826a7'].value.join(', ') + ')' : '')}}
ORDER BY c.acctbal DESC

Explanation of Templating

• {{ widget['658c35c6d83241778b7826c9']?.value }}: Dynamically inserts the value from the widget with ID 658c35c6d83241778b7826c9. If the widget has no value, an empty string is used.
• widget['65120371a03d36913219b90c'].value?.length > 0: Checks if the widget contains any values. If it does, it appends a condition to filter c.mktsegment.
• widget['658c27cdd83241778b7826a7'].value?.length > 0: Similar to the previous point, this checks for values in another widget to filter by r.regionkey.

Guidelines for Creating Custom Queries

1. Identify Widgets: Determine which widgets in your component will influence the query.
2. Define Placeholders: Use {{ widget['widgetID']?.value }}to insert values from widgets into your SQL query.
3. Handle Empty Inputs: Ensure your query accounts for scenarios where the widget might not have a value.
4. Test Queries: Before deploying, test your queries with various inputs to ensure they behave as expected.

Conclusion

Using SQL queries with JavaScript templating in Bhuma allows for the creation of dynamic and interactive components. Following the guidelines above, you can efficiently integrate complex querying capabilities into your Bhuma applications.