# 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.

```javascript
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.

```javascript
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.

```javascript

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.

```javascript
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.

```javascript
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.

```javascript
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.

{% hint style="info" %}
**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.
{% endhint %}

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.

```javascript
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.

```javascript
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.

```javascript
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.

```javascript
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.

```javascript
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.

```javascript
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);
```

Output Example:

```plaintext
Generated SQL: SELECT orderid, SUM(quantity) AS totalQuantity FROM orders INNER JOIN customers ON orders.customerid = customers.customerid WHERE customers.city = ? GROUP BY orderid HAVING SUM(quantity) > ?
SQL Parameters: [Berlin, 500]
```

## Related Articles

* [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect#qbselect)
* [QBWhereCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbwherecondition#qbwherecondition)
* [QBResult](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbresult#qbresult)