A project I'm currently working has a requirement to enable ad-hoc query capabilities. This means I have to create a GUI that allows a user to declare the information they want and convert that declaration into something my data storage (SQL Server, in this case) understands. GUI to SQL expression.
Although it can be a challenge to conceive and build a GUI - the more difficult part is to accurately convert the values in the GUI to a SQL statement. In past projects, I have simply done a ton of string manipulations to get to the end result, and that works okay... until I want to add capabilities.... then the string refactoring begins.
Squel is a JavaScript API that removes almost all of the tedium from generating valid SQL.... including a "flavour" option to target MS Sql or MySql family databases.
Where to get Squel
https://hiddentao.github.io/squel/
NOTE
The author has archived the library. You will have the full source code - but you should take care to make sure that your implementation works as desired. If not, your only recourse is to tweak the library.
How I Implemented Squel
My first step was to create a set of Views in the database against which my Advanced Find tool will query. This afforded me the opportunity to
- Align table fieldnames more closely with the application UI and leverage the user's familiarity with the UI to more intuitively go after data.... i.e. the names of things they see in the GUI match the names they are used to seeing in the application.
- Another big plus here is that I can hide fields from the user that they either don't need to have access to or should be secured.
- Finally, this seemed like a great way to resolve the foreign key values and include common data from related tables - this allows the users to not have to relate tables in the GUI quite so often.
The second step was to populate a table with "Joinable Fields" data. This table helps simplify the process of creating queries with multiple tables by defining the fields in each table that are either the table primary key or foreign keys to other tables. In the GUI, this results in vastly limiting the "from" and "to" fields required to join tables together - not perfect, but very helpful.
The third step was to create the Advanced Find GUI. Because of the prior two steps, I'm able to effectively use information_schema to fetch and present the names of the views and fieldnames - which are now very friendly. I chose to create a GUI similar to MS Access, with a glaring difference (which I'll save for a future update of the software).
The final step was to "scrape" the values from the GUI, convert that to a SQL expression and run it. This is where Squel comes into play.... making it almost trivial.
The Advanced Find GUI
- Choose the table/field you want to output
- or not - maybe you just want to filter a field, but not display it
- no limit on number of field selections
- Alias any field
- Multi-level sorting
- Five filter "columns"
- each column is internally AND with OR between columns
- Join definitions between tables
- with some auto-fill where joins are obvious
- Data types are integrated so that we know when to enclose filter parameters in quotes and when not to
- Duplicate field names are detected and automatically Aliased
- Existence of appropriate Joins is validated
The Advanced Find Results
The Generate SQL Code
Although there is a lot of parsing through the DOM here, focus on the lines that involve the "sql" object (which is an instance of the Squel API).
//this will roll through the page and
//populate the squel object...
let selectedTables = iq.AdvancedFind.getSelectedTables();
if (selectedTables.length === 0) {
return;
}
let rows = iq.AdvancedFind.getBuilderRows();
//fire up the sqluel api object
let top = count == 0 ? 1000 : count;
let sql = squel.useFlavour("mssql");
sql = squel.useFlavour("mssql").select({ fieldAliasQuoteCharacter: "" }).top(top);
//add the fields
rows.forEach(row => {
if (row.TableName.length > 0) {
sql.field("qry_" + row.TableName + "." + row.FieldName, (row.FriendlyName.length > 0 ? "[" + row.FriendlyName + "]" : "") );
}
});
//add the from section/joins
if (selectedTables.length === 1) {
sql.from("qry_" + selectedTables[0].TableName);
} else {
let joinRows = iq.AdvancedFind.getJoinRows();
let isValid = true;
joinRows.forEach(row => {
if (row.FromTable.length === 0
|| row.FromField.length === 0
|| row.ToTable.length === 0
|| row.ToField.length === 0) {
isValid = false;
}
});
if (!isValid) {
iq.Error.banner = "Oops!";
iq.Error.heading = "Missing Table Connections";
iq.Error.errorMsg = "When you select more than 1 table, you also have to provide the connection information for those tables.";
iq.Error.errorMsgPlain = "When you select more than 1 table, you also have to provide the connection information for those tables.";
iq.Error.popError(false);
return;
}
sql.from("qry_" + joinRows[0].FromTable);
joinRows.forEach(jr => {
if (jr.ToTable.length > 0) {
if (jr.JoinType.toLowerCase() === "inner") {
sql.join("qry_" + jr.ToTable, null, "qry_" + jr.FromTable + "." + jr.FromField + " = qry_" + jr.ToTable + "." + jr.ToField);
} else {
sql.left_join("qry_" + jr.ToTable, null, "qry_" + jr.FromTable + "." + jr.FromField + " = qry_" + jr.ToTable + "." + jr.ToField);
}
}
});
}
//where section
//we're going to build an expression for each column filter, or1, orX
let fullExpr = squel.expr();
let filterRows = rows.filter(row => row.Filter.length > 0);
let fExpr = squel.expr();
let or1Rows = rows.filter(row => row.Or1.length > 0);
let or1Expr = squel.expr();
let or2Rows = rows.filter(row => row.Or2.length > 0);
let or2Expr = squel.expr();
let or3Rows = rows.filter(row => row.Or3.length > 0);
let or3Expr = squel.expr();
let or4Rows = rows.filter(row => row.Or4.length > 0);
let or4Expr = squel.expr();
//populate the individual expressions and append to the fullExpr
if (filterRows.length > 0) {
filterRows.forEach(row => {
fExpr.and("(" + "qry_" + row.TableName + "." + row.FieldName + " " + row.Filter + ")");
});
fullExpr.and("(" + fExpr.toString() + ")");
}
if (or1Rows.length > 0) {
or1Rows.forEach(row => {
or1Expr.and("(" + "qry_" + row.TableName + "." + row.FieldName + " " + row.Or1 + ")");
});
fullExpr.or("(" + or1Expr.toString() + ")");
}
if (or2Rows.length > 0) {
or2Rows.forEach(row => {
or2Expr.and("(" + "qry_" + row.TableName + "." + row.FieldName + " " + row.Or2 + ")");
});
fullExpr.or("(" + or2Expr.toString() + ")");
}
if (or3Rows.length > 0) {
or3Rows.forEach(row => {
or3Expr.and("(" + "qry_" + row.TableName + "." + row.FieldName + " " + row.Or3 + ")");
});
fullExpr.or("(" + or3Expr.toString() + ")");
}
if (or4Rows.length > 0) {
or4Rows.forEach(row => {
or4Expr.and("(" + "qry_" + row.TableName + "." + row.FieldName + " " + row.Or4 + ")");
});
fullExpr.or("(" + or4Expr.toString() + ")");
}
sql.where(fullExpr.toString());
//SORT SECTION
let sortRows = rows.filter(row => row.SortDirection !== "Not Sorted");
sortRows.sort((rowA, rowB) => {
return rowA.SortOrder - rowB.SortOrder;
});
//process filtered/sorted resultSet
sortRows.forEach(row => {
if (row.SortDirection === "Ascending") {
sql.order("qry_" + row.TableName + "." + row.FieldName);
} else {
sql.order("qry_" + row.TableName + "." + row.FieldName, false);
}
});
return sql.toString();
Summary
At first glance, it might seem like that's a lot of code to generate a SQL statement - but it's really nothing like having to do those string manipulations and contortions to end up with valid SQL.
