Sql
extends Base
in package
Query object to mutate and generate (complex) SQL queries.
Tags
Table of Contents
- $columns : array<string|int, mixed>|string
- Array or string containing the fields, When the key is a string it will be used as alias "$value AS $key" "*" becomes "*" "id, name" becomes "id, name" array('id', 'name') becomes "id, name" array('id', 'name2' => 'name') becomes "id, name AS name2".
- $groupBy : array<string|int, mixed>|string
- Array met group by info, wordt verbonden met ', '.
- $having : string|array<string|int, mixed>
- Similar to $where, but for the HAVING clause.
- $limit : false|int
- Limit the number of results.
- $offset : false|int
- Skip the first x results.
- $orderBy : array<string|int, mixed>|string
- index is naam, waarde is DESC of ASC.
- $select : string
- Hiermee kun de "SELECT" aanpassen naar een "SELECT SQL_COUNT" e.d.
- $tables : array<string|int, mixed>
- array('alias1'=> 'table1', 't2' => array('type' => 'INNER JOIN', 'table' => 'table2 AS t2', 'on' => 't1.id = t2.t1_id'));.
- $where : string|array<string|int, mixed>
- Tree-structure with conditions. array('AND', 'col1 = 23', array('OR', 'col2 = 1', 'col2 = 56')).
- __call() : mixed
- Report that the $method doesn't exist.
- __callStatic() : mixed
- Report that the $method doesn't exist.
- __get() : mixed
- Report that $property doesn't exist.
- __set() : mixed
- Report that $property doesn't exist and set the property to the given $value.
- __toString() : string
- Allow the SQL object to be used as string.
- addColumns() : mixed
- Adds columns to the existing $this->columns.
- andWhere() : Sql
- Returns a new SQL with the where modified to include the "AND" restriction.
- column() : Sql
- Returns a new SQL with the given $column added to the $sql->columns.
- columns() : Sql
- Returns a new SQL with the given $columns added to the $sql->columns.
- from() : Sql
- Single table: from('table') or from('table AS t') Multiple tables: from('table1', 'table2') of from(array('table1', 'table2')) from('table1 AS t1', 'table2 t2') of from(array('t1' => 'table1', 'table2 AS t2')).
- groupBy() : Sql
- Returns a new SQL with $this->groupBy set to the given $columns.
- innerJoin() : Sql
- Returns a new SQL with the given join appended.
- leftJoin() : Sql
- Returns a new SQL with the given join appended.
- limit() : Sql
- Return a new SQL with $this->limit set to the given $limit.
- offset() : Sql
- Return a new SQL with $this->offset set to the given $limit.
- orderBy() : Sql
- Returns a new SQL with $this->orderBy set to the given $column => $direction.
- orWhere() : Sql
- Returns a new SQL with the where modified to include the "OR" restriction.
- removeColumn() : mixed
- Remove a column via its alias.
- rightJoin() : Sql
- Returns a new SQL with the given join appended.
- select() : Sql
- Returns a new SQL with $sql->columns replaced by the given $columns.
- setFrom() : mixed
- Set the FROM clause to one or more tables.
- setJoin() : mixed
- Set/Append a join to the FROM.
- where() : Sql
- Returns a new SQL with the $sql->where set to the given $where.
- compose() : string
- Build the SQL string.
- composeRestrictions() : string
- De inhoud van de WHERE or HAVING samenstellen.
- extractAlias() : string
- De alias van een kolom of tabel uitzoeken.
- join() : Sql
- Returns a new SQL with the join added to the sql.
Properties
$columns
Array or string containing the fields, When the key is a string it will be used as alias "$value AS $key" "*" becomes "*" "id, name" becomes "id, name" array('id', 'name') becomes "id, name" array('id', 'name2' => 'name') becomes "id, name AS name2".
public
array<string|int, mixed>|string
$columns
= []
$groupBy
Array met group by info, wordt verbonden met ', '.
public
array<string|int, mixed>|string
$groupBy
= []
$having
Similar to $where, but for the HAVING clause.
public
string|array<string|int, mixed>
$having
= ''
$limit
Limit the number of results.
public
false|int
$limit
= false
$offset
Skip the first x results.
public
false|int
$offset
= false
$orderBy
index is naam, waarde is DESC of ASC.
public
array<string|int, mixed>|string
$orderBy
= []
$select
Hiermee kun de "SELECT" aanpassen naar een "SELECT SQL_COUNT" e.d.
public
string
$select
= 'SELECT'
Tags
$tables
array('alias1'=> 'table1', 't2' => array('type' => 'INNER JOIN', 'table' => 'table2 AS t2', 'on' => 't1.id = t2.t1_id'));.
public
array<string|int, mixed>
$tables
= []
$where
Tree-structure with conditions. array('AND', 'col1 = 23', array('OR', 'col2 = 1', 'col2 = 56')).
public
string|array<string|int, mixed>
$where
= ''
Methods
__call()
Report that the $method doesn't exist.
public
__call(string $method, array<string|int, mixed> $arguments) : mixed
Parameters
- $method : string
- $arguments : array<string|int, mixed>
Return values
mixed —__callStatic()
Report that the $method doesn't exist.
public
static __callStatic(string $method, array<string|int, mixed> $arguments) : mixed
Parameters
- $method : string
- $arguments : array<string|int, mixed>
Return values
mixed —__get()
Report that $property doesn't exist.
public
__get(string $property) : mixed
Parameters
- $property : string
Return values
mixed —__set()
Report that $property doesn't exist and set the property to the given $value.
public
__set(string $property, mixed $value) : mixed
Parameters
- $property : string
- $value : mixed
Return values
mixed —__toString()
Allow the SQL object to be used as string.
public
__toString() : string
Return values
string —addColumns()
Adds columns to the existing $this->columns.
public
addColumns(array<string|int, mixed> $columns) : mixed
Parameters
- $columns : array<string|int, mixed>
-
param string ...
Return values
mixed —andWhere()
Returns a new SQL with the where modified to include the "AND" restriction.
public
andWhere(mixed $restriction) : Sql
Parameters
- $restriction : mixed
Return values
Sql —column()
Returns a new SQL with the given $column added to the $sql->columns.
public
column(string $column[, string $alias = null ]) : Sql
Parameters
- $column : string
- $alias : string = null
Return values
Sql —columns()
Returns a new SQL with the given $columns added to the $sql->columns.
public
columns(array<string|int, mixed>|string $columns) : Sql
To override current columns use $sql->select().
Parameters
- $columns : array<string|int, mixed>|string
-
param string ...
Return values
Sql —from()
Single table: from('table') or from('table AS t') Multiple tables: from('table1', 'table2') of from(array('table1', 'table2')) from('table1 AS t1', 'table2 t2') of from(array('t1' => 'table1', 'table2 AS t2')).
public
from(string|array<string|int, mixed> $table) : Sql
Parameters
- $table : string|array<string|int, mixed>
Return values
Sql —groupBy()
Returns a new SQL with $this->groupBy set to the given $columns.
public
groupBy(string|array<string|int, mixed> $columns) : Sql
Parameters
- $columns : string|array<string|int, mixed>
Return values
Sql —innerJoin()
Returns a new SQL with the given join appended.
public
innerJoin(string $table, string $on) : Sql
Parameters
- $table : string
- $on : string
Return values
Sql —leftJoin()
Returns a new SQL with the given join appended.
public
leftJoin(string $table, string $on) : Sql
Parameters
- $table : string
- $on : string
Return values
Sql —limit()
Return a new SQL with $this->limit set to the given $limit.
public
limit(int $limit) : Sql
Parameters
- $limit : int
-
The maximum number of records
Return values
Sql —offset()
Return a new SQL with $this->offset set to the given $limit.
public
offset(int $offset) : Sql
Parameters
- $offset : int
-
Skip x records.
Return values
Sql —orderBy()
Returns a new SQL with $this->orderBy set to the given $column => $direction.
public
orderBy(string $column[, string $direction = 'ASC' ]) : Sql
Parameters
- $column : string
- $direction : string = 'ASC'
-
"ASC" or "DESC"
Return values
Sql —orWhere()
Returns a new SQL with the where modified to include the "OR" restriction.
public
orWhere(mixed $restriction) : Sql
Parameters
- $restriction : mixed
Return values
Sql —removeColumn()
Remove a column via its alias.
public
removeColumn(string $alias) : mixed
Parameters
- $alias : string
Return values
mixed —rightJoin()
Returns a new SQL with the given join appended.
public
rightJoin(string $table, string $on) : Sql
Parameters
- $table : string
- $on : string
Return values
Sql —select()
Returns a new SQL with $sql->columns replaced by the given $columns.
public
select(array<string|int, mixed>|string $columns) : Sql
Parameters
- $columns : array<string|int, mixed>|string
-
('AS value' => 'column name') param string ...
Return values
Sql —setFrom()
Set the FROM clause to one or more tables.
public
setFrom(string|array<string|int, mixed> $table) : mixed
Parameters
- $table : string|array<string|int, mixed>
Return values
mixed —setJoin()
Set/Append a join to the FROM.
public
setJoin(string $table[, string $type = ',' ][, string $on = null ]) : mixed
Parameters
- $table : string
- $type : string = ','
-
'INNER JOIN', ',', 'LEFT JOIN'
- $on : string = null
Return values
mixed —where()
Returns a new SQL with the $sql->where set to the given $where.
public
where(string|array<string|int, mixed> $where) : Sql
Parameters
- $where : string|array<string|int, mixed>
Return values
Sql —compose()
Build the SQL string.
private
compose() : string
Return values
string —composeRestrictions()
De inhoud van de WHERE or HAVING samenstellen.
private
composeRestrictions(array<string|int, mixed> $restrictions[, bool $addBraces = false ]) : string
Parameters
- $restrictions : array<string|int, mixed>
- $addBraces : bool = false
Return values
string —extractAlias()
De alias van een kolom of tabel uitzoeken.
private
extractAlias(string &$string[, string $alias = null ][, bool $stripAlias = false ]) : string
Geeft anders de gehele string terug.
Parameters
- $string : string
- $alias : string = null
- $stripAlias : bool = false
Return values
string —join()
Returns a new SQL with the join added to the sql.
private
join(string $type, string $table, string $on) : Sql
Parameters
- $type : string
-
"INNER JOIN", "LEFT JOIN", or "RIGHT JOIN"
- $table : string
-
Name of the table.
- $on : string
-
The ON conditions.