🔨 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);

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()

📦 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'));

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