Select

A class for creating SQL SELECT statements. It extends the base Query class and implements ISelectQuery.

Complex Usage Example

Below is a comprehensive example demonstrating how many of the Select class methods can be chained together to build a complex query. This example includes query flags, aliased columns, a subquery column, joins, multiple where conditions, grouping, having clauses, and pagination.

PHP Implementation:

use Staple\Query\Select;

$select = Select::table(['u' => 'users'])
    ->addFlag(Select::DISTINCT)
    ->columns(['id' => 'u.id', 'username' => 'u.username'])
    ->addColumn('p.first_name', 'first_name')
    ->addColumn('p.last_name', 'last_name')
    ->addColumn(
        Select::table(['o' => 'orders'])
            ->addColumn('COUNT(*)')
            ->where('o.user_id', '=', 'u.id', true),
        'order_count'
    )
    ->join('profiles', 'u.id = p.user_id', 'p')
    ->where('u.status', '=', 'active')
    ->whereIn('u.role', ['admin', 'moderator'])
    ->whereBetween('u.last_login', '2025-01-01', '2025-12-31')
    ->groupBy('u.id')
    ->havingCondition('order_count', '>', 5)
    ->orderBy('u.username ASC')
    ->limit(10, 20);

echo $select->build();

Generated SQL (MySQL):

SELECT DISTINCT u.id AS `id`, u.username AS `username`, p.first_name AS `first_name`, p.last_name AS `last_name`, (SELECT COUNT(*) FROM orders AS `o` WHERE o.user_id = u.id) AS `order_count`
FROM users AS `u`
INNER JOIN profiles AS `p` ON u.id = p.user_id
WHERE u.status = :u_status AND u.role IN (:u_role_in_1, :u_role_in_2) AND u.last_login BETWEEN :u_last_login_start AND :u_last_login_end
GROUP BY u.id
HAVING order_count > :order_count
ORDER BY u.username ASC
LIMIT 10 OFFSET 20

Query Description:

1. Distinct Results: The DISTINCT flag ensures that only unique rows are returned.
2. Aliased Columns: It selects the id and username from the users table, giving them explicit aliases.
3. Joins: It performs an INNER JOIN with the profiles table to include the user’s first and last names.
4. Subquery Column: It includes a calculated order_count column using a subquery that counts rows in the orders table for each user.
5. Filtering:
   • Filters by active users (u.status).
   • Filters by specific roles using whereIn.
   • Filters by a date range using whereBetween.
6. Grouping & Having: Groups results by the user’s ID and uses a HAVING clause to only include users with more than 5 orders (referencing the subquery alias).
7. Ordering: Sorts the final result set by username in ascending order.
8. Pagination: Uses limit to implement pagination, taking 10 results and skipping the first 20.

Constants (Flags)

• ALL, DISTINCT, DISTINCTROW, HIGH_PRIORITY, STRAIGHT_JOIN, SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CACHE, SQL_NO_CACHE, SQL_CALC_FOUND_ROWS.

Factory Methods

Select::table(Query | array | string $table): static

Static factory method to create a new Select query instance for a specific table.

Example:

use Staple\Query\Select;

$select = Select::table('users');

Select::select(mixed $table = null, array $columns = null, IConnection | null $db = null, string | array | null $order = null, int | null $limit = null, bool | null $parameterized = null): Select

Static factory method to create a new Select query instance with multiple parameters.

Example:

use Staple\Query\Select;

$select = Select::select('users', ['id', 'username']);

Methods

__construct(mixed $table = null, array $columns = null, IConnection | null $db = null, string | array | null $order = null, int | null $limit = null, bool | null $parameterized = null)

Constructor for the Select query. All parameters are optional and can be set later using setter methods.

Example:

use Staple\Query\Select;

$select = new Select('users', ['id', 'username']);

setTable(Query | Union | array | string $table, string | null $alias = null): Select

Sets the primary table for the SELECT query. It can be a table name (string), an array of tables, or a subquery (Select or Union object). An optional alias can be provided and is required when using subquery objects.

Example:

$select->setTable('users', 'u');

// Using a subquery
$subquery = Select::table('orders')->where('status', '=', 'shipped');
$select->setTable($subquery, 'shipped_orders');

addFlag($flag): static

Adds a specific SQL flag to the SELECT statement.

Example:

$select->addFlag(Select::DISTINCT);

clearFlags(): static

Removes all flags currently set for the query.

Example:

$select->addFlag(Select::DISTINCT);
$select->clearFlags();

columns(array $columns): static

Sets the list of columns to be selected. This replaces any existing columns in the query.

Example:

$select->columns(['id', 'username', 'email']);

addColumn(string | Select $col, string | null $name = null): static

Adds a single column or subquery to the selection. An optional alias ($name) can be provided.

Example:

$select->addColumn('COUNT(*)', 'total');

// Using a sub-select column
$subSelect = Select::table('orders')->addColumn('COUNT(*)')->whereColumn('users.id', '=', 'orders.user_id');
$select->addColumn($subSelect, 'order_count');

addColumnsArray(array $columns): static

Adds an array of columns to the existing selection.

Example:

$select->addColumnsArray(['first_name', 'last_name']);

setColumns(array $columns): static

Replaces all existing columns in the query with the provided array.

Example:

$select->setColumns(['id', 'name']);

removeColumn(string $col): bool

Removes a column from the selection by its name or expression. Returns true if the column was found and removed, false otherwise.

Example:

$select->removeColumn('email');

removeColumnByName(string $name): bool

Removes a column from the selection by its alias. Returns true if the column was found and removed, false otherwise.

Example:

$select->addColumn('COUNT(*)', 'total');
$select->removeColumnByName('total');

setOrder(array | string $order): static

Sets the ORDER BY clause for the query.

Example:

$select->setOrder('name ASC');

setGroupBy(array | string $group): static

Sets the GROUP BY clause for the query.

Example:

$select->setGroupBy('category_id');

setLimit(int $limit): static

Sets the LIMIT for the query.

Example:

$select->setLimit(10);

setLimitOffset(int $offset): static

Sets the OFFSET for the query.

Example:

$select->setLimitOffset(20);

where(string $column, string $operator, mixed $value, bool | null $columnJoin = null, string | null $paramName = null, bool $parameterized = true): static

Adds a WHERE condition to the query. Inherited from the Query class.

Example:

$select->where('status', '=', 'active');

clearWhere(): static

Removes all WHERE conditions currently set for the query.

Example:

$select->where('status', '=', 'active');
$select->clearWhere();

whereEqual(string $column, mixed $value, bool | null $columnJoin = null, string | null $paramName = null, bool $parameterized = true): static

Adds an equality (=) condition to the WHERE clause.

Example:

$select->whereEqual('id', 123);

whereIn(string $column, mixed $values, string | null $paramName = null, bool $parameterized = true): static

Adds an IN condition to the WHERE clause.

Example:

$select->whereIn('status', ['active', 'pending']);

orWhere(string $column, string $operator, mixed $value, bool | null $columnJoin = null, string | null $paramName = null, bool $parameterized = true): static

Adds an OR condition to the WHERE clause.

Example:

$select->where('role', '=', 'admin')
       ->orWhere('role', '=', 'superadmin');

orderBy(array | string $order): static

Sets the ORDER BY clause for the query.

Example:

$select->orderBy('last_login DESC');

groupBy($group): static

Sets the GROUP BY clause for the query.

Example:

$select->groupBy('category_id');

limit(Pager | int $limit, int | null $offset = null): static

Sets the LIMIT and optionally the OFFSET for the query. If a Pager object is passed, it automatically sets both limit and offset.

Example:

$select->limit(20, 40); // LIMIT 20 OFFSET 40

skip(int $offset): static

Sets the number of rows to skip (OFFSET).

Example:

$select->skip(10); // OFFSET 10

take(int $amount): static

Sets the number of rows to return (LIMIT).

Example:

$select->take(5); // LIMIT 5

havingCondition(string $column, string $operator, mixed $value, bool | null $columnJoin = null, string | null $paramName = null, bool $parameterized = true): static

Adds a HAVING condition to the query.

Example:

$select->groupBy('user_id');
$select->havingCondition('COUNT(*)', '>', 5);

havingEqual(string $column, mixed $value, bool | null $columnJoin = null, string | null $paramName = null, bool $parameterized = true): static

Adds an equality (=) condition to the HAVING clause.

Example:

$select->havingEqual('status', 'active');

havingLike(string $column, mixed $value, bool | null $columnJoin = null, string | null $paramName = null, bool $parameterized = true): static

Adds a LIKE condition to the HAVING clause.

Example:

$select->havingLike('username', 'admin%');

havingIn(string $column, array $values, string | null $paramName = null, bool $parameterized = true): static

Adds an IN condition to the HAVING clause.

Example:

$select->havingIn('category_id', [1, 2, 3]);

havingBetween(string $column, mixed $start, mixed $end, string | null $startParamName = null, string | null $endParamName = null, bool $parameterized = true): static

Adds a BETWEEN condition to the HAVING clause.

Example:

$select->havingBetween('price', 10, 100);

havingNull(string $column): static

Adds an IS NULL condition to the HAVING clause.

Example:

$select->havingNull('deleted_at');

havingStatement(string | Condition $statement): static

Adds a raw SQL statement to the HAVING clause.

Example:

$select->havingStatement('SUM(total) > 1000');

clearHaving(): static

Removes all HAVING conditions currently set for the query.

Example:

$select->havingEqual('total', 100);
$select->clearHaving();

addHaving(Condition $having): static

Adds a Condition object directly to the HAVING clause.

Example:

use Staple\Query\Condition;
$condition = Condition::get('total', '>', 500);
$select->addHaving($condition);

isJoined(string $table): bool

Checks if a specific table is already joined to the query. Returns true if joined, false otherwise.

Example:

if (!$select->isJoined('profiles')) {
    $select->leftJoin('profiles', 'users.id = profiles.user_id');
}

leftJoin(string $table, string $condition, string | null $alias = null, string | null $schema = null): static

Adds a LEFT OUTER JOIN to the query.

Example:

$select->leftJoin('profiles', 'users.id = profiles.user_id', 'p');

innerJoin(string $table, string $condition, string | null $alias = null, string | null $schema = null): static

Adds an INNER JOIN to the query.

Example:

$select->innerJoin('orders', 'users.id = orders.user_id', 'o');

join(string $table, string $condition, string | null $alias = null, string | null $schema = null): static

Alias for innerJoin.

Example:

$select->join('orders', 'users.id = orders.user_id');

removeJoin(string $table): bool

Removes a join from the query by table name. Returns true if the join was found and removed, false otherwise.

Example:

$select->removeJoin('orders');

addJoin(Join $join): static

Adds a Join object directly to the query.

Example:

use Staple\Query\Join;
$join = Join::left('profiles', 'users.id = profiles.user_id');
$select->addJoin($join);

getJoins(): array

Returns an array of all Join objects currently attached to the query.

Example:

$joins = $select->getJoins();

getColumns(): array

Returns an array of all columns currently set for selection.

Example:

$columns = $select->getColumns();

getOrder(): array | string

Returns the current ORDER BY clause, either as a string or an array.

Example:

$order = $select->getOrder();

getGroupBy(): array | string

Returns the current GROUP BY clause, either as a string or an array.

Example:

$groupBy = $select->getGroupBy();

getLimit(): Pager | int

Returns the current limit, either as an integer or a Pager object.

Example:

$limit = $select->getLimit();

getLimitOffset(): int

Returns the current limit offset as an integer.

Example:

$offset = $select->getLimitOffset();

build(bool | null $parameterized = null): string

Builds and returns the full SQL SELECT statement string. An optional $parameterized flag can be passed to override the query’s default parameterization setting.

Example:

$sql = $select->build();

execute(IConnection | null $connection = null): ModelQueryResult | Statement | false

Executes the SELECT query and returns a Statement object. An optional database connection can be provided to override the query’s default connection.

Example:

$result = $select->execute();
foreach ($result as $row) {
    echo $row['username'];
}