Query Builder

Astra TechnoAbout 3 min

The Query class is a powerful, reusable, and flexible query builder designed to solve real-world problems in application development, such as dynamic query generation, data filtering, and complex query structures. This allows developers to construct and execute dynamic SQL queries effortlessly. Below is the complete documentation of its methods, features, and real-world application usage.

Overview

The Query class allows developers to define and manage queries programmatically. It supports multiple select types, dynamic parameters, and advanced filtering, enabling:

Reusability: Define reusable query objects to minimize repetitive code.
Security: Sanitize user input dynamically to prevent SQL injection.
Flexibility: Handle complex queries with nested subqueries, aggregates, and multiple conditions.
Scalability: Extend queries and combine them for efficient union operations.

Creating a Query Object

To create a query object, use the global Query function provided by DataForge. For example:

$query = Query('ProductList');

Purpose: The above code instantiates a new Query Builder object and names it ProductList.
Named Query: The query name is used to create logs & hide backend query errors while displaying them on the frontend. This improves user experience by presenting meaningful error information.

This is the starting point for building complex queries in the DataForge package.

Class Methods

1. `select($name, $fields)`

Purpose: Defines a named set of fields to be selected in the query.

Parameters:

$name (string): The identifier for the selection set (e.g., list, item).
$fields (string): The SQL fields, including aggregates, subqueries, or raw SQL expressions.

Example:

$query->select('list', 'p.id AS productId, p.name, p.price, p.stock, c.name AS category');
$query->select('item', 'p.id AS productId, p.name, p.price, p.stock, p.description, c.name AS category, p.image_url');
$query->select('total', 'COUNT(p.id) AS totalCount');

Real-World Use: Useful for creating modular responses like summary lists and detailed views without redefining the base query.

Try It:

Endpoint:

Try It:

Endpoint:

2. `from($table)`

Purpose: Specifies the base table for the query.

Parameters:

$table (string): The table name or alias.

Example:

$query->from('products AS p');

Real-World Use: Essential for defining the data source in queries.

3. `filter($condition)`

Purpose: Defines mandatory WHERE conditions. Throws an error if the parameter is missing.

Parameters:

$condition (string): The SQL condition with dynamic placeholders (e.g., {request.param}).

Example:

$query->filter('p.status = 1');
$query->filter('p.category_id = {request.categoryId}');

Real-World Use: Prevents incomplete queries by enforcing essential conditions like user permissions or data visibility.

Try It:

Endpoint:

4. `filterOptional($condition)`

Purpose: Defines optional WHERE conditions. Skips the condition if the parameter is missing.

Parameters:

$condition (string): The SQL condition with dynamic placeholders.

Example:

$query->filterOptional('p.price BETWEEN {request.minPrice} AND {request.maxPrice}');
$query->filterOptional('p.name LIKE {%request.keyword%}');

Key Feature Highlight: This method allows you to design queries that adapt dynamically based on user input without breaking the query structure. It ensures flexibility for advanced searches and filters.

Real-World Use: Facilitates advanced searches and filters, applying only when relevant parameters are provided.

Try It:

Endpoint:

5. `filterAnyOneRequired($groupName, $conditions)`

Purpose: Ensures at least one condition in a set is applied, throwing an error if all are skipped.

Parameters:

$groupName (string): A unique identifier for the condition group.
$conditions (array): An array of SQL conditions with dynamic placeholders.

Example:

$query->filterAnyOneRequired('CategoryOrId', [
    'p.category_id = {request.categoryId}',
    'p.id = {request.productId}'
]);

Key Feature Highlight: This method ensures that queries remain restrictive and secure by requiring at least one critical filter to be applied, avoiding potential data leaks.

Real-World Use: Prevents data leaks by ensuring restrictive conditions are always applied, especially in multi-tenant systems.

Try It:

Endpoint:

6. `group($fields)`

Purpose: Specifies the GROUP BY clause.

Parameters:

$fields (string): The SQL fields for grouping.

Example:

$query->group('p.id');

Real-World Use: Useful for aggregate queries like counting records or summarizing data.

7. `order($field, $direction)`

Purpose: Adds ORDER BY clauses to the query.

Parameters:

$field (string): The field to sort by.
$direction (string): Sort direction (ASC or DESC).

Example:

$query->order('{request.sort}', '{request.order}');

Real-World Use: Enables dynamic sorting based on user preferences.

Try It:

Endpoint:

8. `union($queryObj1, $queryObj2, ...)`

Purpose: Combines multiple query objects into a single result set.

Parameters:

$queryObj (Query): Accepts multiple Query objects for merging.

Example:

$query = Query('ProductSummary');
$query->union($electronicsQueryObj, $furnitureQueryObj);

Real-World Use:

Simplifies complex queries by splitting them into manageable parts.
Enhances reusability and modularity for separate query objects.

Dynamic Parameters

Syntax: {request.param}

Purpose: Allows secure and dynamic parameter substitution at runtime.

Behavior:

Mandatory Parameters: Used in filter and must be present in the request.
Optional Parameters: Used in filterOptional, applied only when provided.
Grouped Conditions: Used in filterAnyOneRequired, ensuring at least one condition is applied.

Example:

$query->filter('p.category_id = {request.categoryId}');
$query->filterOptional('p.price BETWEEN {request.minPrice} AND {request.maxPrice}');
$query->filterAnyOneRequired('GroupName', [
    'p.category_id = {request.categoryId}',
    'p.id = {request.productId}'
]);

Real-World Use: Prevents SQL injection and ensures clean, dynamic queries based on user input.

Scalability of the Query Builder

This class is designed to scale seamlessly for growing application needs, particularly in scenarios with increasing complexity or data volume. Here’s how it achieves scalability:

1. Separation of Concerns

Modular query logic reduces code duplication.
Independent components can extend base queries without interference.

2. Handling Large Data Sets

Efficient SQL generation ensures performance even with large datasets.
Union queries enable merging results from multiple datasets.

3. Dynamic Adaptability

Flexible parameter handling enables queries to adapt to varied user requirements.

4. Performance Optimization

Valid conditions (filterOptional, filterAnyOneRequired) reduce unnecessary processing.

5. Integration Across Applications

Generates consistent SQL for APIs and backend logic.
Facilitates integration with caching mechanisms for faster repeated queries.

Performance Tips

Use filterOptional sparingly for better query optimization.
Combine union with caching mechanisms for repeated data queries.
Structure grouped queries with meaningful identifiers for readability and maintenance.

This documentation provides a comprehensive understanding of the Query class, showcasing