🔨 Query

The QueryBuilder in Hector ORM provides a fluent, object-oriented API to construct and execute SQL queries. It is designed to streamline the building of SELECT, INSERT, UPDATE, DELETE, and UNION queries, while maintaining control and readability in your codebase.

🚀 Initialization

You can initialize the QueryBuilder using a Connection object. This setup allows you to immediately start crafting your queries.

use Hector\Connection\Connection;
use Hector\Query\QueryBuilder;

$connection = new Connection('...');
$queryBuilder = new QueryBuilder($connection);

$result = $queryBuilder
    ->select('table')
    ->where('field1', 'foo')
    ->where('field2', '>=', 2)
    ->fetchAll();

🧱 Query Types

The library provides specific classes for each query type. Each of these classes implements the StatementInterface, enabling you to manually build the query and bind parameters.

• Hector\Query\Select
• Hector\Query\Insert
• Hector\Query\Update
• Hector\Query\Delete
• Hector\Query\Union

📌 Specifying the Table

Use from() to define the target table for your query:

$queryBuilder->from('users');
// SELECT * FROM users

$queryBuilder->from('users', 'u');
// SELECT * FROM users AS u

// Multiple tables (implicit join)
$queryBuilder
    ->from('users', 'u')
    ->from('profiles', 'p')
    ->where('u.id', '=', 'p.user_id');

🧪 Example

use Hector\Connection\Bind\BindParamList;
use Hector\Connection\Connection;
use Hector\Query\Select;

$select = new Select();
$select
    ->from('table')
    ->where('field', 'value');

$binds = new BindParamList();
$statement = $select->getStatement($binds);

$connection = new Connection('...');
$result = $connection->fetchAll($statement, $binds);

🧮 Conditions

Both WHERE and HAVING clauses are supported using the same API. Simply switch the method prefix.

Where / Having

Two-argument form: column, value (implicit “=” operator):

use Hector\Query\QueryBuilder;

$queryBuilder
    ->from('table', 'alias')
    ->where('field', 'value');

Three-argument form (column, operator, value) :

use Hector\Query\QueryBuilder;

$queryBuilder
    ->from('table', 'alias')
    ->where('field', '=', 'value')
    ->orWhere('field', '>=', 10);

Grouped Conditions

You can pass a Closure to where() or having() to create grouped conditions wrapped in parentheses. The closure receives a Conditions object (Hector\Query\Statement\Conditions) as its first argument, which exposes the same where() / having() methods:

use Hector\Query\Statement\Conditions;

$queryBuilder
    ->from('users')
    ->where(function (Conditions $conditions): void {
        $conditions->where('age', '>=', 18);
        $conditions->orWhere('role', 'admin');
    })
    ->where('active', true);
// WHERE ( age >= ? OR role = ? ) AND active = ?

This is useful for combining OR conditions without affecting the rest of the query:

$queryBuilder
    ->from('products')
    ->where('in_stock', true)
    ->where(function (Conditions $conditions): void {
        $conditions->where('category', 'electronics');
        $conditions->orWhere('price', '<', 10);
    });
// WHERE in_stock = ? AND ( category = ? OR price < ? )

The same pattern works with orWhere(), having(), and orHaving():

$queryBuilder
    ->from('orders')
    ->column('customer_id')
    ->column('SUM(amount)', 'total')
    ->groupBy('customer_id')
    ->having(function (Conditions $conditions): void {
        $conditions->having('total', '>', 1000);
        $conditions->orHaving('total', '<', 100);
    });
// HAVING ( total > ? OR total < ? )

Condition Shortcuts

Several convenience methods are provided:

$queryBuilder->whereIn('id', [1, 2, 3]);
$queryBuilder->whereNotBetween('age', 18, 30);
$queryBuilder->whereGreaterThan('score', 50);
$queryBuilder->whereExists(new Select(...));
$queryBuilder->whereContains('name', 'john');

Full list:

• whereIn($column, array $values)
• whereNotIn($column, array $values)
• whereBetween($column, $value1, $value2)
• whereNotBetween($column, $value1, $value2)
• whereGreaterThan($column, $value)
• whereGreaterThanOrEqual($column, $value)
• whereLessThan($column, $value)
• whereLessThanOrEqual($column, $value)
• whereExists($statement)
• whereNotExists($statement)
• whereContains($column, $string)
• whereStartsWith($column, $string)
• whereEndsWith($column, $string)

All where* methods have their having* counterparts for filtering grouped results:

$queryBuilder
    ->from('orders')
    ->column('customer_id')
    ->column('SUM(amount)', 'total')
    ->groupBy('customer_id')
    ->having('total', '>', 1000)
    ->orHaving('total', '<', 100);

Available:

• having()
• orHaving()
• havingIn()
• havingNotIn()
• havingBetween()
• havingNotBetween(),
• havingGreaterThan()
• havingGreaterThanOrEqual()
• havingLessThan()
• havingLessThanOrEqual(),
• havingExists()
• havingNotExists()
• havingContains()
• havingStartsWith()
• havingEndsWith().

📋 Selecting Columns

You can customize the columns returned by your query:

$queryBuilder
    ->from('users')
    ->column('name')
    ->column('email', 'user_email');
// SELECT name, email AS user_email FROM users

$queryBuilder->resetColumns();
$queryBuilder->columns(['id', 'name', 'created_at']);
// SELECT id, name, created_at FROM users

🧑‍🤝‍🧑 Grouping Results

To group query results:

$queryBuilder
    ->from('products')
    ->column('category_id')
    ->column('COUNT(*)', 'count')
    ->groupBy('category_id')
    ->groupBy('status');
// SELECT category_id, COUNT(*) AS count FROM products GROUP BY category_id, status

$queryBuilder->resetGroups();
$queryBuilder->groupByWithRollup();
// Adds WITH ROLLUP modifier (MySQL)

🔢 Ordering Results

Sort your result set with orderBy():

$queryBuilder
    ->from('posts')
    ->orderBy('created_at', 'DESC')
    ->orderBy('name', 'ASC');
// SELECT * FROM posts ORDER BY created_at DESC, name ASC

$queryBuilder->resetOrder();
$queryBuilder->random();
// SELECT * FROM posts ORDER BY RAND()

🔀 Sorting

The Sort namespace provides type-safe, composable sorting objects. This is especially useful when sort parameters come from user input (e.g. ?sort=title:desc) and need to be validated before being applied to a query.

Sort Objects

The SortInterface defines a single method: apply(QueryBuilder $builder): void. Two implementations are provided:

use Hector\Query\Sort\Sort;
use Hector\Query\Sort\MultiSort;

// Single sort
$sort = new Sort('title', 'ASC');
$sort->apply($builder);

// Multiple sorts
$sort = new MultiSort(
    new Sort('title', 'ASC'),
    new Sort('id', 'DESC'),
);
$sort->apply($builder);

The QueryBuilder also provides a fluent shortcut:

$builder->applySort($sort);

ℹ️ applySort() is additive: it appends the sort to the existing order. To replace the existing order, call resetOrder() first:

$builder->resetOrder()->applySort($sort);

SortConfig

SortConfig parses and validates sort parameters from HTTP query strings. It lets you control which columns are sortable, map public parameter names to real column names, and optionally lock sort directions.

use Hector\Query\Sort\SortConfig;

$sortConfig = new SortConfig(
    allowed: ['title', 'created_at', 'id'],
    default: ['title'],
);

// Resolve from query params: ?sort=created_at:desc
$sort = $sortConfig->resolve($request->getQueryParams());
$builder->resetOrder()->applySort($sort);

Constructor parameters

Allowed formats

The allowed parameter accepts several formats:

// Simple: column name as-is, user can choose direction
$config = new SortConfig(
    allowed: ['title', 'created_at', 'id'],
    default: ['title'],
);

// Column mapping: expose a public name, map to real column
$config = new SortConfig(
    allowed: ['name' => 'user_name', 'date' => 'created_at'],
    default: ['name'],
);

// Locked direction: the mapping includes :dir, user cannot override it
$config = new SortConfig(
    allowed: ['status' => 'create_time:desc'],
    default: ['status'],
);

// Multi-column mapping: one public name maps to multiple ORDER BY columns
$config = new SortConfig(
    allowed: ['status' => ['status', 'create_time:desc']],
    default: ['status'],
);

Direction control: free vs. locked

The direction is determined by the presence of :dir in the mapping value:

For multi-column mappings, each column follows its own rule:

$config = new SortConfig(
    allowed: [
        'create_time' => 'review_id',                     // free direction
        'status' => ['status', 'create_time:desc'],        // status=free, create_time=locked DESC
    ],
    default: ['create_time:desc'],
    defaultDir: 'asc',
);

Default sort format

Default sort items use the column:dir string format. Each item must match a key in allowed:

// Single default
$config = new SortConfig(allowed: ['title', 'id'], default: ['title']);

// With explicit direction
$config = new SortConfig(allowed: ['title', 'id'], default: ['title:desc']);

// Multi-column default
$config = new SortConfig(allowed: ['title', 'id'], default: ['title', 'id:desc']);

Supported URL formats

• Single: ?sort=title:asc
• Multiple: ?sort[]=title:asc&sort[]=id:desc
• Without direction (uses defaultDir): ?sort=title

Custom Sort Implementations

Implement SortInterface for custom sorting logic:

use Hector\Query\Sort\SortInterface;
use Hector\Query\QueryBuilder;

class RandomSort implements SortInterface
{
    public function apply(QueryBuilder $builder): void
    {
        $builder->orderBy('RAND()');
    }
}

class NullsLastSort implements SortInterface
{
    public function __construct(
        private string $column,
        private string $dir = 'ASC',
    ) {}

    public function apply(QueryBuilder $builder): void
    {
        $builder->orderBy(sprintf('%s IS NULL', $this->column), 'ASC');
        $builder->orderBy($this->column, $this->dir);
    }
}

📦 Limiting Results

Control pagination using limit() and offset():

$queryBuilder->limit(10);
$queryBuilder->offset(20);
$queryBuilder->resetLimit();

📝 Assigning Values

When building INSERT or UPDATE queries, use:

$queryBuilder
    ->assign('name', 'Alice')
    ->assign('email', 'alice@example.com');

$queryBuilder->resetAssignments();

Or in bulk:

$queryBuilder->assigns([
    'name' => 'Alice',
    'email' => 'alice@example.com'
]);

You can also pass a StatementInterface to assigns() for more advanced use cases.

🔗 Joins

Join tables with the following methods:

$queryBuilder->innerJoin('users', 'users.id = posts.user_id', 'u');
$queryBuilder->leftJoin('categories', 'categories.id = posts.category_id');
$queryBuilder->rightJoin('tags', 'tags.id = posts.tag_id');

$queryBuilder->resetJoin();

🔀 Unions

The Union class allows combining multiple SELECT queries:

use Hector\Query\Select;
use Hector\Query\Union;

$select1 = (new Select())->from('customers')->column('name');
$select2 = (new Select())->from('suppliers')->column('name');

$union = new Union();
$union->addSelect($select1, $select2);

Union also implements StatementInterface, allowing you to bind and execute it like any other query.

Executing a Union

use Hector\Connection\Bind\BindParamList;

$binds = new BindParamList();
$statement = $union->getStatement($binds);

// Execute via connection
foreach ($connection->fetchAll($statement, $binds) as $row) {
    echo $row['name'];
}

Union All (with duplicates)

$union = new Union();
$union->addSelect($select1, $select2);
$union->all(); // UNION ALL instead of UNION

📤 Fetching Results

Use the following methods to retrieve data:

$queryBuilder->fetchOne();      // ?array - first row
$queryBuilder->fetchAll();      // Generator - all rows
$queryBuilder->fetchColumn();   // Generator - specific column

🔢 Counting Results

Quickly count the results of a query:

$count = $queryBuilder
    ->from('table')
    ->where('status', 'active')
    ->count();

$rows = $queryBuilder->fetchAll();

This method resets column selection, limit, and order, but does not mutate the original query builder.

🔁 Distinct Values

Use distinct() to eliminate duplicates:

$queryBuilder
    ->from('users')
    ->distinct()
    ->fetchAll();

❓ Existence Check

Determine if any row matches the conditions:

$exists = $queryBuilder
    ->from('users')
    ->where('email', 'alice@example.com')
    ->exists();

Does not alter the query builder instance.

✍️ Shortcuts for Insert / Update / Delete

You can execute data manipulation directly:

$queryBuilder->insert([
    'name' => 'Alice',
    'email' => 'alice@example.com'
]);

$queryBuilder->update([
    'status' => 'inactive'
]);

$queryBuilder->delete();

You may also use a subquery for insert:

$queryBuilder->insert((new Select())->from('source_table'));

Ignoring Duplicates

Use ignore() to skip rows that would cause duplicate key violations:

$queryBuilder
    ->from('users')
    ->ignore()
    ->insert([
        'email' => 'alice@example.com',
        'name' => 'Alice'
    ]);
// INSERT IGNORE INTO users ...

These shortcut methods do not affect the QueryBuilder instance, so it remains reusable for further operations.

🔒 Locking Rows

Use the $lock parameter on fetch methods to acquire a FOR UPDATE lock on selected rows. This is useful for preventing concurrent modifications in transactional contexts.

$connection->beginTransaction();

// Lock the row for update
$user = $queryBuilder
    ->from('users')
    ->where('id', 1)
    ->fetchOne(lock: true);

// Modify and save
$queryBuilder->from('users')->where('id', 1)->update(['balance' => $user['balance'] - 100]);

$connection->commit();

Available on:

• fetchOne(bool $lock = false)
• fetchAll(bool $lock = false)
• fetchColumn(int $column = 0, bool $lock = false)

📄 Integrated Pagination

The QueryBuilder provides a paginate() method that integrates directly with the Pagination component. It automatically handles limit/offset and returns a pagination object.

use Hector\Pagination\Request\OffsetPaginationRequest;

$request = new OffsetPaginationRequest(page: 3, perPage: 20);

$pagination = $queryBuilder
    ->from('users')
    ->where('active', true)
    ->orderBy('created_at', 'DESC')
    ->paginate($request);

// Access results
foreach ($pagination as $row) {
    echo $row['name'];
}

// Pagination metadata
$pagination->getCurrentPage();  // 3
$pagination->hasMore();         // true/false
$pagination->getTotal();        // null (not computed by default)

With Total Count

Pass withTotal: true to compute the total count (requires an additional query):

$pagination = $queryBuilder
    ->from('users')
    ->paginate($request, withTotal: true);

$pagination->getTotal();       // 1523
$pagination->getTotalPages();  // 77

Supported Request Types

use Hector\Pagination\Request\CursorPaginationRequest;

$request = new CursorPaginationRequest(
    perPage: 20,
    position: ['id' => 42],
);

$pagination = $queryBuilder
    ->from('users')
    ->orderBy('id')
    ->paginate($request);

$pagination->getNextPosition();  // ['id' => 62]