Quering data

The SELECT clause

A SELECT statement retrieves zero or more rows from one or more database tables or database views. ^[1]

The Select object builds a SELECT statement that can be executed in a database by using a SelectStatement instance. The SelectStatement only retrieves data from the database and has no persistent effects on the database.

Some essential Select methods:

Select::columns() - sets the list of columns to include in the final result
Select::from() - indicates the table(s)
Select::where() - restricts the rows returned
Select::groupBy() - projects rows having common values into a smaller set of rows
Select::having() - filters rows resulting from the Select::groupBy() call
Select::orderBy() - sorts the resulting data
Select::createStatement() - creates the SelectStatement object

TypeViewBuilder says diu diuTypeViewBuilder says diu diu

Synopsis

interface Select implements SelectQuery, ConditionalQuery

Sets the list of columns to be included in the final result

public columns(string ... $columns):

Sets the table(s) from which data is to be retrieved

public from(Rowset|Join|string ... $tables):Rowsets

Sets the GROUP BY clause to the query

public groupBy(string ... $columns):

Sets a condition to the HAVING part of the query

public having(Predicate|string ... $rules):Having

Sets columns which are used to sort the resulting data

public orderBy(string ... $columns):static

Limits the result rows

public limit(int $limit, int $offset = 0):static

public createStatement(PDO $pdo):SelectStatement

Returns the parameter handler

public getParams():ParameterHandler

Returns the generated SQL language string

public __toString():string

Checks the basic validity of the SQL language generated

public isValid():bool

Sets the WHERE clause

public resetWhere(?Where $where = null):static

Adds rules to the WHERE conditions component and returns the WHERE clause

public where(Predicate|string ... $rules):Where

Common Table Expressions (CTE)

The WITH clause was introduced in standard SQL to simplify complex queries, especially those with JOINs and subqueries. A WITH clause defines a temporary data set whose output is available to be referenced in subsequent queries. ^[2]

CTEs, like database views and derived tables, enable users to more easily write and maintain complex queries via increased readability and simplification. This reduction in complexity is achieved by deconstructing ordinarily complex queries into simple blocks to be used, and reused if necessary, in rewriting the query. ^[3]

The With object (Common Table Expressions)

You can specify common table expressions using The With object that has one or more Select subclauses.

Synopsis

interface With implements SelectQuery