CSqlComposer
in package
implements
ISqlComposer
Final class for compose a dialect SQL query (MySQL, PostgresSQL, etc.).
For greater maintainability of the code it is recommended to always use this class instead of its base classes.
Table of Contents
Interfaces
- ISqlComposer
- Interface for SQL query composers Defines the contract for building SQL queries across different DBMS
Properties
- $composer : mixed
Methods
- __call() : mixed
- Magic method to delegate undefined method calls to the internal composer
- __construct() : mixed
- Constructor
- __toString() : string
- Convert the SQL composer to a string (the SQL query)
- AndWhere() : CSqlComposerBase
- Add "AND" WHERE condition
- As() : string
- Create AS alias for SELECT item
- Clear() : void
- Reset all internal data
- From() : ISqlComposer
- Set the main table for the query
- FromList() : ISqlComposer
- Set multiple tables for the query
- GetItems() : string
- Get the current items/fields in SELECT
- GetTable() : string
- Get table name by index
- GetTypes() : array<string|int, mixed>
- Get types for prepared statement placeholders
- GetValues() : array<string|int, mixed>
- Get values for prepared statement placeholders
- GroupBy() : ISqlComposer
- Set GROUP BY clause
- Having() : ISqlComposer
- Set HAVING clause
- InnerJoin() : ISqlComposer
- Add INNER JOIN clause
- Join() : ISqlComposer
- Add JOIN clause
- JoinItems() : string
- DBMS-specific: Concatenate multiple items
- LeftJoin() : ISqlComposer
- Add LEFT JOIN clause
- LowerItem() : string
- DBMS-specific: Convert item to lowercase
- OrderBy() : ISqlComposer
- Set ORDER BY clause
- OrWhere() : ISqlComposer
- Add OR WHERE condition
- Paging() : ISqlComposer
- Set pagination parameters
- Prepare() : string
- Build and return the SQL query string
- RightJoin() : ISqlComposer
- Add RIGHT JOIN clause
- Select() : ISqlComposer
- Set the fields of the query
- SelectList() : ISqlComposer
- Set the fields of the query from array
- SetTypes() : void
- Set types for ? operator in the WHERE conditions
- SetValues() : void
- Set values for ? operator in the WHERE conditions
- UpperItem() : string
- DBMS-specific: Convert item to uppercase
- Where() : ISqlComposer
- Add WHERE conditions
- WhereBetween() : ISqlComposer
- Add WHERE BETWEEN condition
- WhereIn() : ISqlComposer
- Add WHERE IN condition
- WhereNotBetween() : ISqlComposer
- Add WHERE NOT BETWEEN condition
- WhereNotIn() : ISqlComposer
- Add WHERE NOT IN condition
- WhereNotNull() : ISqlComposer
- Add WHERE IS NOT NULL condition
- WhereNull() : ISqlComposer
- Add WHERE IS NULL condition
Properties
$composer
protected
mixed
$composer
The DBMS-specific composer instance
Methods
__call()
Magic method to delegate undefined method calls to the internal composer
public
__call(string $call_method, array<string|int, mixed> $args) : mixed
Parameters
- $call_method : string
-
The method name being called
- $args : array<string|int, mixed>
-
The arguments passed to the method
Return values
mixed —The result from the internal composer method
__construct()
Constructor
public
__construct(string $dbms_type[, string|null $items = '*' ][, string|null $table = null ][, string|null $as = null ][, string|null $conditions = null ][, array<string|int, mixed>|int|string $values = [] ][, array<string|int, mixed>|int|string $types = [] ]) : mixed
Parameters
- $dbms_type : string
-
The DBMS type ('mysql', 'postgresql', 'oracle', 'mssql', 'sqlserver')
- $items : string|null = '*'
-
The columns to select (default '*')
- $table : string|null = null
-
The table name
- $as : string|null = null
-
The table alias
- $conditions : string|null = null
-
WHERE conditions
- $values : array<string|int, mixed>|int|string = []
-
Bind parameter values
- $types : array<string|int, mixed>|int|string = []
-
Bind parameter types
Tags
__toString()
Convert the SQL composer to a string (the SQL query)
public
__toString() : string
Return values
string —the string containing the query made up of all the parameters previously set
AndWhere()
Add "AND" WHERE condition
public
AndWhere(string $conditions[, array<string|int, mixed>|int|string $values = [] ][, array<string|int, mixed>|int|string $types = [] ]) : CSqlComposerBase
Parameters
- $conditions : string
-
WHERE conditions
- $values : array<string|int, mixed>|int|string = []
-
list of values in WHERE conditions (replaced by '?')
- $types : array<string|int, mixed>|int|string = []
-
list of types mapped one to one with values (it depends on used DBMS)
Return values
CSqlComposerBase —this object
As()
Create AS alias for SELECT item
public
As(string $item) : string
Parameters
- $item : string
Return values
stringClear()
Reset all internal data
public
Clear() : void
From()
Set the main table for the query
public
From(string $table[, string|null $as = null ]) : ISqlComposer
Parameters
- $table : string
-
name of table
- $as : string|null = null
-
alias table name
Return values
ISqlComposerFromList()
Set multiple tables for the query
public
FromList(array<string|int, mixed> $tables) : ISqlComposer
Parameters
- $tables : array<string|int, mixed>
-
list of table names
Return values
ISqlComposerGetItems()
Get the current items/fields in SELECT
public
GetItems() : string
Return values
stringGetTable()
Get table name by index
public
GetTable([int $index = 0 ]) : string
Parameters
- $index : int = 0
Return values
stringGetTypes()
Get types for prepared statement placeholders
public
GetTypes() : array<string|int, mixed>
Return values
array<string|int, mixed>GetValues()
Get values for prepared statement placeholders
public
GetValues() : array<string|int, mixed>
Return values
array<string|int, mixed>GroupBy()
Set GROUP BY clause
public
GroupBy(string $group_by) : ISqlComposer
Parameters
- $group_by : string
-
list of field names to group by
Return values
ISqlComposerHaving()
Set HAVING clause
public
Having(string $having) : ISqlComposer
Parameters
- $having : string
-
conditions for group by having
Return values
ISqlComposerInnerJoin()
Add INNER JOIN clause
public
InnerJoin(string $table, string $on_condition[, string|null $as = null ]) : ISqlComposer
Parameters
- $table : string
-
name of table
- $on_condition : string
-
condition for crossing with a starting table
- $as : string|null = null
-
alias table name
Return values
ISqlComposerJoin()
Add JOIN clause
public
Join(string $table, string $on_condition[, string|null $as = null ]) : ISqlComposer
Parameters
- $table : string
-
name of table
- $on_condition : string
-
condition for crossing with a starting table
- $as : string|null = null
-
alias table name
Return values
ISqlComposerJoinItems()
DBMS-specific: Concatenate multiple items
public
JoinItems(array<string|int, mixed> $items, string $new_name) : string
Parameters
- $items : array<string|int, mixed>
-
list of items to concatenate
- $new_name : string
-
unused parameter for backward compatibility
Return values
string —SQL concatenation expression
LeftJoin()
Add LEFT JOIN clause
public
LeftJoin(string $table, string $on_condition[, string|null $as = null ]) : ISqlComposer
Parameters
- $table : string
-
name of table
- $on_condition : string
-
condition for crossing with a starting table
- $as : string|null = null
-
alias table name
Return values
ISqlComposerLowerItem()
DBMS-specific: Convert item to lowercase
public
LowerItem(string $item[, string $new_name = '' ]) : string
Parameters
- $item : string
- $new_name : string = ''
Return values
string —SQL LOWER expression
OrderBy()
Set ORDER BY clause
public
OrderBy(array<string|int, mixed> $order_by) : ISqlComposer
Parameters
- $order_by : array<string|int, mixed>
-
list of field names to order by
Return values
ISqlComposerOrWhere()
Add OR WHERE condition
public
OrWhere(string $conditions[, array<string|int, mixed>|int|string $values = [] ][, array<string|int, mixed>|int|string $types = [] ]) : ISqlComposer
Parameters
- $conditions : string
-
WHERE conditions
- $values : array<string|int, mixed>|int|string = []
-
list of values in WHERE conditions
- $types : array<string|int, mixed>|int|string = []
-
list of types mapped one to one with values
Return values
ISqlComposerPaging()
Set pagination parameters
public
Paging(int $page[, int $elements_per_page = 20 ]) : ISqlComposer
Parameters
- $page : int
-
page to read (starting from 1)
- $elements_per_page : int = 20
-
maximum total of elements that make up a page
Return values
ISqlComposerPrepare()
Build and return the SQL query string
public
Prepare() : string
Return values
string —the composed SQL query
RightJoin()
Add RIGHT JOIN clause
public
RightJoin(string $table, string $on_condition[, string|null $as = null ]) : ISqlComposer
Parameters
- $table : string
-
name of table
- $on_condition : string
-
condition for crossing with a starting table
- $as : string|null = null
-
alias table name
Return values
ISqlComposerSelect()
Set the fields of the query
public
Select(string $items) : ISqlComposer
Parameters
- $items : string
-
list of db fields separated by a comma
Return values
ISqlComposerSelectList()
Set the fields of the query from array
public
SelectList(array<string|int, mixed> $items) : ISqlComposer
Parameters
- $items : array<string|int, mixed>
-
array contains the list of db fields
Return values
ISqlComposerSetTypes()
Set types for ? operator in the WHERE conditions
public
SetTypes(array<string|int, mixed> $types) : void
Parameters
- $types : array<string|int, mixed>
-
list of types for ? operator in the WHERE conditions
SetValues()
Set values for ? operator in the WHERE conditions
public
SetValues(array<string|int, mixed> $values) : void
Parameters
- $values : array<string|int, mixed>
-
list of values for ? operator in the WHERE conditions
UpperItem()
DBMS-specific: Convert item to uppercase
public
UpperItem(string $item[, string $new_name = '' ]) : string
Parameters
- $item : string
- $new_name : string = ''
Return values
string —SQL UPPER expression
Where()
Add WHERE conditions
public
Where(string $conditions[, array<string|int, mixed>|int|string $values = [] ][, array<string|int, mixed>|int|string $types = [] ]) : ISqlComposer
Parameters
- $conditions : string
-
WHERE conditions
- $values : array<string|int, mixed>|int|string = []
-
list of values in WHERE conditions (replaced by '?')
- $types : array<string|int, mixed>|int|string = []
-
list of types mapped one to one with values
Return values
ISqlComposerWhereBetween()
Add WHERE BETWEEN condition
public
WhereBetween(string $column, mixed $min, mixed $max[, string $type = '' ]) : ISqlComposer
Parameters
- $column : string
-
column name
- $min : mixed
-
minimum value
- $max : mixed
-
maximum value
- $type : string = ''
-
type of values
Return values
ISqlComposerWhereIn()
Add WHERE IN condition
public
WhereIn(string $column, array<string|int, mixed> $values[, string $type = '' ]) : ISqlComposer
Parameters
- $column : string
-
column name
- $values : array<string|int, mixed>
-
list of values for IN clause
- $type : string = ''
-
type of values
Return values
ISqlComposerWhereNotBetween()
Add WHERE NOT BETWEEN condition
public
WhereNotBetween(string $column, mixed $min, mixed $max[, string $type = '' ]) : ISqlComposer
Parameters
- $column : string
-
column name
- $min : mixed
-
minimum value
- $max : mixed
-
maximum value
- $type : string = ''
-
type of values
Return values
ISqlComposerWhereNotIn()
Add WHERE NOT IN condition
public
WhereNotIn(string $column, array<string|int, mixed> $values[, string $type = '' ]) : ISqlComposer
Parameters
- $column : string
-
column name
- $values : array<string|int, mixed>
-
list of values for NOT IN clause
- $type : string = ''
-
type of values
Return values
ISqlComposerWhereNotNull()
Add WHERE IS NOT NULL condition
public
WhereNotNull(string $column) : ISqlComposer
Parameters
- $column : string
-
column name
Return values
ISqlComposerWhereNull()
Add WHERE IS NULL condition
public
WhereNull(string $column) : ISqlComposer
Parameters
- $column : string
-
column name