Query builder components

Sparkle's Query Builder provides an elegant and fluent interface for constructing SQL queries in PHP. This documentation covers the available methods, their full method signatures, and examples.

Initialization

To start building a query, use the Sparkle::table() method:

php

$builder = Sparkle::table('users');

Methods

Limit

Set the LIMIT clause for the query.

php

limit(int $limit): Builder

Arguments:

$limit (int): The number of records to retrieve. This defines the maximum number of results returned by the query.

Return:

Builder: Returns the current instance of the query builder, allowing for method chaining.

Example Usage:

php

// Set limit to maximum 10 rows.
$query = Sparkle::table('users')->limit(10);

Offset

Set the OFFSET clause for the query.

php

offset(int $offset): Builder

Arguments:

$offset (int): The number of records to skip before starting to return results. Typically used in pagination, where you might skip a set of records and start from a specific point.

Return:

Builder: Returns the current instance of the query builder, allowing for method chaining.

Example Usage:

php

// Skips the first 5 records and retrieves the next 10
$query = Sparkle::table('users')->offset(5)->limit(10);

OrderBy

Adds an ORDER BY clause to the query to specify the sorting order of the results.

php

orderBy(string $column, string $direction = 'asc'): Builder

Arguments:

$column (string): The name of the column to sort the results by.
$direction (string, optional): The sorting direction. You can set it to:
- 'asc': Sort in ascending order (default).
- 'desc': Sort in descending order.

Return:

Builder: Returns the current instance of the query builder, allowing method chaining.

Example Usage:

php

// Orders the results by the 'name' column in ascending order (default)
$query = Sparkle::table('users')->orderBy('name');

// Orders the results by the 'age' column in descending order
$query = Sparkle::table('users')->orderBy('age', 'desc');

GroupBy

Adds a GROUP BY clause to the query, which groups the results based on a specific column.

php

groupBy(string $column): Builder

Arguments:

$column (string): The name of the column to group the results by.

Return:

Builder: Returns the current instance of the query builder, allowing method chaining.

Example Usage:

php

// Groups the results by the 'category' column
$query = Sparkle::table('products')->groupBy('category');

Having

Adds a HAVING clause to the query. The HAVING clause is used to filter records after they have been grouped (typically used with GROUP BY).

php

having(string $column, string $operator, string $value): Builder

Arguments:

$column (string): The name of the column to apply the condition on.
$operator (string): The comparison operator (e.g., '=', '>', '<', etc.).
$value (string): The value to compare the column against.

Return:

Builder: Returns the current instance of the query builder, allowing method chaining.

Example Usage:

php

// Retrieves products grouped by 'category' and filters those with a total sales greater than 100
$query = Sparkle::table('products')
    ->groupBy('category')
    ->having('total_sales', '>', '100');

Where

Adds a WHERE clause to the query.

php

where(string $column, ComparisonOperator $comparisonOperator, string $value): Builder

Arguments:

$column (string): The name of the column to apply the condition on.
$comparisonOperator (Sirmerdas\Sparkle\Enums\ComparisonOperator\ComparisonOperator): The comparison operator to use for the condition (e.g., ComparisonOperator::EQUAL, ComparisonOperator::GREATER_THAN, ComparisonOperator::LESS_THAN).
$value (string): The value to compare the column against.

TIP

For ComparisonOperator, check out ComparisonOperator Enum

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('users')->where('age', ComparisonOperator::GREATER_THAN, '18');

OrWhere

Adds an OR WHERE clause to the query.

php

orWhere(string $column, ComparisonOperator $comparisonOperator, string $value): Builder

Arguments:

$column (string): The name of the column to apply the condition on.
$comparisonOperator (ComparisonOperator): The comparison operator to use (e.g., ComparisonOperator::EQUAL, ComparisonOperator::NOT_EQUAL).
$value (string): The value to compare the column against.

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('users')->where('age', ComparisonOperator::GREATER_THAN, '18')->orWhere('status', ComparisonOperator::EQUAL, 'active');

WhereNull

Adds a condition to check if a column is NULL.

php

whereNull(string $column): Builder

Arguments:

$column (string): The name of the column to check for NULL.

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('users')->whereNull('deleted_at');

WhereIn

Adds a WHERE IN clause to the query.

php

whereIn(string $column, array $values): Builder

Arguments:

$column (string): The name of the column to apply the WHERE IN condition on.
$values (array): An array of values to match against the column.

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('users')->whereIn('id', [1, 2, 3]);

OrWhereIn

Adds an OR WHERE IN clause to the query.

php

orWhereIn(string $column, array $values): Builder

Arguments:

$column (string): The name of the column to apply the WHERE IN condition on.
$values (array): An array of values to match against the column.

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('users')->orWhereIn('category_id', [1, 2, 3]);

WhereNotIn

Adds a WHERE NOT IN clause to the query.

php

whereNotIn(string $column, array $values): Builder

Arguments:

$column (string): The name of the column to apply the WHERE NOT IN condition on.
$values (array): An array of values to exclude from the column.

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('users')->whereNotIn('category_id', [4, 5, 6]);

orWhereNotIn

Adds an OR WHERE NOT IN clause to the query.

php

orWhereNotIn(string $column, array $values): Builder

Arguments:

$column (string): The name of the column to apply the WHERE NOT IN condition on.
$values (array): An array of values to exclude from the column.

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('users')->orWhereNotIn('category_id', [4, 5, 6]);

WhereNotNull

Adds a condition to check if a column is NOT NULL.

php

whereNotNull(string $column): Builder

Arguments:

$column (string): The name of the column to check for NOT NULL.

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('users')->whereNotNull('updated_at');

Here’s the README in the requested Laravel style for the provided join method:

Join

Adds a JOIN clause to the query, allowing you to combine rows from two or more tables based on a related column.

php

join(string $targetTable, string $leftColumn, ComparisonOperator $comparisonOperator, string $rightColumn, JoinType $joinType = JoinType::INNER): Builder

Arguments:

$targetTable (string): The name of the table to join with.
$leftColumn (string): The column from the current table to use in the join condition.
$comparisonOperator (ComparisonOperator): The operator to use for comparison (e.g., ComparisonOperator::EQUAL, ComparisonOperator::GREATER_THAN, ComparisonOperator::LESS_THAN).
$rightColumn (string): The column from the target table to use in the join condition.
$joinType (JoinType): The type of join to perform (e.g., JoinType::INNER, JoinType::LEFT, JoinType::RIGHT). Defaults to INNER. Check out JoinType

Return:

Returns the current instance of the query builder for method chaining.

Example:

php

Sparkle::table('orders')
    ->join('customers', 'orders.customer_id', ComparisonOperator::EQUAL, 'customers.id')
    ->where('status', ComparisonOperator::EQUAL, 'active');

Here’s the README in the requested Laravel style for the provided methods:

Count

Gets the total count of records for the current query.

php

count(): int

Return:

Returns an int, which is the number of records matching the query.

Example:

php

$totalRecords = Sparkle::table('users')->count();

This will return the total number of records in the users table.

Exists

Determines if any records exist for the current query.

php

exists(): bool

Return:

Returns a bool:

true if records exist.
false otherwise.

Example:

php

$hasRecords = Sparkle::table('users')->exists();

This will return true if there are any records in the users table.

Copy

Creates a copy of the current Builder instance.

php

copy(): Builder

Return:

Returns a new instance of the Builder class with the same property values.

Example:

php

$newBuilder = Sparkle::table('users')->copy();

This will create a new Builder instance that is a copy of the current instance.

TIP

Use This When you want to use one some chained query result as base query for some rows.

Transaction

Executes a set of database operations within a transaction.

php

transaction(callable $transactions): void

Arguments:

$transactions (callable): A callback function that receives a copied instance of the builder and executes database operations within the transaction.

Example:

php

Sparkle::table('users')->transaction(function (Builder $builder) {
    $builder->copy()->createGetId->insert(['name' => 'John Doe']);
    $builder->copy()->createGetId->insert(['name' => 'Test Doe']);
    $builder->copy()->createGetId->insert(['name' => 'New user']);
});

This will execute the insert operation within a database transaction.

Crud operation

Get

Executes the query and retrieves all matching rows.

php

get(array $columns = ['*']): Sirmerdas\Sparkle\Database\QueryBuilder\QueryResult

Arguments:

$columns (array): The columns to select. Defaults to selecting all columns (['*']).

Return:

Returns an instance of QueryResult containing the result of the query.

INFO

For getting familiar better with QueryResult please visit QueryResult docs.

Example:

php

$users = Sparkle::table('users')->get();

This will return all rows from the users table.

SelectRaw

Executes a raw SQL select query with optional parameters.

php

selectRaw(string $query, array $params = []): array

Arguments:

$query (string): The raw SQL query string to execute.
$params (array): An optional array of parameters to bind to the query. Defaults to an empty array.

Return:

Returns an array of results, typically as an array of objects.

Example:

php

$results = Sparkle::table('users')->selectRaw('SELECT * FROM users WHERE age > ?', [30]);

This will execute the raw SQL query and return the matching results.

First

Executes the query and retrieves the first matching row.

php

first(array $columns = ['*']): bool|object

Arguments:

$columns (array): The columns to select. Defaults to selecting all columns (['*']).

Return:

Returns the first matching row as an object, or false if no rows match.

Example:

php

$user = Sparkle::table('users')->first();

This will return the first row from the users table.

Create

Inserts a new record into the database.

php

create(array $data): bool

Arguments:

$data (array): An associative array where keys are column names and values are the corresponding data to insert.

Return:

Returns true if at least one row was inserted successfully, otherwise false.

Example:

php

Sparkle::table('users')->create(['name' => 'John Doe', 'email' => 'john@example.com']);

This will insert a new user into the users table.

CreateRaw

Executes a raw SQL insert query with optional parameters.

php

createRaw(string $query, array $params): int

Arguments:

$query (string): The raw SQL query string to execute.
$params (array): An array of parameters to bind to the query.

Return:

Returns the ID of the last inserted row.

Example:

php

$lastInsertId = Sparkle::table('users')->createRaw('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'john@example.com']);

This will execute the raw SQL insert query and return the last inserted ID.

CreateGetId

Inserts a new record into the database and returns the last inserted ID.

php

createGetId(array $data): int

Arguments:

$data (array): An associative array where keys are column names and values are the corresponding data to insert.

Return:

Returns the ID of the last inserted row.

Example:

php

$userId = Sparkle::table('users')->createGetId(['name' => 'John Doe', 'email' => 'john@example.com']);

This will insert a new user and return the ID of the last inserted user.

Update

Updates records in the database with the provided data.

php

update(array $data): int

Arguments:

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

Return:

Returns the number of affected rows.

Example:

php

$updatedRows = Sparkle::table('users')->update(['name' => 'John Doe']);

This will update the name field for matching users and return the number of affected rows.

UpdateRaw

Executes a raw SQL update query with optional parameters.

php

updateRaw(string $query, array $params): int

Arguments:

$query (string): The raw SQL query string to execute.
$params (array): An array of parameters to bind to the query.

Return:

Returns the number of affected rows.

Example:

php

$affectedRows = Sparkle::table('users')->updateRaw('UPDATE users SET name = ? WHERE email = ?', ['John Doe', 'john@example.com']);

This will execute the raw SQL update query and return the number of affected rows.

Delete

Deletes records from the database based on the provided conditions.

php

delete(array $delete = []): int

Arguments:

$delete (array): An array of column names to delete from the table. Required when using joins.

Return:

Returns the number of rows affected by the delete operation.

Example:

php

$deletedRows = Sparkle::table('users')->delete(['name']);

This will delete records from the users table based on the provided conditions.

DeleteRaw

Executes a raw SQL delete query with optional parameters.

php

deleteRaw(string $query, array $params): int

Arguments:

$query (string): The raw SQL query string to execute.
$params (array): An array of parameters to bind to the query.

Return:

Returns the number of affected rows.

Example:

php

$deletedRows = Sparkle::table('users')->deleteRaw('DELETE FROM users WHERE name = ?', ['John Doe']);

This will execute the raw SQL delete query and return the number of rows affected.

Raw

Executes a raw SQL query with optional parameters and returns the results.

php

raw(string $query, array $params = []): array

Arguments:

$query (string): The SQL query to be executed.
$params (array): Optional parameters to bind to the query.

Return:

Returns an array with:

'items' (array): The fetched results as objects.
'rowCount' (int): The number of affected rows.
'lastInsertId' (string|bool): The last inserted ID if applicable.

Example:

php

$result = Sparkle::table('users')->raw('SELECT * FROM users WHERE age > ?', [30]);

This will execute the raw SQL query and return the results, including the number of affected rows and the last inserted ID.

Example

This is an example of method chaining using Sparkle package

php

Sparkle::table('orders')
    ->where('status', ComparisonOperator::EQUAL, 'shipped')
    ->join('customers', 'orders.customerNumber', ComparisonOperator::EQUAL, 'customers.customerNumber')
    ->join('orderdetails','orders.orderNumber',ComparisonOperator::EQUAL,'orderdetails.orderNumber')
    ->join('products','orderdetails.productCode',ComparisonOperator::EQUAL,'products.productCode')
    ->orderBy('orderDate', 'desc')
    ->where('produscts.productName',ComparisonOperator::LIKE,'%FORD%')
    ->limit(100)
    ->get();

Query builder components ​

Initialization ​

Methods ​

Limit ​

Arguments: ​

Return: ​

Example Usage: ​

Offset ​

Arguments: ​

Return: ​

Example Usage: ​

OrderBy ​

Arguments: ​

Return: ​

Example Usage: ​

GroupBy ​

Arguments: ​

Return: ​

Example Usage: ​

Having ​

Arguments: ​

Return: ​

Example Usage: ​

Where ​

Arguments: ​

Return: ​

Example: ​

OrWhere ​

Arguments: ​

Return: ​

Example: ​

WhereNull ​

Arguments: ​

Return: ​

Example: ​

WhereIn ​

Arguments: ​

Return: ​

Example: ​

OrWhereIn ​

Arguments: ​

Return: ​

Example: ​

WhereNotIn ​

Arguments: ​

Return: ​

Example: ​

orWhereNotIn ​

Arguments: ​

Return: ​

Example: ​

WhereNotNull ​

Arguments: ​

Return: ​

Example: ​

Join ​

Arguments: ​

Return: ​

Example: ​

Count ​

Return: ​

Example: ​

Exists ​

Return: ​

Example: ​

Copy ​

Return: ​

Example: ​

Transaction ​

Arguments: ​

Example: ​

Crud operation ​

Get ​

Arguments: ​

Return: ​

Example: ​

SelectRaw ​

Arguments: ​

Return: ​

Example: ​

Query builder components

Initialization

Methods

Limit

Arguments:

Return:

Example Usage:

Offset

Arguments:

Return:

Example Usage:

OrderBy

Arguments:

Return:

Example Usage:

GroupBy

Arguments:

Return:

Example Usage:

Having

Arguments:

Return:

Example Usage:

Where

Arguments:

Return:

Example:

OrWhere

Arguments:

Return:

Example:

WhereNull

Arguments:

Return:

Example:

WhereIn

Arguments:

Return:

Example:

OrWhereIn

Arguments:

Return:

Example:

WhereNotIn

Arguments:

Return:

Example:

orWhereNotIn

Arguments:

Return:

Example:

WhereNotNull

Arguments:

Return:

Example:

Join

Arguments:

Return:

Example:

Count

Return:

Example:

Exists

Return:

Example:

Copy

Return:

Example:

Transaction

Arguments:

Example:

Crud operation

Get

Arguments:

Return:

Example:

SelectRaw

Arguments:

Return:

Example: