Query Builder

Overview

The Query Builder in Servoy is a versatile tool designed for constructing dynamic database queries programmatically. It offers an intuitive API that allows developers to build, modify, and execute complex queries at runtime. Being database-agnostic, Query Builder seamlessly adapts to different database systems without requiring manual SQL adjustments. It also ensures consistent data access by automatically applying table and foundset filters. This chapter explores the capabilities of Query Builder, showcasing its effectiveness in data retrieval and manipulation within Servoy applications.

Advantages of Using Query Builder

Using the Query Builder in Servoy brings several key benefits that enhance the way you interact with databases, offering a more efficient, consistent, and flexible approach to query construction and execution:

Database Agnostic: Allows for seamless migration between different database systems without needing to rewrite queries.
Automatic Inclusion of Filters: Ensures that table and foundset filters are always considered, providing consistent data access.
Programmable API: Enables dynamic extension and modification of queries, offering greater flexibility in query construction and execution.

Query Builder Examples

QB with Logical AND

This example demonstrates how to filter data using multiple conditions that must all be true. It's ideal for scenarios where you need to apply strict criteria to retrieve specific records, such as filtering customers by city and postal code.

var q = datasources.db.example_data.customers.createSelect();
q.where.add(
    q.columns.city.eq('Berlin')
).add(
    q.columns.postalcode.eq('111')
);

foundset.loadRecords(q);

This query uses the AND operator to filter records where the city is Berlin and the postal code is 111. The conditions are combined using q.where.add() to create an intersection of both criteria.

QB with Logical OR

This example shows how to filter data where any of the specified conditions can be true. It's useful when you need to retrieve records that meet one of several possible criteria, such as customers in different cities.

var q = datasources.db.example_data.customers.createSelect();
q.where.add(
    q.or.add(
        q.columns.city.eq('Berlin')
    ).add(
        q.columns.city.eq('London')
    )
);

foundset.loadRecords(q);

This query uses the OR operator to filter records where the city is either Berlin or London. The conditions are grouped using q.or.add() to combine them logically.

Using Relations in QB for Joins

This example illustrates how to join related tables in a query, retrieving combined data based on relationships between tables. It's essential when working with relational databases to ensure comprehensive data retrieval across related entities.


var q = datasources.db.example_data.orders.createSelect();
q.where.add(
    q.joins.customers.columns.city.eq('Berlin')
);

foundset.loadRecords(q);

This example shows how to join two related tables, such as orders and customers, and filter the orders based on the customer’s city. The query joins the tables using the relationship defined in the Servoy model.

QB Complex Search with Conditions

This example highlights creating complex queries by grouping multiple conditions using both AND and OR operators. It's useful for scenarios where advanced filtering logic is required, such as finding customers in different cities with specific postal codes.

var q = datasources.db.example_data.customers.createSelect();

var condition1 = q.and;
condition1.add(q.columns.city.eq('Berlin'))
          .add(q.columns.postalcode.eq('111'));

var condition2 = q.and;
condition2.add(q.columns.city.eq('London'))
          .add(q.columns.postalcode.eq('222'));

q.where.add(q.or.add(condition1).add(condition2));

foundset.loadRecords(q);

This query demonstrates how to create complex search conditions using logical operators. The query retrieves customers either in Berlin with postal code 111 or in London with postal code 222.

QB Search with Dates

This example demonstrates how to filter data within a specific date range, which is crucial for time-sensitive queries like generating reports for a specific period.

var q = datasources.db.example_data.orders.createSelect();
q.where.add(
    q.columns.orderdate.between(startDate, endDate)
);

foundset.loadRecords(q);

This query filters orders based on a date range, retrieving only those orders placed within the specified startDate and endDate.

Complex QB with Aggregates and HAVING Clause

This example shows how to perform aggregations on grouped data and filter the results based on these aggregated values. It's ideal for generating summary reports, such as identifying orders that exceed a certain total value.

var q = datasources.db.example_data.order_details.createSelect();
q.result.add(q.columns.orderid);
q.result.add(q.columns.quantity.multiply(q.columns.unitprice).sum, 'total');

q.groupBy.add(q.columns.orderid);
q.having.add(q.columns.quantity.multiply(q.columns.unitprice).sum.gt(500));

var dataset = databaseManager.getDataSetByQuery(q, -1);

This query aggregates the total value of each order and uses a HAVING clause to filter only those orders where the total value exceeds $500. The results are returned as a dataset.

Where Query Builder Can Be Used

Loading Records into the Foundset

Query Builder can be used to construct queries that load specific records into a foundset. This is useful for dynamically retrieving and displaying data that meets certain criteria within your application.

Important When using Query Builder to load records into a foundset, only the primary key (PK) should be included in the query results to ensure that the foundset functions correctly.

Example: This example demonstrates how to construct a query using Query Builder to filter customers based on city and postal code. The loadRecords(q) method then loads the results directly into the foundset.

var q = datasources.db.example_data.customers.createSelect();
q.result.add(q.columns.customerid);  // Only the primary key should be selected
q.where.add(
    q.columns.city.eq('Berlin')
).add(
    q.columns.postalcode.eq('111')
);

// Load the primary keys into the foundset
foundset.loadRecords(q);

Using Query Builder as a Foundset Filter

You can apply Query Builder to create filters that limit the records displayed in a foundset. This is ideal for scenarios where you want to enforce data constraints or provide users with a filtered view of the data.

Example: In this example, a query is constructed to filter orders based on a date range. The addFoundSetFilterParam(q, 'dateFilter') method applies this filter to the foundset, ensuring that only records matching the criteria are included.

var q = datasources.db.example_data.orders.createSelect();
q.where.add(
    q.columns.orderdate.between(startDate, endDate)
);

foundset.addFoundSetFilterParam(q, 'dateFilter');
foundset.loadRecords();

Getting Results in a Dataset

Query Builder can return results in a dataset, which is useful for processing data programmatically, generating reports, or performing analytics without directly affecting the foundset.

Example: This query groups order details by productid and calculates the total quantity ordered for each product. The HAVING clause filters results to include only products with a total quantity greater than 100. The results are stored in a dataset, which can then be iterated over and processed.

var q = datasources.db.example_data.order_details.createSelect();
q.result.add(q.columns.productid);
q.result.add(q.columns.quantity.sum, 'totalQuantity');

q.groupBy.add(q.columns.productid);
q.having.add(q.columns.quantity.sum.gt(100));

var dataset = databaseManager.getDataSetByQuery(q, -1);

// Process the dataset
for (var i = 1; i <= dataset.getMaxRowIndex(); i++) {
    var productId = dataset.getValue(i, 1);
    var totalQuantity = dataset.getValue(i, 2);
    application.output('Product ID: ' + productId + ', Total Quantity: ' + totalQuantity);
}

Creating an In-Memory Datasource by Query

You can use Query Builder to create an in-memory datasource, which is a temporary set of data used within your application. This is helpful for scenarios where you need to manipulate or display data that doesn’t need to be stored permanently in the database.

Example: This example demonstrates how to execute a query and use the resulting dataset to create an in-memory datasource. The in-memory datasource can then be loaded into a form’s foundset for further interaction.

var q = datasources.db.example_data.customers.createSelect();
q.result.add(q.columns.customerid);
q.result.add(q.columns.companyname);
q.where.add(q.columns.city.eq('Berlin'));

var dataset = databaseManager.getDataSetByQuery(q, -1);

// Create an in-memory datasource from the dataset
var dataSourceName = 'mem:customers_in_berlin';
var dataSource = dataset.createDataSource(dataSourceName, [JSColumn.TEXT, JSColumn.TEXT]);

// Use the in-memory datasource in a form
forms.myForm.controller.recreateUI();
forms.myForm.foundset.loadRecords(dataSource);

Debugging Queries: Retrieving Plain SQL and Parameters

In Servoy, debugging queries built with the Query Builder can be essential for understanding how your queries are translated into raw SQL. Servoy provides methods like getSQL() and getSQLParameters() to retrieve the generated SQL string and its corresponding parameters. These tools are invaluable for troubleshooting and ensuring that your queries perform as expected.

Here are some examples:

Debugging a Simple Query

This example demonstrates how to retrieve and inspect the SQL query and its parameters generated by the Query Builder. It's useful for verifying that a basic query performs as expected and returns the correct results.

var q = datasources.db.example_data.customers.createSelect();
q.where.add(q.columns.city.eq('Berlin'))
    .add(q.columns.postalcode.eq('111'));

// Retrieve and print the plain SQL and parameters
var sqlString = q.getSQL();
var sqlParams = q.getSQLParameters();
application.output("Generated SQL: " + sqlString);
application.output("SQL Parameters: " + sqlParams);

// Load the records into the foundset
foundset.loadRecords(q);

Explanation:

q.getSQL(): Returns the generated SQL query as a string.
q.getSQLParameters(): Returns an array of parameters that will be used in the query, substituting the ? placeholders in the SQL string.

Output Example:

Generated SQL: SELECT customerid FROM customers WHERE city = ? AND postalcode = ?
SQL Parameters: [Berlin, 111]

Debugging a Query with Joins and Aggregates

This example shows how to extract the SQL and parameters for a more complex query involving table joins and aggregation. It's particularly useful for ensuring that complex data relationships and calculations are correctly represented in the generated SQL.

var q = datasources.db.example_data.orders.createSelect();
q.result.add(q.columns.orderid);
q.result.add(q.columns.quantity.sum, 'totalQuantity');

q.groupBy.add(q.columns.orderid);
q.having.add(q.columns.quantity.sum.gt(500));

q.joins.customers.columns.city.eq('Berlin');

// Retrieve and print the plain SQL and parameters
var sqlString = q.getSQL();
var sqlParams = q.getSQLParameters();
application.output("Generated SQL: " + sqlString);
application.output("SQL Parameters: " + sqlParams);

// Load the records into the foundset
foundset.loadRecords(q);