TsgcHTMLComponent_QueryBuilder: a visual query and filter builder. Users combine conditions (field, operator, value) in AND and OR groups, by hand or from a question in plain language answered by the AI Assistant, and the server turns the result into a parameterised SQL condition, a row match or a filter on a Grid, in Delphi, C++ Builder and .NET.
Add the fields a rule can name, or link a TsgcHTMLComponent_Grid to use its columns, and render it. Every button posts a server event, the component validates the tree of that request, and OnApply hands you the tree to query your data.
TsgcHTMLComponent_QueryBuilder, with TsgcHTMLQueryNode for the tree of conditions
A tree of conditions and groups as Bootstrap 5 markup, server rendered and without script
Delphi, C++ Builder, .NET
Fields lists what a rule can name. OnApply receives the validated tree of the request, GetSQLWhere writes it as a SQL condition with named parameters, and ToJSON keeps it for the session of the user.
uses
sgcHTML_AI, sgcHTML_Component_QueryBuilder;
// Created once: it answers its own server events
oQB := TsgcHTMLComponent_QueryBuilder.Create(Self);
oQB.ElementName := 'orders';
oField := oQB.Fields.Add;
oField.Name := 'customer';
oField.Caption := 'Customer';
oField := oQB.Fields.Add;
oField.Name := 'amount';
oField.Caption := 'Amount';
oField.DataType := aidtInteger;
oField.SQLName := 'o.amount';
oField := oQB.Fields.Add;
oField.Name := 'status';
oField.Caption := 'Status';
oField.Options.Add('open=Open');
oField.Options.Add('paid=Paid');
oQB.HTMXEngine := HTMXEngine1;
oQB.AIAssistant := sgcHTMLAIAssistant1;
oQB.OnApply := QueryBuilderApply;
Result := oQB.HTML;
// aRules is the tree of this request, Rules is not touched
procedure TForm1.QueryBuilderApply(Sender: TObject;
const aRules: TsgcHTMLQueryNode; var aResponse: string);
var
oQB: TsgcHTMLComponent_QueryBuilder;
oParams: TStringList;
vWhere: string;
i: Integer;
begin
oQB := TsgcHTMLComponent_QueryBuilder(Sender);
oParams := TStringList.Create;
try
vWhere := oQB.GetSQLWhere(oParams, aRules);
FDQuery1.Close;
FDQuery1.SQL.Text := 'select * from orders';
if vWhere <> '' then
FDQuery1.SQL.Add('where ' + vWhere);
for i := 0 to oParams.Count - 1 do
FDQuery1.Params.ParamByName(oParams.Names[i]).AsString :=
oParams.ValueFromIndex[i];
FDQuery1.Open;
DoSaveFilter(oQB.ToJSON(aRules)); // your session store
aResponse := DoOrdersHTML(FDQuery1); // rows marked hx-swap-oob
finally
oParams.Free;
end;
end;// includes: sgcHTML_AI.hpp, sgcHTML_Component_QueryBuilder.hpp
oQB = new TsgcHTMLComponent_QueryBuilder(this);
oQB->ElementName = "orders";
TsgcHTMLQueryField *oField = oQB->Fields->Add();
oField->Name = "customer";
oField->Caption = "Customer";
oField = oQB->Fields->Add();
oField->Name = "amount";
oField->Caption = "Amount";
oField->DataType = aidtInteger;
oField->SQLName = "o.amount";
oQB->HTMXEngine = HTMXEngine1;
oQB->OnApply = QueryBuilderApply;
html = oQB->HTML;
void __fastcall TForm1::QueryBuilderApply(TObject *Sender,
TsgcHTMLQueryNode* const aRules, UnicodeString &aResponse)
{
TsgcHTMLComponent_QueryBuilder *oQB = (TsgcHTMLComponent_QueryBuilder *)Sender;
TStringList *oParams = new TStringList();
try
{
UnicodeString vWhere = oQB->GetSQLWhere(oParams, aRules);
FDQuery1->Close();
FDQuery1->SQL->Text = "select * from orders";
if (vWhere != "")
FDQuery1->SQL->Add("where " + vWhere);
for (int i = 0; i < oParams->Count; i++)
FDQuery1->Params->ParamByName(oParams->Names[i])->AsString =
oParams->ValueFromIndex[i];
FDQuery1->Open();
DoSaveFilter(oQB->ToJSON(aRules));
aResponse = DoOrdersHTML(FDQuery1);
}
__finally
{
delete oParams;
}
}using esegece.sgcWebSockets;
var qb = new TsgcHTMLComponent_QueryBuilder();
qb.ElementName = "orders";
var field = qb.Fields.Add();
field.Name = "customer";
field.Caption = "Customer";
field = qb.Fields.Add();
field.Name = "amount";
field.Caption = "Amount";
field.DataType = TsgcHTMLAIDataType.aidtInteger;
field.SQLName = "o.amount";
qb.HTMXEngine = htmxEngine;
qb.AIAssistant = aiAssistant;
// aRules is the tree of this request
qb.OnApply += (object sender, TsgcHTMLQueryNode aRules, ref string aResponse) =>
{
var parameters = new List<string>();
string where = qb.GetSQLWhere(parameters, aRules);
using var cmd = connection.CreateCommand();
cmd.CommandText = "select * from orders" + (where != "" ? " where " + where : "");
foreach (var p in parameters)
{
var kv = p.Split(new[] { '=' }, 2);
cmd.Parameters.AddWithValue(":" + kv[0], kv[1]);
}
DoSaveFilter(qb.ToJSON(aRules));
aResponse = DoOrdersHTML(cmd.ExecuteReader());
};
string html = qb.HTML;A filter screen for people who do not write SQL, and a condition your code can run safely on any database.
Every condition names a field, an operator and one or two values, and every group joins its children with AND or OR. Groups nest up to MaxDepth levels and a tree holds at most MaxRules rules. Add condition and Add group start on the first field in effect.
The DataType of a field decides which operators the user can pick: equals, does not equal, contains, starts with, ends with, greater, less, between, in list, is empty and is not empty. A field with Options, a closed list of values, allows a shorter list.
With an AIAssistant and AIPrompt True, an Ask box turns a question into AND rules over the fields in effect. The rules land in the builder, where the user can still change them before applying, and the explanation of the answer shows under the box.
An assistant with no provider configured answers with its rule based parser, so the Ask box works offline. When a provider call fails, UseRuleBasedFallback (True by default) lets the same parser answer.
Link a TsgcHTMLComponent_Grid and the builder takes its fields from the grid columns when Fields is empty. ApplyToGrid filters the rows of the grid through its RowFilter, and LoadFromGridOps fills a tree with one rule per filter of a grid operation set.
Every control is named <ElementName>.<path>.<key>, where the path is the index path of the node and the key names the combinator, field, operator or value. LoadFromRequest reads those names back, so the builder also works inside a plain form posted without htmx.
The members you reach for most often.
Fields holds the fields a rule can name, each with its Caption, DataType, SQLName and optional Options, one value or value=caption per line. Without items, the columns of Grid are the fields in effect. FindField, GetFieldCount and IsOperatorAllowed answer questions about them.
OnApply(Sender, aRules, aResponse) fires after Apply, Clear and an answered Ask, with the validated tree of the request. What you return in aResponse is appended to the answer, for instance your rows marked hx-swap-oob. Without OnApply, ApplyToGrid copies the tree into Rules and filters the linked Grid.
Returns the condition without the WHERE keyword, or an empty string for an empty tree. Values are only written as the named parameters :qb1, :qb2 and so on, each added to aParams as qb1=<value>, so the user text cannot change the statement. The column is the SQLName of the field, or its Name.
Everything that fills a tree from outside, JSON, an AI answer or a posted form, goes through the same validation. Rules on fields that are not in effect, operators the type does not allow, rules over MaxRules and groups deeper than MaxDepth are dropped, values are cut to 200 characters and empty sub groups are removed. SQLName goes into the SQL as is, so set it in code and never from a request.
ToJSON writes a group as {"c":"and","r":[...]} and a rule as {"f":"field","o":"operator","v":"value"}, plus "v2" for between. ParseJSON and LoadFromJSON read it back and validate it.
Matches evaluates the tree on one row given as Name=Value pairs, for rows held in memory. Every helper takes an optional tree, and nil means Rules, so an OnApply handler works on the tree of the request without touching the shared component.
One component serves every session, because the server event works on the tree of the request. On a page shared by several users, assign OnApply, keep ToJSON(aRules) in the session, and render the builder of each user with ParseJSON and GetBuilderHTML.
Every button, and the field and operator selects when they change, post the server event qb to the component events endpoint of the HTMX engine, or to ActionURL. The answer is the builder marked hx-swap-oob="true", which htmx replaces by its id: QueryBuilderID, or qb_ plus ElementName.
Demos\60.HTML\01.RunTime\25.QueryBuilder is an orders page on port 8127 with the builder above the grid and a SQL card that shows the parameterised WHERE clause the same rules would run on a database. Its AI Assistant has no API key, so the Ask box answers offline, and every visitor keeps their own rules in the session. The design time, Lazarus and .NET versions mirror it.
| Try It OnlineSee it in the Query Builder demo, running live on the eSeGeCe demo server. | Open | |
| Online HelpFull API reference and usage guide for this component. | Open | |
| Grid ComponentThe grid the builder filters and takes its columns from. | Open | |
| AI AssistantTurns a question in plain language into the rules of the Ask box. | Open | |
| All sgcHTML ComponentsBrowse the full feature matrix of 80+ components. | Open | |
| Download Free TrialThe 30-day trial ships the 60.HTML demo projects. | Open | |
| PricingSingle, Team and Site licenses with full source code. | Open |