Build SQL Queries

The arrayToSql function in KConverter provides a flexible way to construct SQL queries from an array-based input. This function is designed to handle complex queries, including table joins, conditions, sorting, and aggregation. It can be used to generate SQL queries dynamically for execution in the Ketroute Framework.

Function Signature

public static function arrayToSql(array $core_data_source, array ...$join_data_source): string

Parameters

Core Data Source ($core_data_source)

The first parameter represents the main table and the query structure:

• Index 0: The parent table name.
• Index 1: An array of selected fields. Fields can be:

Simple field names: ['field1', 'field2']

Aggregated fields: ['field3' => 'SUM']

Aliased fields with functions: ['field4' => ['field_alias', 'UPPER']]

• Index 2: A filter condition array. Supports:

Direct equality: ['field' => 1]

OR conditions: ['OR' => ['field' => 520, 'field' => [2,5,9], 'field' => null]]

IGNORE (fields that should not match): ['IGNORE' => ['field' => 520]]

• Index 3 (Optional): Database type, e.g., 'mysql'.
• Index 4 (Optional): Limit results, either a single value (max rows) or an array [offset, max].
• Index 5 (Optional): Sorting rules: [['name', 'asc/desc']].

Join Data Source ($join_data_source)

Additional parameters define table joins, with each array structured as follows:

• Index 0: The join table name.
• Index 1: The join condition, specified as:

Simple join: [ ['jointable1_id', 'parent_table_id'] ]

Complex join: [ ['jointable1_id', 'parent_table_id', 'optional_joinindex'] ]

• Index 2: Conditions applied to the join table. Supports: OR, IGNORE, GREATERTHAN, LESSTHAN, BETWEEN, etc.

• Index 3: Fields to retrieve from the joined table.
• Index 4 (Optional): Join type (INNER by default, but can be LEFT, RIGHT, OUTER).
• Index 5 (Optional): Sorting rules for joined results.

Example Usage

Simple Query

$query = KConverter::arrayToSql([
    'users',
    ['id', 'name', ['created_at' => 'DATE']],
    ['status' => 'active'],
    'mysql',
    10,
    [['created_at', 'DESC']]
]);

Generated SQL

SELECT id, name, DATE(created_at) FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10;

Query with Joins

$query = KConverter::arrayToSql([
    'orders',
    ['id', 'total', ['created_at' => 'DATE']],
    ['status' => 'pending']
], [
    'customers',
    [['customer_id', 'orders.customer_id']],
    [],
    ['name', 'email'],
    'LEFT'
]);

Generated SQL

SELECT orders.id, orders.total, DATE(orders.created_at), customers.name, customers.email
FROM orders
LEFT JOIN customers ON customers.customer_id = orders.customer_id
WHERE orders.status = 'pending';

Executing the Query

Once the SQL query is generated, it can be executed using KetrouteApplication::db()->runSql($query)

$result = KetrouteApplication::db()->runSql($query);

Summary

• arrayToSql dynamically builds SQL queries from structured arrays.
• It supports filtering, joins, sorting, and aggregation.
• The resulting SQL can be executed using KetrouteApplication::db()->runSql().

This function is a powerful utility for generating queries dynamically without manually writing SQL statements.