Kotchasan Framework Documentation

Kotchasan Framework Documentation

QueryBuilder - SQL Query Builder

EN 05 Feb 2026 07:21

QueryBuilder - SQL Query Builder

Overview

QueryBuilder is an object-oriented SQL query building system in the Kotchasan Framework. It helps developers create complex SQL safely and easily without writing raw SQL directly.

Key Features

  • Type Safety: Automatic SQL injection prevention with named parameters
  • Database Agnostic: Supports multiple database types (MySQL, PostgreSQL, SQLite, SQL Server)
  • Method Chaining: Fluent interface for readable code
  • Subquery Support: Complex subqueries and nested queries
  • Raw SQL Integration: Combine raw SQL with Query Builder when needed
  • Automatic Parameter Binding: Handles parameter binding automatically
  • Query Optimization: Improves SQL performance patterns

Table of Contents

  1. Installation and Basic Usage
  2. SelectBuilder - Creating SELECT Queries
  3. InsertBuilder - Creating INSERT Queries
  4. UpdateBuilder - Creating UPDATE Queries
  5. DeleteBuilder - Creating DELETE Queries
  6. Advanced Features
  7. Best Practices - Guidelines

Installation and Basic Usage

Creating QueryBuilder Instances

use Kotchasan\Database;
use Kotchasan\Database\Sql;
use Kotchasan\QueryBuilder\SelectBuilder;
use Kotchasan\QueryBuilder\InsertBuilder;
use Kotchasan\QueryBuilder\UpdateBuilder;
use Kotchasan\QueryBuilder\DeleteBuilder;

// Method 1: Create via Kotchasan\Database (recommended)
$db = Kotchasan\Database::create();
$selectQuery = $db->select(['id', 'name', 'email']);
$insertQuery = $db->insert('users');
$updateQuery = $db->update('users');
$deleteQuery = $db->delete('users');

// Method 2: Create directly
$connection = Kotchasan\Database::getConnection();
$selectQuery = new SelectBuilder($connection);
$insertQuery = new InsertBuilder($connection);

Basic Usage

use Kotchasan\Database;
// Basic SELECT
$users = Kotchasan\Database::create()
    ->select(['id', 'name', 'email'])
    ->from('user')
    ->where(['status', 'active'])
    ->fetchAll();

// Basic INSERT
$userId = Kotchasan\Database::create()
    ->insert('user')
    ->values([
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->execute();

// Basic UPDATE
$affected = Kotchasan\Database::create()
    ->update('user')
    ->set(['last_login' => date('Y-m-d H:i:s')])
    ->where(['id', 123])
    ->execute();

// Basic DELETE
$deleted = Kotchasan\Database::create()
    ->delete('user')
    ->where(['status', 'inactive'])
    ->where(['last_login', '<', '2023-01-01'])
    ->execute();

SelectBuilder - Creating SELECT Queries

SelectBuilder is a class for building complex SELECT queries. It supports JOINs, subqueries, grouping, and aggregation functions.

Core SelectBuilder Methods

select($columns = '*')

Specify columns to select

use Kotchasan\Database;
// Single column
$query->select('name');

// Multiple columns
$query->select(['id', 'name', 'email']);

// Select all
$query->select('*');

// Select with aliases
$query->select([
    'id',
    'name',
    ['email', 'user_email'],
    ['COUNT(*)', 'total']
]);

// Select with a subquery
$subquery = Kotchasan\Database::create()
    ->select(Sql::GROUP_CONCAT('D.value', null, ',', true))
    ->from('user_meta D')
    ->where(['D.member_id', '=', Sql::column('U.id')])
    ->where(['D.name', '=', 'department']);

$query->select(['U.*', [$subquery, 'department']]);

from(string $table, ?string $alias = null)

Specify the main table

// Single table
$query->from('user');

// Table with alias
$query->from('user', 'U');

// Or shorthand
$query->from('user U');

where($condition, string $operator = 'AND')

Add WHERE conditions

// Basic conditions
$query->where(['status', 'active']);
$query->where(['age', '>', 18]);
$query->where(['name', 'LIKE', 'John%']);

// Multiple conditions
$query->where(['status', 'active'])
      ->where(['age', '>', 18]);

// IN clause
$query->where(['id', [1, 2, 3, 4, 5]]);

// Column to column comparison
$query->where(['user_id', '=', Sql::column('profile.user_id')]);

// Raw SQL condition
$query->whereRaw('FIND_IN_SET(?, tags)', 'AND', [5]);

// Nested conditions
$query->where(function ($q) {
    $q->where(['status', 'active'])
      ->orWhere(['status', 'pending']);
})->where(['age', '>', 18]);

orWhere($condition)

Add OR WHERE conditions

$query->where(['status', 'active'])
      ->orWhere(['status', 'pending']);

join($table, $condition, string $type = 'INNER')

Add JOINs

// INNER JOIN
$query->join('profile P', 'U.id = P.user_id');

// LEFT JOIN
$query->leftJoin('profile P', 'U.id = P.user_id');

// RIGHT JOIN
$query->rightJoin('department D', 'U.department_id = D.id');

// Multiple JOINs
$query->from('user U')
      ->leftJoin('profile P', 'U.id = P.user_id')
      ->leftJoin('department D', 'U.department_id = D.id')
      ->where(['U.status', 'active']);

groupBy($columns)

Group data

// Group by single column
$query->groupBy('department_id');

// Group by multiple columns
$query->groupBy(['department_id', 'status']);

having($condition, string $operator = 'AND')

HAVING condition for GROUP BY

$query->select(['department_id', 'COUNT(*) as total'])
      ->groupBy('department_id')
      ->having('COUNT(*)', '>', 5);

orderBy($column, string $direction = 'ASC')

Order result set

// Single order
$query->orderBy('name', 'ASC');

// Multiple columns
$query->orderBy('department_id', 'ASC')
      ->orderBy('name', 'DESC');

// Order with raw SQL
$query->orderBy('FIELD(status, "active", "pending", "inactive")');

limit(int $limit, int $offset = 0)

Limit number of results

// Limit 10 rows
$query->limit(10);

// Limit 10 rows starting at offset 20
$query->limit(10, 20);

// Pagination
$page = 2;
$perPage = 1; // 5;
$query->limit($perPage, ($page - 1) * $perPage);

SelectBuilder Examples

Example 1: Basic Query

use Kotchasan\Database;

// Fetch active users
$activeUsers = Kotchasan\Database::create()
    ->select(['id', 'name', 'email', 'created_at'])
    ->from('user')
    ->where(['status', 'active'])
    ->orderBy('name', 'ASC')
    ->fetchAll();

// Fetch a single user
$user = Kotchasan\Database::create()
    ->select('*')
    ->from('user')
    ->where(['id', 123])
    ->first();

Example 2: JOIN tables

use Kotchasan\Database;
// Fetch users with profile and department
$usersWithProfile = Kotchasan\Database::create()
    ->select([
        'U.id',
        'U.name',
        'U.email',
        'P.phone',
        'P.address',
        'D.name as department_name'
    ])
    ->from('user U')
    ->leftJoin('profile P', 'U.id = P.user_id')
    ->leftJoin('department D', 'U.department_id = D.id')
    ->where(['U.status', 'active'])
    ->orderBy('U.name')
    ->fetchAll();

Example 3: Using Subquery

use Kotchasan\Database;
use Kotchasan\Database\Sql;

// Build subquery to fetch metadata
$metaSubquery = Kotchasan\Database::create()
    ->select(Sql::GROUP_CONCAT('D.value', null, ',', true))
    ->from('user_meta D')
    ->where(['D.member_id', '=', Sql::column('U.id')])
    ->where(['D.name', '=', 'skills']);

// Main query using subquery
$usersWithSkills = Kotchasan\Database::create()
    ->select([
        'U.id',
        'U.name',
        'U.email',
        [$metaSubquery, 'skills']
    ])
    ->from('user U')
    ->where(['U.status', 'active'])
    ->orderBy('U.name')
    ->fetchAll();

Example 4: GROUP BY and HAVING

use Kotchasan\Database;
// User stats by department
$departmentStats = Kotchasan\Database::create()
    ->select([
        'D.name as department_name',
        'COUNT(U.id) as user_count',
        'AVG(U.salary) as avg_salary'
    ])
    ->from('user U')
    ->leftJoin('department D', 'U.department_id = D.id')
    ->where(['U.status', 'active'])
    ->groupBy('D.id')
    ->having('COUNT(U.id)', '>', 5)
    ->orderBy('user_count', 'DESC')
    ->fetchAll();

Example 5: Complex WHERE

use Kotchasan\Database;
// Search users with complex conditions
$complexSearch = Kotchasan\Database::create()
    ->select(['id', 'name', 'email', 'department_id'])
    ->from('user')
    ->where(function ($q) {
        $q->where(['status', 'active'])
          ->orWhere(['status', 'pending']);
    })
    ->where(['age', '>=', 18])
    ->where(['department_id', [1, 2, 3, 5]])
    ->where(['created_at', '>=', '2023-01-01'])
    ->orderBy('created_at', 'DESC')
    ->limit(50)
    ->fetchAll();
public function limit(int $limit, int $offset = 0): QueryBuilderInterface;
public function toSql(): string;
public function execute(?array $params = null): ResultInterface;

}


### 2. QueryBuilder (Abstract Class)

Base class implementing QueryBuilderInterface with shared functionality

```php
abstract class QueryBuilder implements QueryBuilderInterface
{
    protected ConnectionInterface $connection;
    protected string $table = '';
    protected ?string $alias = null;
    protected array $columns = [];
    protected array $wheres = [];
    protected array $joins = [];
    protected array $orders = [];
    protected array $namedBindings = [];
}

Using SELECT Queries

SelectBuilder

Class for building SELECT queries

use Kotchasan\Database;

// Basic SELECT
$query = Kotchasan\Database::createQuery()
    ->select(['id', 'name', 'email'])
    ->from('user U')
    ->where(['U.status', 'active'])
    ->orderBy('U.name', 'ASC')
    ->limit(10);

$users = $query->fetchAll();

Selecting Columns

// Select specific columns
$query->select(['id', 'name', 'email']);

// Select all
$query->select('*');

// Select with alias
$query->select(['U.name AS username', 'U.email AS user_email']);

// Select with SQL function
$query->select(['COUNT(*) AS total', 'MAX(created_at) AS latest']);

Using DISTINCT

use Kotchasan\Database;
$query = Kotchasan\Database::createQuery()
    ->select(['category'])
    ->distinct()
    ->from('product');

Using Subquery in Columns

use Kotchasan\Database;
// Subquery as a column
$subquery = Kotchasan\Database::createQuery()
    ->select('COUNT(*)')
    ->from('order O')
    ->where(['O.user_id', Sql::column('U.id')]);

$users = Kotchasan\Database::createQuery()
    ->select(['U.', [$subquery, 'order_count']])
    ->from('user U')
    ->fetchAll();

Using WHERE Conditions

Basic WHERE

// Single condition
$query->where(['status', 'active']);

// Multiple conditions
$query->where(['status', 'active'])
      ->where(['age', '>', 18])
      ->where(['city', 'Bangkok']);

WHERE Formats

// Format [column, value] - default operator is =
$query->where(['username', 'admin']);

// Format [column, operator, value]
$query->where(['age', '>', 18]);
$query->where(['status', '!=', 'inactive']);

// NULL checking
$query->where(['deleted_at', null]); // IS NULL
$query->where(['deleted_at', '!=', null]); // IS NOT NULL

// IN clause
$query->where(['id', [1, 2, 3, 4]]);
$query->where(['status', ['active', 'pending']]);

// NOT IN clause
$query->where(['id', '!=', [1, 2, 3]]);

OR WHERE

$query->where(['status', 'active'])
      ->orWhere(['status', 'pending']);

Nested WHERE (condition groups)

$query->where(function($q) {
    $q->where(['status', 'active'])
      ->orWhere(['status', 'pending']);
})->where(['age', '>', 18]);

// Expected: WHERE ((status = 'active' OR status = 'pending') AND age > 18)

Raw WHERE

// Use raw SQL
$query->whereRaw('FIND_IN_SET(?, tags)', 'AND', [5]);
$query->where('DATE(created_at) = CURDATE()');

Column-to-Column Comparison

use Kotchasan\Database\Sql;

$query->where(['U.created_at', '>', Sql::column('U.updated_at')]);
$query->where(['A.user_id', '=', Sql::column('B.id')]);

Using JOINs

INNER JOIN

use Kotchasan\Database;
$query = Kotchasan\Database::createQuery()
    ->select(['U.name', 'P.title'])
    ->from('user U')
    ->join('post P', 'U.id = P.user_id')
    ->fetchAll();

LEFT JOIN

use Kotchasan\Database;
$query = Kotchasan\Database::createQuery()
    ->select(['U.name', 'P.title'])
    ->from('user U')
    ->leftJoin('post P', 'U.id = P.user_id');

RIGHT JOIN

use Kotchasan\Database;
$query = Kotchasan\Database::createQuery()
    ->select(['U.name', 'P.title'])
    ->from('user U')
    ->rightJoin('post P', 'U.id = P.user_id');

JOIN with multiple conditions

// JOIN with array conditions
$query->join('post P', [
    ['U.id', 'P.user_id'],
    ['P.status', 'published'],
    ['P.created_at', '>', '2024-01-01']
]);

// JOIN with closure
$query->join('post P', function($join) {
    $join->where(['U.id', 'P.user_id'])
         ->where(['P.status', 'published']);
});

Using ORDER BY, GROUP BY, HAVING

ORDER BY

// Single order
$query->orderBy('name', 'ASC');
$query->orderBy('created_at', 'DESC');

// Multiple columns
$query->orderBy('status', 'ASC')
      ->orderBy('name', 'ASC');

GROUP BY

$query->select(['category', 'COUNT(*) as count'])
      ->from('product')
      ->groupBy('category');

// GROUP BY multiple columns
$query->groupBy(['category', 'status']);

HAVING

$query->select(['category', 'COUNT(*) as count'])
      ->from('product')
      ->groupBy('category')
      ->having('COUNT(*)', '>', 5);

Using LIMIT and OFFSET

// LIMIT only
$query->limit(10);

// LIMIT and OFFSET
$query->limit(10, 20); // LIMIT 10 OFFSET 20

// Pagination
$page = 2;
$perPage = 1; // 0;
$offset = ($page - 1) * $perPage;
$query->limit($perPage, $offset);

Execution and Fetching

Fetch All

// Fetch as array
$results = $query->fetchAll(true);

// Fetch as object
$results = $query->fetchAll(false);

Fetch One Row

// First row as array
$user = $query->first(true);

// First row as object
$user = $query->first(false);

// Check if data exists
if ($user) {
    echo $user['name']; // or $user->name
}

Counting

use Kotchasan\Database;
$count = Kotchasan\Database::createQuery()
    ->selectCount()
    ->from('user')
    ->where(['status', 'active'])
    ->first()['count'];

Custom Execute

$result = $query->execute();
while ($row = $result->fetch()) {
    // Process each row
}

Using Subqueries

Subquery in WHERE

use Kotchasan\Database;
$subquery = Kotchasan\Database::createQuery()
    ->select('user_id')
    ->from('order')
    ->where(['status', 'completed']);

$users = Kotchasan\Database::createQuery()
    ->select('*')
    ->from('user')
    ->where(['id', 'IN', $subquery])
    ->fetchAll();

Subquery in SELECT

use Kotchasan\Database;
$orderCount = Kotchasan\Database::createQuery()
    ->select('COUNT(*)')
    ->from('order O')
    ->where(['O.user_id', Sql::column('U.id')]);

$users = Kotchasan\Database::createQuery()
    ->select(['U.', [$orderCount, 'order_count']])
    ->from('user U')
    ->fetchAll();

Subquery in FROM

use Kotchasan\Database;
$subquery = Kotchasan\Database::createQuery()
    ->select(['user_id', 'COUNT(*) as order_count'])
    ->from('order')
    ->groupBy('user_id');

$result = Kotchasan\Database::createQuery()
    ->select(['U.name', 'S.order_count'])
    ->from([$subquery, 'S'])
    ->leftJoin('user U', 'U.id = S.user_id')
    ->fetchAll();

Using SQL Functions

Basic SQL Functions

use Kotchasan\Database\Sql;

// Date Functions
$query->select([
    Sql::YEAR('created_at'),
    Sql::MONTH('created_at'),
    Sql::DATE('created_at')
]);

// String Functions
$query->select([
    Sql::CONCAT('first_name', ' ', 'last_name'),
    Sql::UPPER('name'),
    Sql::LOWER('email')
]);

// Aggregate Functions
$query->select([
    Sql::COUNT('*'),
    Sql::SUM('amount'),
    Sql::AVG('score'),
    Sql::MAX('created_at'),
    Sql::MIN('created_at')
]);

GROUP_CONCAT

$query->select([
    'category',
    Sql::GROUP_CONCAT('name', null, ',', true)
])
->from('product')
->groupBy('category');

Using INSERT Queries

InsertBuilder

use Kotchasan\Database;

// Single-row INSERT
// Single-row INSERT
$db = Kotchasan\Database::create();
$db->insert('user')
    ->values([
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'status' => 'active'
    ])
    ->execute();

$newId = $db->lastInsertId();

INSERT IGNORE

use Kotchasan\Database;
$result = Kotchasan\Database::createQuery()
    ->insert('user')
    ->ignore()
    ->values([
        'email' => 'john@example.com',
        'name' => 'John Doe'
    ])
    ->execute();

Batch INSERT

use Kotchasan\Database;
$users = [
    ['name' => 'John', 'email' => 'john@example.com'],
    ['name' => 'Jane', 'email' => 'jane@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com']
];

$result = Kotchasan\Database::createQuery()
    ->insert('user')
    ->rows($users)
    ->execute();

Using UPDATE Queries

UpdateBuilder

use Kotchasan\Database;
// Basic UPDATE
$result = Kotchasan\Database::createQuery()
    ->update('user')
    ->set([
        'name' => 'John Smith',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->where(['id', 1])
    ->execute();

$affectedRows = $result->rowCount();

UPDATE with complex conditions

use Kotchasan\Database;
$result = Kotchasan\Database::createQuery()
    ->update('user')
    ->set(['status' => 'inactive'])
    ->where(['last_login', '<', '2024-01-01'])
    ->where(['status', 'active'])
    ->limit(100)
    ->execute();

UPDATE with JOIN

use Kotchasan\Database;
$result = Kotchasan\Database::createQuery()
    ->update('user U')
    ->join('profile P', 'U.id = P.user_id')
    ->set(['U.status' => 'verified'])
    ->where(['P.verified', 1])
    ->execute();

Using DELETE Queries

DeleteBuilder

use Kotchasan\Database;
// Basic DELETE
$result = Kotchasan\Database::createQuery()
    ->delete('user')
    ->where(['status', 'inactive'])
    ->execute();

$deletedRows = $result->rowCount();

DELETE with complex conditions

use Kotchasan\Database;
$result = Kotchasan\Database::createQuery()
    ->delete('user')
    ->where(['created_at', '<', '2023-01-01'])
    ->where(['status', 'inactive'])
    ->limit(1000)
    ->execute();

DELETE with subquery

use Kotchasan\Database;
$inactiveUsers = Kotchasan\Database::createQuery()
    ->select('id')
    ->from('user')
    ->where(['last_login', '<', '2023-01-01']);

$result = Kotchasan\Database::createQuery()
    ->delete('user_log')
    ->where(['user_id', 'IN', $inactiveUsers])
    ->execute();

Using Caching

Enable Cache

use Kotchasan\Database;
// Cache with default TTL (3600 seconds)
$users = Kotchasan\Database::createQuery()
    ->select('*')
    ->from('user')
    ->cacheOn()
    ->fetchAll();

// Cache with custom TTL
$users = Kotchasan\Database::createQuery()
    ->select('*')
    ->from('user')
    ->cacheOn(true, 7200) // 2 hours
    ->fetchAll();

Disable Cache

use Kotchasan\Database;
$users = Kotchasan\Database::createQuery()
    ->select('*')
    ->from('user')
    ->cacheOff()
    ->fetchAll();

Manual Cache Saving

use Kotchasan\Database;
$query = Kotchasan\Database::createQuery()
    ->select('*')
    ->from('user')
    ->cacheOn(false); // do not auto-save

$users = $query->fetchAll();

// Save cache manually
$query->saveCache($users);

Debugging and Troubleshooting

Viewing Generated SQL

use Kotchasan\Database;
$query = Kotchasan\Database::createQuery()
    ->select('*')
    ->from('user')
    ->where(['status', 'active']);

// Print SQL
echo $query->debug(true);

// or
$sql = $query->toSql();
echo $sql;

Viewing Parameter Bindings

$bindings = $query->getBindings();
var_dump($bindings);

Using EXPLAIN

use Kotchasan\Database;
$result = Kotchasan\Database::createQuery()
    ->select('*')
    ->from('user')
    ->where(['status', 'active'])
    ->explain()
    ->execute();

Best Practices - Guidelines

1. Use Named Parameters

// Good - use array format
$query->where(['status', $userStatus]);

// Avoid - raw SQL
$query->where("status = '$userStatus'"); // dangerous!

2. Use aliases for long table names

use Kotchasan\Database;
$query = Kotchasan\Database::createQuery()
    ->select(['U.name', 'UP.bio'])
    ->from('user U')
    ->leftJoin('user_profile UP', 'U.id = UP.user_id');

3. Use method chaining

use Kotchasan\Database;
$users = Kotchasan\Database::createQuery()
    ->select(['id', 'name', 'email'])
    ->from('user')
    ->where(['status', 'active'])
    ->orderBy('name')
    ->limit(10)
    ->fetchAll();

4. Handle errors

use Kotchasan\Database;
try {
    $result = Kotchasan\Database::createQuery()
        ->insert('user')
        ->values($userData)
        ->execute();
} catch (Kotchasan\DatabaseException $e) {
    // handle error
    error_log($e->getMessage());
}

5. Use transactions for multi-table changes

use Kotchasan\Database;
$db = Kotchasan\Database::getConnection();
$db->beginTransaction();

try {
    // INSERT user
    $userId = Kotchasan\Database::createQuery()
        ->insert('user')
        ->values($userData)
        ->execute()
        ->lastInsertId();

    // INSERT profile
    Kotchasan\Database::createQuery()
        ->insert('user_profile')
        ->values(['user_id' => $userId] + $profileData)
        ->execute();

    $db->commit();
} catch (Exception $e) {
    $db->rollback();
    throw $e;
}

Real-world Examples

1. User Management System

use Kotchasan\Database;
class UserRepository
{
    public function findActiveUsers(int $limit = 10): array
    {
        return Kotchasan\Database::createQuery()
            ->select(['id', 'name', 'email', 'created_at'])
            ->from('user')
            ->where(['status', 'active'])
            ->where(['deleted_at', null])
            ->orderBy('created_at', 'DESC')
            ->limit($limit)
            ->fetchAll(true);
    }

    public function getUserWithProfile(int $userId): ?array
    {
        return Kotchasan\Database::createQuery()
            ->select(['U.*', 'UP.bio', 'UP.avatar'])
            ->from('user U')
            ->leftJoin('user_profile UP', 'U.id = UP.user_id')
            ->where(['U.id', $userId])
            ->first(true);
    }

    public function searchUsers(string $keyword): array
    {
        return Kotchasan\Database::createQuery()
            ->select(['id', 'name', 'email'])
            ->from('user')
            ->where(function($q) use ($keyword) {
                $q->where(['name', 'LIKE', "%$keyword%"])
                  ->orWhere(['email', 'LIKE', "%$keyword%"]);
            })
            ->where(['status', 'active'])
            ->orderBy('name')
            ->fetchAll(true);
    }
}

2. Reporting System

use Kotchasan\Database;
class ReportService
{
    public function getUserStats(): array
    {
        return Kotchasan\Database::createQuery()
            ->select([
                'status',
                'COUNT(*) as count',
                'AVG(DATEDIFF(NOW(), created_at)) as avg_days'
            ])
            ->from('user')
            ->groupBy('status')
            ->orderBy('count', 'DESC')
            ->fetchAll(true);
    }

    public function getMonthlyRegistrations(int $year): array
    {
        return Kotchasan\Database::createQuery()
            ->select([
                Sql::MONTH('created_at'),
                'COUNT(*) as registrations'
            ])
            ->from('user')
            ->where([Sql::YEAR('created_at'), $year])
            ->groupBy(Sql::MONTH('created_at'))
            ->orderBy(Sql::MONTH('created_at'))
            ->fetchAll(true);
    }
}

3. E-commerce System

use Kotchasan\Database;
class OrderRepository
{
    public function getOrdersWithItems(int $userId): array
    {
        $orderItems = Kotchasan\Database::createQuery()
            ->select([
                'OI.order_id',
                Sql::GROUP_CONCAT('P.name', null, ', ', true)
            ])
            ->from('order_item OI')
            ->join('product P', 'OI.product_id = P.id')
            ->groupBy('OI.order_id');

        return Kotchasan\Database::createQuery()
            ->select(['O.', [$orderItems, 'items']])
            ->from('order O')
            ->where(['O.user_id', $userId])
            ->orderBy('O.created_at', 'DESC')
            ->fetchAll(true);
    }

    public function getTopSellingProducts(int $limit = 10): array
    {
        return Kotchasan\Database::createQuery()
            ->select([
                'P.name',
                'SUM(OI.quantity) as total_sold',
                'SUM(OI.quantity  OI.price) as total_revenue'
            ])
            ->from('product P')
            ->join('order_item OI', 'P.id = OI.product_id')
            ->join('order O', 'OI.order_id = O.id')
            ->where(['O.status', 'completed'])
            ->groupBy('P.id')
            ->orderBy('total_sold', 'DESC')
            ->limit($limit)
            ->fetchAll(true);
    }
}

Common Troubleshooting

1. SQL Injection Prevention

QueryBuilder prevents SQL injection automatically when using parameter binding:

// Safe - use parameter binding
$query->where(['name', $userInput]);

// Unsafe - raw SQL
$query->where("name = '$userInput'");

2. Performance Optimization

// Use appropriate indexes
$query->where(['status', 'active']) // if status has an index
      ->where(['created_at', '>', $date]); // if created_at has an index

// Use LIMIT when you don't need all rows
$query->limit(100);

// Use cache for infrequently changing data
$query->cacheOn(true, 3600);

3. Memory Management

// For large datasets, use a cursor
$result = $query->execute();
while ($row = $result->fetch()) {
    // process each row
    processRow($row);
}
$result->close();

InsertBuilder - Creating INSERT Queries

InsertBuilder builds INSERT queries and supports both single-row and batch inserts.

Core InsertBuilder Methods

insert(string $table)

Specify the target table for INSERT

use Kotchasan\Database;
$query = Kotchasan\Database::create()->insert('user');

values(array $data)

Provide data for a single-row INSERT

$query->values([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'status' => 'active',
    'created_at' => date('Y-m-d H:i:s')
]);

rows(array $rows)

Provide multiple rows for batch insert

$query->rows([
    [
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'status' => 'active'
    ],
    [
        'name' => 'Jane Smith',
        'email' => 'jane@example.com',
        'status' => 'pending'
    ]
]);

ignore()

Use INSERT IGNORE to skip duplicate rows

$query->ignore()->values([
    'email' => 'existing@example.com',
    'name' => 'Duplicate User'
]);

InsertBuilder Examples

Insert a single row

use Kotchasan\Database;
$userId = Kotchasan\Database::create()
    ->insert('user')
    ->values([
        'name' => 'John Doe',
        'email' => 'john.doe@example.com',
        'password' => password_hash('mypassword', PASSWORD_DEFAULT),
        'status' => 'active',
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->execute();

Insert multiple rows

use Kotchasan\Database;
$result = Kotchasan\Database::create()
    ->insert('user')
    ->rows([
        [
            'name' => 'Alice Johnson',
            'email' => 'alice@example.com',
            'status' => 'active'
        ],
        [
            'name' => 'Bob Smith',
            'email' => 'bob@example.com',
            'status' => 'pending'
        ]
    ])
    ->execute();

UpdateBuilder - Creating UPDATE Queries

UpdateBuilder builds UPDATE queries safely and efficiently.

Core UpdateBuilder Methods

update(string $table)

Specify the table to UPDATE

use Kotchasan\Database;
$query = Kotchasan\Database::create()->update('user');

set(array $data)

Define the data to update

$query->set([
    'name' => 'Updated Name',
    'updated_at' => date('Y-m-d H:i:s')
]);

where($condition)

Add WHERE conditions (required for safety)

$query->where(['id', 123]);

UpdateBuilder Examples

Basic Update

use Kotchasan\Database;
$affected = Kotchasan\Database::create()
    ->update('user')
    ->set([
        'name' => 'John Updated',
        'email' => 'john.updated@example.com',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->where(['id', 123])
    ->execute();

Update with Calculations

use Kotchasan\Database;
use Kotchasan\Database\Sql;

$affected = Kotchasan\Database::create()
    ->update('user')
    ->set([
        'login_count' => Sql::raw('login_count + 1'),
        'last_login' => date('Y-m-d H:i:s')
    ])
    ->where(['id', 123])
    ->execute();

DeleteBuilder - Creating DELETE Queries

DeleteBuilder is a class for building DELETE queries safely.

Core DeleteBuilder Methods

delete(string $table)

Specify the table to DELETE

use Kotchasan\Database;
$query = Kotchasan\Database::create()->delete('user');

where($condition)

Add WHERE conditions (required for safety)

$query->where(['id', 123]);

DeleteBuilder Examples

Delete a Single Row

use Kotchasan\Database;
$deleted = Kotchasan\Database::create()
    ->delete('user')
    ->where(['id', 123])
    ->execute();

Delete by Condition

use Kotchasan\Database;
$deleted = Kotchasan\Database::create()
    ->delete('user')
    ->where(['status', 'inactive'])
    ->where(['updated_at', '<', date('Y-m-d', strtotime('-1 year'))])
    ->execute();

Advanced Features

Using Transactions

use Kotchasan\Database;
try {
    Kotchasan\Database::beginTransaction();

    // Create a new user
    $userId = Kotchasan\Database::create()
        ->insert('user')
        ->values(['name' => 'John', 'email' => 'john@example.com'])
        ->execute();

    // Create a profile
    Kotchasan\Database::create()
        ->insert('user_profile')
        ->values(['user_id' => $userId, 'phone' => '02-123-4567'])
        ->execute();

    Kotchasan\Database::commit();
} catch (Exception $e) {
    Kotchasan\Database::rollback();
    throw $e;
}

Using Raw SQL

use Kotchasan\Database\Sql;

// Raw expressions in SELECT
$query->select([
    'id',
    'name',
    Sql::raw('COUNT(*) as total'),
    Sql::raw('AVG(score) as average_score')
]);

// Raw expressions in WHERE
$query->where(Sql::raw('FIND_IN_SET(?, tags)'), [5]);

// Raw expressions in ORDER BY
$query->orderBy(Sql::raw('FIELD(status, "active", "pending", "inactive")'));

Using Subqueries

use Kotchasan\Database;
// Subquery in SELECT
$subquery = Kotchasan\Database::create()
    ->select('COUNT(*)')
    ->from('orders')
    ->where(['customer_id', '=', Sql::column('customers.id')]);

$customers = Kotchasan\Database::create()
    ->select(['customers.*', [$subquery, 'order_count']])
    ->from('customers')
    ->fetchAll();

// Subquery in WHERE
$activeUsers = Kotchasan\Database::create()
    ->select('id')
    ->from('users')
    ->where(['status', 'active']);

$orders = Kotchasan\Database::create()
    ->select('*')
    ->from('orders')
    ->where(['customer_id', 'IN', $activeUsers])
    ->fetchAll();

Best Practices

1. Security

  • Always use parameter binding instead of string concatenation.
  • Validate user input before passing it into QueryBuilder.
  • Use whitelists for column and table names.

2. Performance

  • Use limit() when you do not need all rows.
  • Select only required columns instead of using *.
  • Use appropriate indexes for WHERE conditions.

3. Error Handling

  • Use try-catch for critical operations.
  • Use transactions for multi-step operations.
  • Log errors for debugging.

4. Readable Code

  • Break long method chains into multiple lines.
  • Use variables for complex queries.
  • Add comments to explain complex conditions.
use Kotchasan\Database;
// Example of readable code
$userQuery = Kotchasan\Database::create()
    ->select([
        'U.id',
        'U.name',
        'U.email',
        'P.phone',
        'D.name as department'
    ])
    ->from('user U')
    ->leftJoin('profile P', 'U.id = P.user_id')
    ->leftJoin('department D', 'U.department_id = D.id')
    ->where(['U.status', 'active'])
    ->where(['U.created_at', '>=', '2023-01-01'])
    ->orderBy('U.name', 'ASC')
    ->limit(50);

$users = $userQuery->fetchAll();

QueryBuilder in the Kotchasan Framework is a powerful tool for database operations, helping keep code organized, secure, and easy to maintain.

Did you spot an improvement?

Enhance the documentation by contributing updates or translations.

Visit project repository