JiFramework - QueryBuilder

Introduction

The QueryBuilder class in JiFramework provides a fluent and intuitive interface for building and executing SQL queries using PDO. It supports common SQL operations such as SELECT, INSERT, UPDATE, and DELETE, along with clauses like WHERE, JOIN, GROUP BY, HAVING, ORDER BY, and more. This class simplifies database interactions by allowing developers to construct queries programmatically without writing raw SQL.

Method: `__construct(string $connectionName = 'primary')`

Description: Initializes a new instance of the QueryBuilder class, setting up the PDO connection based on the provided connection name.

Parameters:

$connectionName (string, optional): The name of the database connection to use. Defaults to 'primary'.

Returns: An instance of the QueryBuilder class.

Usage:

use JIFramework\Core\App\App;
// Initialize the App
$app = new App();
// Initialize the QueryBuilder with the default connection
$db = $app->db;

OR

use JIFramework\Core\Database\QueryBuilder;
// Initialize the QueryBuilder with the default connection
$db = new QueryBuilder();
// Or specify a different connection that alreay setup in Config
$db = new QueryBuilder('connection_name');

Explanation:

The constructor obtains a PDO connection using the DatabaseConnection class.
It allows you to work with multiple database connections if configured.

table()

Method: `table(string $table): self`

Description: Sets the table to execute queries on.

Parameters:

$table (string): The name of the table.

Returns: The current instance of the QueryBuilder (for method chaining).

Usage:

$db->table('users');

Explanation:

This method specifies the table on which subsequent queries will be performed.
It is usually the starting point for building a query.

select()

Method: `select(array|string $columns = ['*']): self`

Description: Sets the columns to select in a SELECT query.

Parameters:

$columns (array|string, optional): The columns to select. Defaults to ['*']. You can pass a string or an array of column names.

Returns: The current instance of the QueryBuilder.

Usage:

// Select all columns
$db->table('users')->select();

// Select specific columns
$db->table('users')->select('id', 'name', 'email');

// Using an array
$db->table('users')->select(['id', 'name', 'email']);

Explanation:

If no columns are specified, it defaults to selecting all columns (*).
You can pass multiple columns as arguments or as an array.

where()

Method: `where(string|array $column, string|null $operator = null, mixed $value = null, string $boolean = 'AND'): self`

Description: Adds a WHERE clause to the query.

Parameters:

$column (string|array): The column name or an associative array of column-value pairs.
$operator (string|null, optional): The operator for the comparison (e.g., '=', '!=', '>', '<'). If omitted and $value is provided, defaults to '='.
$value (mixed, optional): The value to compare against.
$boolean (string, optional): Logical operator to combine multiple conditions ('AND' or 'OR'). Defaults to 'AND'.

Returns: The current instance of the QueryBuilder.

Usage:

// Simple where clause
$db->table('users')->where('id', '=', 1);

// Operator defaults to '=' if omitted
$db->table('users')->where('email', '[email protected]');

// Multiple conditions
$db->table('users')
   ->where('status', 'active')
   ->where('age', '>', 18);

// Using an array of conditions
$db->table('users')->where([
    'status' => 'active',
    'role' => 'admin',
]);

// Using OR condition
$db->table('users')
   ->where('status', 'active')
   ->orWhere('role', 'admin');

Explanation:

Allows adding one or multiple WHERE conditions.
If an array is provided as $column, it iterates over each key-value pair.
Supports combining conditions with 'AND' and 'OR'.

orWhere()

Method: `orWhere(string|array $column, string|null $operator = null, mixed $value = null): self`

Description: Adds an OR WHERE clause to the query.

Parameters:

$column (string|array): The column name or an associative array of column-value pairs.
$operator (string|null, optional): The operator for the comparison.
$value (mixed, optional): The value to compare against.

Returns: The current instance of the QueryBuilder.

Usage:

$db->table('users')
   ->where('status', 'active')
   ->orWhere('role', 'admin');

Explanation:

Functions similarly to where, but combines the condition using 'OR'.

join()

Method: `join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self`

Description: Adds a JOIN clause to the query.

Parameters:

$table (string): The table to join.
$first (string): The first column in the join condition.
$operator (string): The operator for the join condition.
$second (string): The second column in the join condition.
$type (string, optional): The type of join ('INNER', 'LEFT', 'RIGHT'). Defaults to 'INNER'.

Returns: The current instance of the QueryBuilder.

Usage:

// Inner join
$db->table('orders')
   ->join('users', 'orders.user_id', '=', 'users.id');
// Left join
$db->table('orders')
   ->join('users', 'orders.user_id', '=', 'users.id', 'LEFT');
// Right join
$db->table('orders')
   ->join('users', 'orders.user_id', '=', 'users.id', 'RIGHT');

Explanation:

Supports different types of joins.
Joins are useful for combining rows from two or more tables.

leftJoin()

Method: `leftJoin(string $table, string $first, string $operator, string $second): self`

Description: Adds a LEFT JOIN clause to the query.

Parameters:

Same as join, but the $type is fixed to 'LEFT'.

Returns: The current instance of the QueryBuilder.

Usage:

$db->table('orders')
   ->leftJoin('users', 'orders.user_id', '=', 'users.id');

rightJoin()

Method: `rightJoin(string $table, string $first, string $operator, string $second): self`

Description: Adds a RIGHT JOIN clause to the query.

Parameters:

Same as join, but the $type is fixed to 'RIGHT'.

Returns: The current instance of the QueryBuilder.

Usage:

$db->table('orders')
   ->rightJoin('users', 'orders.user_id', '=', 'users.id');

limit()

Method: `limit(int $limit): self`

Description: Sets the LIMIT for the query.

Parameters:

$limit (int): The maximum number of records to retrieve.

Returns: The current instance of the QueryBuilder.

Usage:

$db->table('users')->limit(10);

Explanation:

Limits the number of records returned by a SELECT query.

offset()

Method: `offset(int $offset): self`

Description: Sets the OFFSET for the query.

Parameters:

$offset (int): The number of records to skip.

Returns: The current instance of the QueryBuilder.

Usage:

$db->table('users')->limit(10)->offset(20);

Explanation:

Useful for pagination in combination with limit.

orderBy()

Method: `orderBy(string $column, string $direction = 'ASC'): self`

Description: Adds an ORDER BY clause to the query.

Parameters:

$column (string): The column to sort by.
$direction (string, optional): The sort direction ('ASC' or 'DESC'). Defaults to 'ASC'.

Returns: The current instance of the QueryBuilder.

Usage:

// Order by created_at descending
$db->table('users')->orderBy('created_at', 'DESC');

Explanation:

You can chain multiple orderBy calls to sort by multiple columns.

groupBy()

Method: `groupBy(array|string $columns): self`

Description: Adds a GROUP BY clause to the query.

Parameters:

$columns (array|string): The column(s) to group by.

Returns: The current instance of the QueryBuilder.

Usage:

// Group by status
$db->table('users')->groupBy('status');

// Group by multiple columns
$db->table('orders')->groupBy(['user_id', 'status']);

Explanation:

Groups the result set by one or more columns.

having()

Method: `having(string $column, string $operator, mixed $value, string $boolean = 'AND'): self`

Description: Adds a HAVING clause to the query.

Parameters:

$column (string): The column or aggregate function to apply the condition on.
$operator (string): The operator for the comparison.
$value (mixed): The value to compare against.
$boolean (string, optional): Logical operator to combine multiple conditions ('AND' or 'OR'). Defaults to 'AND'.

Returns: The current instance of the QueryBuilder.

Usage:

$db->table('orders')
   ->select('user_id', 'SUM(total) as total_spent')
   ->groupBy('user_id')
   ->having('total_spent', '>', 1000);

Explanation:

HAVING is used to filter results after aggregation (GROUP BY).

orHaving()

Method: `orHaving(string $column, string $operator, mixed $value): self`

Description: Adds an OR HAVING clause to the query.

Parameters:

Same as having, but combines the condition using 'OR'.

Returns: The current instance of the QueryBuilder.

Usage:

$db->table('orders')
   ->select('user_id', 'SUM(total) as total_spent')
   ->groupBy('user_id')
   ->having('total_spent', '>', 1000)
   ->orHaving('total_spent', '<', 100);

get()

Method: `get(): array`

Description: Executes the query and retrieves all matching records as an array.

Parameters: None

Returns: An array of associative arrays representing the result set.

Usage:

$users = $db->table('users')->where('status', 'active')->get();

foreach ($users as $user) {
    echo $user['name'] . '
';
}

Explanation:

Executes the built query and fetches all results.
Resets the internal state after execution.

first()

Method: `first(): array|false`

Description: Executes the query and retrieves the first matching record.

Parameters: None

Returns: An associative array representing the first result, or false if no records are found.

Usage:

$user = $db->table('users')->where('id', 1)->first();

if ($user) {
    echo 'User found: ' . $user['name'];
} else {
    echo 'User not found.';
}

Explanation:

Automatically sets LIMIT 1 on the query.
Useful when expecting a single record.

fetchAll()

Method: `fetchAll(): array`

Description: Alias for get(). Executes the query and retrieves all matching records.

Parameters: None

Returns: An array of associative arrays representing the result set.

Usage:

$users = $db->table('users')->fetchAll();

fetch()

Method: `fetch(): array|false`

Description: Alias for first(). Executes the query and retrieves the first matching record.

Parameters: None

Returns: An associative array representing the first result, or false if no records are found.

Usage:

$user = $db->table('users')->fetch();

value()

Method: `value(string $column)`

Description: Fetches a single column value from the first matching record.

Parameters:

$column (string): The column name to retrieve.

Returns: The value of the specified column, or null if not found.

Usage:

$email = $db->table('users')->where('id', 1)->value('email');

echo 'User email: ' . $email;

Explanation:

Simplifies retrieving a single value from the database.

count()

Method: `count(): int`

Description: Counts the number of records matching the query.

Parameters: None

Returns: An integer representing the count.

Usage:

$totalUsers = $db->table('users')->where('status', 'active')->count();

echo 'Total active users: ' . $totalUsers;

Explanation:

Executes a SELECT COUNT(*) query.
Useful for pagination or statistics.

insert()

Method: `insert(array $data): bool`

Description: Inserts a new record into the database.

Parameters:

$data (array): An associative array of column-value pairs to insert.

Returns: bool indicating whether the insertion was successful.

Usage:

$result = $db->table('users')->insert([
    'name' => 'Jane Doe',
    'email' => '[email protected]',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
]);

if ($result) {
    echo 'User inserted successfully.';
} else {
    echo 'Failed to insert user.';
}

Explanation:

Prepares an INSERT INTO statement.
Binds the values securely to prevent SQL injection.

update()

Method: `update(array $data): int`

Description: Updates records in the database matching the query.

Parameters:

$data (array): An associative array of column-value pairs to update.

Returns: An integer representing the number of affected rows.

Usage:

$rowsUpdated = $db->table('users')
    ->where('id', 1)
    ->update([
        'email' => '[email protected]',
    ]);

echo 'Rows updated: ' . $rowsUpdated;

Explanation:

Prepares an UPDATE statement with SET clauses.
Requires a WHERE clause to avoid updating all records unintentionally.

delete()

Method: `delete(): int`

Description: Deletes records from the database matching the query.

Parameters: None

Returns: An integer representing the number of affected rows.

Usage:

$rowsDeleted = $db->table('users')->where('id', 1)->delete();

echo 'Rows deleted: ' . $rowsDeleted;

Explanation:

Prepares a DELETE FROM statement.
Requires a WHERE clause to avoid deleting all records.

execute()

Method: `execute(): int`

Description: Executes a query without fetching results. Useful for INSERT, UPDATE, or DELETE statements when you need the number of affected rows.

Parameters: None

Returns: An integer representing the number of affected rows.

Usage:

$affectedRows = $db->query("DELETE FROM users WHERE status = 'inactive'")->execute();

echo 'Inactive users deleted: ' . $affectedRows;

Explanation:

Executes the query and returns the number of affected rows.
Can be used with raw SQL queries.

toSql()

Method: `toSql(): string`

Description: Generates the SQL representation of the query without executing it.

Parameters: None

Returns: A string containing the SQL query.

Usage:

$sql = $db->table('users')->where('status', 'active')->toSql();

echo 'Generated SQL: ' . $sql;

Explanation:

Useful for debugging or logging purposes.
Note that parameter bindings are not substituted in the string.

query()

Method: `query(string $sql): self`

Description: Sets a raw SQL query to be executed.

Parameters:

$sql (string): The raw SQL query.

Returns: The current instance of the QueryBuilder.

Usage:

$db->query('SELECT * FROM users WHERE status = :status')->bind(':status', 'active');

Explanation:

Allows you to execute custom SQL queries while still benefiting from parameter binding.

bind()

Method: `bind(string|array $param, mixed $value = null, int|null $type = null): self`

Description: Binds a parameter to the query. Supports binding multiple parameters at once.

Parameters:

$param (string|array): The parameter name (e.g., ':id') or an associative array of parameters.
$value (mixed, optional): The value to bind if $param is a string.
$type (int|null, optional): The PDO parameter type (e.g., PDO::PARAM_INT). If omitted, it is detected automatically.

Returns: The current instance of the QueryBuilder.

Usage:

// Binding a single parameter
$db->query('SELECT * FROM users WHERE id = :id')->bind(':id', 1);

// Binding multiple parameters
$db->query('SELECT * FROM users WHERE id = :id AND status = :status')
   ->bind([
       ':id' => 1,
       ':status' => 'active',
   ]);

Explanation:

Supports both single and multiple parameter bindings.
Automatically detects the PDO parameter type if not specified.

lastInsertId()

Method: `lastInsertId(): string`

Description: Retrieves the ID of the last inserted row.

Parameters: None

Returns: A string representing the last inserted ID.

Usage:

$db->table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
]);

$lastId = $db->lastInsertId();

echo 'Last inserted ID: ' . $lastId;

Explanation:

Useful after an INSERT operation to get the auto-incremented ID.

Complex Query Examples

Example 1: Complex Select with Joins, Group By, Having, and Order By

$db = new QueryBuilder();

$results = $db->table('orders')
    ->select('users.name', 'users.email', 'SUM(orders.total) as total_spent')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->where('orders.status', 'completed')
    ->groupBy('users.id')
    ->having('total_spent', '>', 1000)
    ->orderBy('total_spent', 'DESC')
    ->limit(10)
    ->get();

foreach ($results as $row) {
    echo $row['name'] . ' spent ' . $row['total_spent'] . '
';
}

Explanation:

Selects users who have completed orders with total spending over $1000.
Joins orders and users tables.
Groups the results by user.
Orders the results by total spending in descending order.
Limits the results to the top 10 users.

Example 2: Subquery with Raw SQL

$db = new QueryBuilder();
$subQuery = '(SELECT AVG(rating) FROM reviews WHERE reviews.product_id = products.id)';
$results = $db->table('products')
   ->select('products.name', 'products.price', "{$subQuery} as average_rating")
   ->where('products.price', '>', 50)
   ->orderBy('average_rating', 'DESC')
   ->get();
foreach ($results as $product) {
   echo $product['name'] . ' has an average rating of ' . $product['average_rating'] . '
';
}

Explanation:

Calculates the average rating for each product using a subquery.
Selects products priced over $50.
Orders the products by their average rating.

Example 3: Transactions

$db = new QueryBuilder();

try {
    $db->beginTransaction();

    // Update user balance
    $db->table('users')
        ->where('id', $userId)
        ->update(['balance' => $newBalance]);

    // Insert transaction record
    $db->table('transactions')
        ->insert([
            'user_id' => $userId,
            'amount' => $amount,
            'type' => 'debit',
            'created_at' => date('Y-m-d H:i:s'),
        ]);

    $db->commit();
    echo 'Transaction completed successfully.';
} catch (Exception $e) {
    $db->rollBack();
    echo 'Transaction failed: ' . $e->getMessage();
}

Explanation:

Demonstrates using transactions to ensure data integrity.
If any operation fails, the entire transaction is rolled back.

Example 4: Multiple Joins with Aliases and Complex Conditions

$db = new QueryBuilder();
// Fetch orders along with customer and product details where the order amount is greater than $500
$results = $db->table('orders as o')
   ->select('o.order_id', 'c.name as customer_name', 'p.product_name', 'o.total_amount')
   ->join('customers as c', 'o.customer_id', '=', 'c.customer_id')
   ->join('order_items as oi', 'o.order_id', '=', 'oi.order_id')
   ->join('products as p', 'oi.product_id', '=', 'p.product_id')
   ->where('o.total_amount', '>', 500)
   ->orderBy('o.order_date', 'DESC')
   ->get();
foreach ($results as $order) {
   echo "Order ID: {$order['order_id']} - Customer: {$order['customer_name']} - Product: {$order['product_name']} - Total: {$order['total_amount']}
";
}

Explanation:

Tables and Aliases: The main table orders is aliased as o.
Joins:

Join customers table as c to get customer details.
Join order_items table as oi to get items in each order.
Join products table as p to get product details.

Conditions:

Only select orders where o.total_amount is greater than 500.

Ordering:

Orders are sorted by o.order_date in descending order.

Example 5: Using Subqueries and EXISTS Clause

$db = new QueryBuilder();
// Fetch users who have not placed any orders
$subQuery = $db->table('orders')
   ->select('user_id')
   ->whereRaw('orders.user_id = users.id')
   ->toSql();
$usersWithoutOrders = $db->table('users')
   ->select('users.id', 'users.name', 'users.email')
   ->whereRaw("NOT EXISTS ({$subQuery})")
   ->get();
foreach ($usersWithoutOrders as $user) {
   echo "User ID: {$user['id']} - Name: {$user['name']} - Email: {$user['email']}
";
}

Explanation:

Subquery:

Creates a subquery to select user_id from orders where orders.user_id equals users.id.

NOT EXISTS:

Uses NOT EXISTS to find users who do not have matching records in the orders table.

whereRaw:

Since the QueryBuilder may not have a whereExists method, whereRaw is used to insert raw SQL conditions.

Example 9: Pagination with OFFSET and LIMIT

$db = new QueryBuilder();
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 20;
$offset = ($page - 1) * $perPage;
$results = $db->table('articles')
   ->select('id', 'title', 'published_at')
   ->where('status', 'published')
   ->orderBy('published_at', 'DESC')
   ->limit($perPage)
   ->offset($offset)
   ->get();
foreach ($results as $article) {
   echo "Article ID: {$article['id']} - Title: {$article['title']}
";
}

Explanation:

Pagination Logic:

Calculates $offset based on the current page.

Query:

Retrieves a specific number of articles per page.

Ordering:

Orders articles by publication date.

Example 11: Union of Two Queries

$db = new QueryBuilder();
$activeUsers = $db->table('users')
   ->select('id', 'name', 'email')
   ->where('status', 'active')
   ->toSql();
$pendingUsers = $db->table('users')
   ->select('id', 'name', 'email')
   ->where('status', 'pending')
   ->toSql();
$unionQuery = "{$activeUsers} UNION {$pendingUsers} ORDER BY name ASC";
$results = $db->query($unionQuery)->get();
foreach ($results as $user) {
   echo "User ID: {$user['id']} - Name: {$user['name']}
";
}

Explanation:

Union of Queries:

Combines results from two queries using UNION.

Ordering:

Orders the combined results by name.

QueryBuilder

Introduction

Method: __construct(string $connectionName = 'primary')

table()

Method: table(string $table): self

select()

Method: select(array|string $columns = ['*']): self

Parameters:

where()

Method: where(string|array $column, string|null $operator = null, mixed $value = null, string $boolean = 'AND'): self

orWhere()

Method: orWhere(string|array $column, string|null $operator = null, mixed $value = null): self

join()

Method: join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self

leftJoin()

Method: leftJoin(string $table, string $first, string $operator, string $second): self

rightJoin()

Method: rightJoin(string $table, string $first, string $operator, string $second): self

limit()

Method: limit(int $limit): self

offset()

Method: offset(int $offset): self

orderBy()

Method: orderBy(string $column, string $direction = 'ASC'): self

groupBy()

Method: groupBy(array|string $columns): self

having()

Method: having(string $column, string $operator, mixed $value, string $boolean = 'AND'): self

orHaving()

Method: orHaving(string $column, string $operator, mixed $value): self

get()

Method: get(): array

first()

Method: first(): array|false

fetchAll()

Method: fetchAll(): array

fetch()

Method: fetch(): array|false

value()

Method: value(string $column)

count()

Method: count(): int

insert()

Method: insert(array $data): bool

update()

Method: update(array $data): int

delete()

Method: delete(): int

execute()

Method: execute(): int

toSql()

Method: toSql(): string

query()

Method: query(string $sql): self

bind()

Method: bind(string|array $param, mixed $value = null, int|null $type = null): self

lastInsertId()

Method: lastInsertId(): string

Complex Query Examples

Complex Query Examples

Topics

Method: `__construct(string $connectionName = 'primary')`

Method: `table(string $table): self`

Method: `select(array|string $columns = ['*']): self`

Method: `where(string|array $column, string|null $operator = null, mixed $value = null, string $boolean = 'AND'): self`

Method: `orWhere(string|array $column, string|null $operator = null, mixed $value = null): self`

Method: `join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self`

Method: `leftJoin(string $table, string $first, string $operator, string $second): self`

Method: `rightJoin(string $table, string $first, string $operator, string $second): self`

Method: `limit(int $limit): self`

Method: `offset(int $offset): self`

Method: `orderBy(string $column, string $direction = 'ASC'): self`

Method: `groupBy(array|string $columns): self`

Method: `having(string $column, string $operator, mixed $value, string $boolean = 'AND'): self`

Method: `orHaving(string $column, string $operator, mixed $value): self`

Method: `get(): array`

Method: `first(): array|false`

Method: `fetchAll(): array`

Method: `fetch(): array|false`

Method: `value(string $column)`

Method: `count(): int`

Method: `insert(array $data): bool`

Method: `update(array $data): int`

Method: `delete(): int`

Method: `execute(): int`

Method: `toSql(): string`

Method: `query(string $sql): self`

Method: `bind(string|array $param, mixed $value = null, int|null $type = null): self`

Method: `lastInsertId(): string`