Kotchasan\QueryBuilder\SelectBuilder

SelectBuilder creates SELECT queries with a fluent interface.

Usage

use Kotchasan\Database;

$db = Database::create();

// Basic SELECT
$users = $db->select('*')
    ->from('users')
    ->fetchAll();

// SELECT with conditions
$users = $db->select('id', 'name', 'email')
    ->from('users')
    ->where(['status', 'active'])
    ->orderBy('name')
    ->limit(10)
    ->fetchAll();

Main Methods

select()

->select('*')                    // all columns
->select('id', 'name', 'email')  // specific columns
->select(['id', 'name'])         // array format

from()

->from('users')
->from('users U')

distinct()

->distinct()  // SELECT DISTINCT

where() / orWhere()

->where(['status', 'active'])
->where(['age', '>', 18])
->orWhere(['role', 'admin'])

join() / leftJoin() / rightJoin()

->join('orders O', 'users.id = O.user_id')
->leftJoin('profiles P', 'users.id = P.user_id')
->rightJoin('addresses A', 'users.id = A.user_id')

groupBy() / having()

->groupBy('category')
->having('COUNT(*)', '>', 5)

orderBy()

->orderBy('name')
->orderBy('created_at', 'DESC')

limit()

->limit(10)
->limit(10, 20)

Examples

Complex Query

use Kotchasan\Database;

$products = $db->select('p.id', 'p.name', 'c.name AS category', 'COUNT(o.id) AS order_count')
    ->from('products p')
    ->leftJoin('categories c', 'p.category_id = c.id')
    ->leftJoin('order_items o', 'p.id = o.product_id')
    ->where(['p.status', 'active'])
    ->groupBy('p.id')
    ->having('order_count', '>', 10)
    ->orderBy('order_count', 'DESC')
    ->limit(20)
    ->fetchAll();

Subquery in SELECT

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

$subquery = $db->select('user_id', [Sql::SUM('amount'), 'total'])
    ->from('orders')
    ->groupBy('user_id');

$users = $db->select('u.name', [$subquery, 'total'])
    ->from('users u')
    ->fetchAll();

Generating SQL (Debugging)

// Build SQL without executing
$conn = new \Kotchasan\Connection\Connection(new \Kotchasan\Connection\MySQLDriver());
$qb = new \Kotchasan\QueryBuilder\SelectBuilder($conn);
$sql = $qb->select('*')
    ->from('users')
    ->where(['status', 1])
    ->toSql();

// Expected: SELECT * FROM `users` WHERE (`status` = :qb_p0)

Gotchas / Common Mistakes

• Avoid passing user input into raw SQL
• Use parameter binding instead of string concatenation
• Use Sql::column() when you need a column reference

Related Classes

• QueryBuilder Overview
• InsertBuilder
• UpdateBuilder
• DeleteBuilder