Query Builder Form

Query Builder Form Guide

The Query Builder Form interface allows administrators to build dynamic database queries either using step-by-step visual UI blocks (Normal Query Builder) or by writing free-form SQL text statements (Custom Query Mode).

Two Query Builder Modes

Query Builder supports two distinct operational modes:

1. Normal Query Builder

Interactive block-based interface where administrators select Base Tables, output columns, joins, filter conditions, group by rules, and sort orders without writing SQL code.

2. Custom Query Mode

Free-form SQL text editor activated by clicking "Custom Query". Best for complex SQL queries (CASE WHEN, UNION, subqueries) and session parameter tags ({company_id}, {current_user}).

1. QUERY BUILDER Main Metadata

Main metadata inputs located at the top of the Query Builder Form:

Field Name	Input Type	Description & Requirement
Query Builder Name *	Text Input	Human-readable query title (e.g. User Details). Mandatory.
Query Builder Slug *	Text Input	Unique system slug used in code calls (e.g. `user-details`). Mandatory.
Base Table *	Dropdown	Primary database table. Selecting table loads columns automatically. Mandatory in Normal Mode.
Query Builder Description	Textarea	Description of query purpose and usage notes. Optional.
Query Type *	Dropdown	Operation type: `SELECT` (1), `INSERT` (2), or `UPDATE` (3). Mandatory.
Store Variable *	Text Input	PHP variable storing query result set (e.g. `userDetails`). Mandatory in Normal Mode.

2. COLUMNS Block

When a Base Table is selected, its columns display automatically with checkboxes:

Check column boxes to include fields in the SELECT clause.
Each column receives a table alias prefix (e.g. oj1.nm_first_name).
Enter custom display aliases (e.g. AS nm_partner) for joined reference fields.

3. JOINS Block

Click "+ Join" to add relational table joins:

Reference Table Restriction: Dropdown populates ONLY reference tables defined in nm_app_schema_reference_key.
Join Types: Select LEFT, RIGHT, or INNER join.
Table Aliases: System assigns aliases (e.g. oj2, oj3) automatically.
Join Conditions: Specify join keys (e.g. oj1.nm_company_id = oj2.nm_company_id).

4. CONDITIONS Block (WHERE Clause)

Click "+ Condition" to build filtering rules:

Field Selection: Select target column from base or joined tables.
Operators: Choose =, !=, >, <, >=, <=, LIKE, IN, NOT IN, IS NULL, IS NOT NULL.
Dynamic Session Values: Use parameter tags such as {current_user} or {company_id} for dynamic context evaluation.
AND / OR Logic: Combine multiple conditions using logical AND or OR operators.

5. GROUP BY & HAVING Blocks

Group By (+ Group): Select columns for SQL aggregation grouping.
Having (+ Having): Define condition filters applied after aggregation.

6. ORDER BY Block

Click "+ Order" to specify sorting columns and direction (ASC or DESC). Supports multiple ordering columns.

7. LIMIT & OFFSET Controls

Numeric inputs defining maximum row return count (LIMIT) and starting row index (OFFSET).

8. CUSTOM QUERY MODE (Free-Form SQL)

Click "Custom Query" (btnCustomQuery) to switch to Custom Query Mode:

SQL Text Editor: Direct text area for writing raw SQL queries.
Complex SQL Support: Supports complex SQL statements including CASE WHEN, UNION, subqueries, and custom joins.
Session Parameter Tags: Embed dynamic tags like {company_id} or {current_user}.
Storage: Custom queries store "is_custom": true, "raw_sql", and "custom_sql" in nm_query_json.

9. GENERATE QUERY Action

Click "GENERATE QUERY" before saving:

Compilation Engine: JavaScript compiles UI blocks or custom SQL into formatted JSON (nm_query_json) and SQL string preview.
Preview Display: Displays generated JSON and SQL in code preview textareas.
CRITICAL NOTE: GENERATE QUERY compiles and previews the query—it does NOT execute SQL against the database data.

Normal Query vs. Custom Query Comparison

Dimension	Normal Query Builder	Custom Query Mode
Query Construction	UI block builder (+ Join, + Condition)	Direct free-form SQL text editor
Base Table	Mandatory selection from dropdown	User writes SQL statement directly
Select Columns	Checkbox selection with auto-alias	Custom SQL controls SELECT statement
Join Restrictions	Restricted to `nm_app_schema_reference_key`	Custom SQL supports any valid JOIN syntax
Compilation	Compiles UI blocks into JSON	Stores raw SQL with `"is_custom": true`

Best Practices

Use Normal Query Builder for standard table queries to ensure clean schema reference validation.
Use Custom Query Mode for complex multi-table analytical reporting.
Always click "GENERATE QUERY" to verify JSON/SQL formatting before clicking "Save Query".