Documentation

Select extends AbstractPredicateClause
in package

Pop

Db

Select class

Constants

BACKTICK = 'BACKTICK': Constants for id quote types
BRACKET = 'BRACKET'
DOUBLE_QUOTE = 'DOUBLE_QUOTE'
MYSQL = 'MYSQL': Constants for database types
NO_QUOTE = 'NO_QUOTE'
PGSQL = 'PGSQL'
SQLITE = 'SQLITE'
SQLSRV = 'SQLSRV'

Properties

$where : PredicateSet|null
$aggregateFunctions : array<string|int, mixed>: Supported standard SQL aggregate functions
$alias : string|null: Alias
$closeQuote : string|null: ID close quote
$dateTimeFunctions : array<string|int, mixed>: Supported standard SQL date-time functions
$db : AbstractAdapter|null: Database object
$dbType : string|null: Database type
$distinct : bool: Distinct keyword
$groupBy : string|null: GROUP BY value
$having : Having|null: HAVING predicate object
$idQuoteType : string: ID quote type
$joins : array<string|int, mixed>: Joins
$limit : mixed: LIMIT value
$mathFunctions : array<string|int, mixed>: Supported standard SQL math functions
$offset : int|null: OFFSET value
$openQuote : string|null: ID open quote
$orderBy : string|null: ORDER BY value
$parameterCount : int: Parameter count
$placeholder : string|null: SQL placeholder
$stringFunctions : array<string|int, mixed>: Supported standard SQL string functions
$table : mixed: Table
$values : array<string|int, mixed>: Values
$wherePredicate : PredicateSet|null: WHERE predicate object

Methods

__construct() : mixed: Constructor
__get() : mixed: Magic method to access $where and $having properties
__toString() : string: Render the SELECT statement
addValue() : AbstractClause: Add a value
andHaving() : Select: Access the HAVING clause with AND
andWhere() : AbstractPredicateClause: Access the WHERE clause with AND
asAlias() : Select: Set table AS alias name
db() : AbstractAdapter|null: Get the current database adapter object (alias method)
decrementParameterCount() : AbstractSql: Decrement parameter count
distinct() : Select: Select distinct
from() : Select: Set from table
fullInnerJoin() : Select: Add a FULL INNER JOIN clause
fullJoin() : Select: Add a FULL JOIN clause
fullOuterJoin() : Select: Add a FULL OUTER JOIN clause
getAlias() : string|null: Get the alias
getCloseQuote() : string|null: Get close quote
getDb() : AbstractAdapter|null: Get the current database adapter object
getDbType() : string|null: Get the current database type
getIdQuoteType() : string: Get the quote ID type
getOpenQuote() : string|null: Get open quote
getParameter() : string: Get parameter placeholder value
getParameterCount() : int: Get parameter count
getPlaceholder() : string|null: Get the SQL placeholder
getTable() : string|null: Get the table
getValue() : mixed: Get a value
getValues() : array<string|int, mixed>: Get the values
groupBy() : Select: Set the GROUP BY value
hasAlias() : bool: Determine if there is an alias
having() : Select: Access the HAVING clause
incrementParameterCount() : AbstractSql: Increment parameter count
innerJoin() : Select: Add a INNER JOIN clause
isMysql() : bool: Determine if the DB type is MySQL
isParameter() : bool: Check if value is parameter placeholder
isPgsql() : bool: Determine if the DB type is PostgreSQL
isSqlite() : bool: Determine if the DB type is SQLite
isSqlsrv() : bool: Determine if the DB type is SQL Server
isSupportedFunction() : bool: Check if value contains a standard SQL supported function
isSupportedFunctionCall() : bool: Check if the value is a single, simple call to a standard SQL supported function, e.g. 'COUNT(*)', 'SUM(total)' or 'MAX(users.id)'
join() : Select: Add a JOIN clause
jsonExtract() : JsonExtract: Create a JSON path extraction expression, usable as a SELECT column value or an orderBy() argument
leftInnerJoin() : Select: Add a LEFT INNER JOIN clause
leftJoin() : Select: Add a LEFT JOIN clause
leftOuterJoin() : Select: Add a LEFT OUTER JOIN clause
limit() : Select: Set the LIMIT value
offset() : Select: Set the OFFSET value
orderBy() : Select: Set the ORDER BY value
orHaving() : Select: Access the HAVING clause with OR
orWhere() : AbstractPredicateClause: Access the WHERE clause with OR
outerJoin() : Select: Add a OUTER JOIN clause
quote() : float|int|string: Quote the value (if it is not a numeric value)
quoteId() : string: Quote the identifier
render() : string: Render the SELECT statement
rightInnerJoin() : Select: Add a RIGHT INNER JOIN clause
rightJoin() : Select: Add a RIGHT JOIN clause
rightOuterJoin() : Select: Add a RIGHT OUTER JOIN clause
setAlias() : AbstractClause: Set the alias
setIdQuoteType() : AbstractSql: Set the quote ID type
setPlaceholder() : AbstractSql: Set the placeholder
setTable() : AbstractClause: Set the table
setValues() : AbstractClause: Set the values
where() : AbstractPredicateClause: Access the WHERE clause
buildColumnsClause() : string: Build the column list portion of the SELECT statement
buildFromClause() : string: Build the FROM clause target (table, aliased table, or nested SELECT)
buildJoinsClause() : string: Build any JOIN clauses
buildLimitOffsetClause() : string: Build the LIMIT/OFFSET clause for non-SQLSRV databases
buildSqlSrvLimitAndOffset() : string: Method to build the SQL Server limit and offset FROM clause target
buildSqlSrvRowNumberPredicate() : string|null: Method to build the SQL Server predicate that filters the ROW_NUMBER() derived table
buildSqlSrvTopClause() : string: Method to build the SQL Server TOP clause, for a limit that carries no offset
getLimitAndOffset() : array<string|int, mixed>: Method to get the limit and offset
init() : void: Initialize SQL object
initQuoteType() : void: Initialize quite type
quoteByColumn() : string: Quote a single GROUP BY / ORDER BY column

BACKTICK

Constants for id quote types


    public
        mixed
    BACKTICK
    = 'BACKTICK'

BRACKET


    public
        mixed
    BRACKET
    = 'BRACKET'

DOUBLE_QUOTE


    public
        mixed
    DOUBLE_QUOTE
    = 'DOUBLE_QUOTE'

MYSQL

Constants for database types


    public
        mixed
    MYSQL
    = 'MYSQL'

NO_QUOTE


    public
        mixed
    NO_QUOTE
    = 'NO_QUOTE'

PGSQL


    public
        mixed
    PGSQL
    = 'PGSQL'

SQLITE


    public
        mixed
    SQLITE
    = 'SQLITE'

SQLSRV


    public
        mixed
    SQLSRV
    = 'SQLSRV'

$where read-only


        public
            PredicateSet|null
    $where

WHERE predicate object (lazily created by subclasses)

$aggregateFunctions

Supported standard SQL aggregate functions


        protected
        static    array<string|int, mixed>
    $aggregateFunctions
     = ['AVG', 'COUNT', 'MAX', 'MIN', 'SUM']

$alias

Alias


        protected
            string|null
    $alias
     = null

$closeQuote

ID close quote


        protected
            string|null
    $closeQuote
     = null

$dateTimeFunctions

Supported standard SQL date-time functions


        protected
        static    array<string|int, mixed>
    $dateTimeFunctions
     = ['CURRENT_DATE', 'CURRENT_TIMESTAMP', 'CURRENT_TIME', 'CURDATE', 'CURTIME', 'DATE', 'DATETIME', 'DAY', 'EXTRACT', 'GETDATE', 'HOUR', 'LOCALTIME', 'LOCALTIMESTAMP', 'MINUTE', 'MONTH', 'NOW', 'SECOND', 'TIME', 'TIMEDIFF', 'TIMESTAMP', 'UNIX_TIMESTAMP', 'YEAR']

$db

Database object


        protected
            AbstractAdapter|null
    $db
     = null

$dbType

Database type


        protected
            string|null
    $dbType
     = null

$distinct

Distinct keyword


        protected
            bool
    $distinct
     = false

$groupBy

GROUP BY value


        protected
            string|null
    $groupBy
     = null

$having

HAVING predicate object


        protected
            Having|null
    $having
     = null

$idQuoteType

ID quote type


        protected
            string
    $idQuoteType
     = 'NO_QUOTE'

$joins

Joins


        protected
            array<string|int, mixed>
    $joins
     = []

$limit

LIMIT value


        protected
            mixed
    $limit
     = null

$mathFunctions

Supported standard SQL math functions


        protected
        static    array<string|int, mixed>
    $mathFunctions
     = ['ABS', 'RAND', 'SQRT', 'POW', 'POWER', 'EXP', 'LN', 'LOG', 'LOG10', 'GREATEST', 'LEAST', 'DIV', 'MOD', 'ROUND', 'TRUNC', 'CEIL', 'CEILING', 'FLOOR', 'COS', 'ACOS', 'ACOSH', 'SIN', 'SINH', 'ASIN', 'ASINH', 'TAN', 'TANH', 'ATANH', 'ATAN2']

$offset

OFFSET value


        protected
            int|null
    $offset
     = null

$openQuote

ID open quote


        protected
            string|null
    $openQuote
     = null

$orderBy

ORDER BY value


        protected
            string|null
    $orderBy
     = null

$parameterCount

Parameter count


        protected
            int
    $parameterCount
     = 0

$placeholder

SQL placeholder


        protected
            string|null
    $placeholder
     = null

$stringFunctions

Supported standard SQL string functions


        protected
        static    array<string|int, mixed>
    $stringFunctions
     = ['CONCAT', 'FORMAT', 'INSTR', 'LCASE', 'LEFT', 'LENGTH', 'LOCATE', 'LOWER', 'LPAD', 'LTRIM', 'POSITION', 'QUOTE', 'REGEXP', 'REPEAT', 'REPLACE', 'REVERSE', 'RIGHT', 'RPAD', 'RTRIM', 'SPACE', 'STRCMP', 'SUBSTRING', 'SUBSTR', 'TRIM', 'UCASE', 'UPPER']

$table

Table


        protected
            mixed
    $table
     = null

$values

Values


        protected
            array<string|int, mixed>
    $values
     = []

$wherePredicate

WHERE predicate object


        protected
            PredicateSet|null
    $wherePredicate
     = null

__construct()

Constructor


    public
                    __construct(AbstractAdapter $db) : mixed

Instantiate the SQL object

Parameters

$db : AbstractAdapter

__get()

Magic method to access $where and $having properties


    public
                    __get(string $name) : mixed

Parameters

$name : string

__toString()

Render the SELECT statement


    public
                    __toString() : string

Return values

string

addValue()

Add a value


    public
                    addValue(mixed $value[, string|null $name = null ]) : AbstractClause

Parameters

$value : mixed
$name : string|null = null

Return values

AbstractClause

andHaving()

Access the HAVING clause with AND


    public
                    andHaving([mixed $having = null ]) : Select

Parameters

$having : mixed = null

Return values

Select

andWhere()

Access the WHERE clause with AND


    public
                    andWhere([mixed $where = null ]) : AbstractPredicateClause

Parameters

$where : mixed = null

Return values

AbstractPredicateClause

asAlias()

Set table AS alias name


    public
                    asAlias(mixed $table) : Select

Parameters

$table : mixed

Return values

Select

db()

Get the current database adapter object (alias method)


    public
                    db() : AbstractAdapter|null

Return values

AbstractAdapter|null

decrementParameterCount()

Decrement parameter count


    public
                    decrementParameterCount() : AbstractSql

Return values

AbstractSql

distinct()

Select distinct


    public
                    distinct([bool $distinct = true ]) : Select

Parameters

$distinct : bool = true

Return values

Select

from()

Set from table


    public
                    from(mixed $table) : Select

Parameters

$table : mixed

Return values

Select

fullInnerJoin()

Add a FULL INNER JOIN clause


    public
                    fullInnerJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

fullJoin()

Add a FULL JOIN clause


    public
                    fullJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

fullOuterJoin()

Add a FULL OUTER JOIN clause


    public
                    fullOuterJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

getAlias()

Get the alias


    public
                    getAlias() : string|null

Return values

string|null

getCloseQuote()

Get close quote


    public
                    getCloseQuote() : string|null

Return values

string|null

getDb()

Get the current database adapter object


    public
                    getDb() : AbstractAdapter|null

Return values

AbstractAdapter|null

getDbType()

Get the current database type


    public
                    getDbType() : string|null

Return values

string|null

getIdQuoteType()

Get the quote ID type


    public
                    getIdQuoteType() : string

Return values

string

getOpenQuote()

Get open quote


    public
                    getOpenQuote() : string|null

Return values

string|null

getParameter()

Get parameter placeholder value


    public
                    getParameter(mixed $value[, string|null $column = null ]) : string

Parameters

$value : mixed
$column : string|null = null

Return values

string

getParameterCount()

Get parameter count


    public
                    getParameterCount() : int

Return values

int

getPlaceholder()

Get the SQL placeholder


    public
                    getPlaceholder() : string|null

Return values

string|null

getTable()

Get the table


    public
                    getTable() : string|null

Return values

string|null

getValue()

Get a value


    public
                    getValue(string $name) : mixed

Parameters

$name : string

getValues()

Get the values


    public
                    getValues() : array<string|int, mixed>

Return values

array<string|int, mixed>

groupBy()

Set the GROUP BY value


    public
                    groupBy(mixed $by) : Select

Parameters

$by : mixed

Return values

Select

hasAlias()

Determine if there is an alias


    public
                    hasAlias() : bool

Return values

bool

having()

Access the HAVING clause


    public
                    having([mixed $having = null ]) : Select

Parameters

$having : mixed = null

Return values

Select

incrementParameterCount()

Increment parameter count


    public
                    incrementParameterCount() : AbstractSql

Return values

AbstractSql

innerJoin()

Add a INNER JOIN clause


    public
                    innerJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

isMysql()

Determine if the DB type is MySQL


    public
                    isMysql() : bool

Return values

bool

isParameter()

Check if value is parameter placeholder


    public
                    isParameter(mixed $value[, string|null $column = null ]) : bool

Parameters

$value : mixed
$column : string|null = null

Return values

bool

isPgsql()

Determine if the DB type is PostgreSQL


    public
                    isPgsql() : bool

Return values

bool

isSqlite()

Determine if the DB type is SQLite


    public
                    isSqlite() : bool

Return values

bool

isSqlsrv()

Determine if the DB type is SQL Server


    public
                    isSqlsrv() : bool

Return values

bool

isSupportedFunction()

Check if value contains a standard SQL supported function


    public
            static        isSupportedFunction(mixed $value) : bool

Parameters

$value : mixed

Return values

bool

isSupportedFunctionCall()

Check if the value is a single, simple call to a standard SQL supported function, e.g. 'COUNT(*)', 'SUM(total)' or 'MAX(users.id)'


    public
            static        isSupportedFunctionCall(mixed $value) : bool

The argument list is deliberately restricted to identifier characters, digits, '*', '.', ',' and whitespace, and the whole value must be nothing but that one call. That keeps anything that could carry additional SQL (quotes, operators, comment markers, a nested statement) out of the "render me verbatim" path.

Parameters

$value : mixed

Return values

bool

join()

Add a JOIN clause


    public
                    join(mixed $foreignTable, array<string|int, mixed> $columns[, string $join = 'JOIN' ]) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>
$join : string = 'JOIN'

Return values

Select

jsonExtract()

Create a JSON path extraction expression, usable as a SELECT column value or an orderBy() argument


    public
                    jsonExtract(string $column, string $path) : JsonExtract

Parameters

$column : string
$path : string

Return values

JsonExtract

leftInnerJoin()

Add a LEFT INNER JOIN clause


    public
                    leftInnerJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

leftJoin()

Add a LEFT JOIN clause


    public
                    leftJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

leftOuterJoin()

Add a LEFT OUTER JOIN clause


    public
                    leftOuterJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

limit()

Set the LIMIT value


    public
                    limit(int $limit) : Select

Parameters

$limit : int

Return values

Select

offset()

Set the OFFSET value


    public
                    offset(int $offset) : Select

Parameters

$offset : int

Return values

Select

orderBy()

Set the ORDER BY value


    public
                    orderBy(mixed $by[, string $order = 'ASC' ]) : Select

Parameters

$by : mixed
$order : string = 'ASC'

Return values

Select

orHaving()

Access the HAVING clause with OR


    public
                    orHaving([mixed $having = null ]) : Select

Parameters

$having : mixed = null

Return values

Select

orWhere()

Access the WHERE clause with OR


    public
                    orWhere([mixed $where = null ]) : AbstractPredicateClause

Parameters

$where : mixed = null

Return values

AbstractPredicateClause

outerJoin()

Add a OUTER JOIN clause


    public
                    outerJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

quote()

Quote the value (if it is not a numeric value)


    public
                    quote([mixed $value = null ][, bool $force = false ]) : float|int|string

Parameters

$value : mixed = null
$force : bool = false

Return values

float|int|string

quoteId()

Quote the identifier


    public
                    quoteId(string $identifier) : string

A supported SQL function call is an expression rather than an identifier, so it is passed through untouched - quoting it would produce a bogus identifier such as COUNT(*), which errors on MySQL/PostgreSQL and silently matches nothing on SQLite.

Parameters

$identifier : string

Return values

string

render()

Render the SELECT statement


    public
                    render() : string

Return values

string

rightInnerJoin()

Add a RIGHT INNER JOIN clause


    public
                    rightInnerJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

rightJoin()

Add a RIGHT JOIN clause


    public
                    rightJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

rightOuterJoin()

Add a RIGHT OUTER JOIN clause


    public
                    rightOuterJoin(mixed $foreignTable, array<string|int, mixed> $columns) : Select

Parameters

$foreignTable : mixed
$columns : array<string|int, mixed>

Return values

Select

setAlias()

Set the alias


    public
                    setAlias(string $alias) : AbstractClause

Parameters

$alias : string

Return values

AbstractClause

setIdQuoteType()

Set the quote ID type


    public
                    setIdQuoteType([string $type = self::NO_QUOTE ]) : AbstractSql

Parameters

$type : string = self::NO_QUOTE

Return values

AbstractSql

setPlaceholder()

Set the placeholder


    public
                    setPlaceholder(string $placeholder) : AbstractSql

Parameters

$placeholder : string

Return values

AbstractSql

setTable()

Set the table


    public
                    setTable(mixed $table) : AbstractClause

Parameters

$table : mixed

Return values

AbstractClause

setValues()

Set the values


    public
                    setValues(array<string|int, mixed> $values) : AbstractClause

Parameters

$values : array<string|int, mixed>

Return values

AbstractClause

where()

Access the WHERE clause


    public
                    where([mixed $where = null ]) : AbstractPredicateClause

Parameters

$where : mixed = null

Return values

AbstractPredicateClause

buildColumnsClause()

Build the column list portion of the SELECT statement


    protected
                    buildColumnsClause() : string

Return values

string

buildFromClause()

Build the FROM clause target (table, aliased table, or nested SELECT)


    protected
                    buildFromClause() : string

Return values

string

buildJoinsClause()

Build any JOIN clauses


    protected
                    buildJoinsClause() : string

Return values

string

buildLimitOffsetClause()

Build the LIMIT/OFFSET clause for non-SQLSRV databases


    protected
                    buildLimitOffsetClause() : string

Return values

string

buildSqlSrvLimitAndOffset()

Method to build the SQL Server limit and offset FROM clause target


    protected
                    buildSqlSrvLimitAndOffset() : string

With an offset, the table is wrapped in a derived table carrying a ROW_NUMBER() column that buildSqlSrvRowNumberPredicate() then filters on. Without one, the row cap is a TOP clause on the SELECT itself (see buildSqlSrvTopClause()), so the table is used as is. This method is side effect free.

Return values

string

buildSqlSrvRowNumberPredicate()

Method to build the SQL Server predicate that filters the ROW_NUMBER() derived table


    protected
                    buildSqlSrvRowNumberPredicate() : string|null

Returned as its own predicate set so that it is combined with the user's WHERE clause by a top level AND. Adding it to the user's predicate set would inherit the conjunction of the predicate before it, turning a WHERE of 'a OR b' into 'a OR b OR rownumber', which drops the limit entirely.

Return values

string|null

buildSqlSrvTopClause()

Method to build the SQL Server TOP clause, for a limit that carries no offset


    protected
                    buildSqlSrvTopClause() : string

Return values

string

getLimitAndOffset()

Method to get the limit and offset


    protected
                    getLimitAndOffset() : array<string|int, mixed>

Return values

array<string|int, mixed>

init()

Initialize SQL object


    protected
                    init(string $adapter) : void

Parameters

$adapter : string

initQuoteType()

Initialize quite type


    protected
                    initQuoteType() : void

quoteByColumn()

Quote a single GROUP BY / ORDER BY column


    protected
                    quoteByColumn(mixed $column) : string

A JsonExtract value object already carries its own fully-rendered, dialect-specific extraction SQL, so it is embedded verbatim - passing it through trim()/quoteId() would either error or wrap the whole expression in identifier quotes, producing invalid SQL.

Parameters

$column : mixed

Return values

string

Select extends AbstractPredicateClause in package Pop Db

Tags

Table of Contents

Constants

Properties

Methods

Constants

BACKTICK

BRACKET

DOUBLE_QUOTE

MYSQL

NO_QUOTE

PGSQL

SQLITE

SQLSRV

Properties

$where read-only

$aggregateFunctions

$alias

$closeQuote

$dateTimeFunctions

$db

$dbType

$distinct

$groupBy

$having

$idQuoteType

$joins

$limit

$mathFunctions

$offset

$openQuote

$orderBy

$parameterCount

$placeholder

$stringFunctions

$table

$values

$wherePredicate

Methods

__construct()

Parameters

__get()

Parameters

Tags

__toString()

Tags

Return values

addValue()

Parameters

Return values

andHaving()

Parameters

Return values

andWhere()

Parameters

Return values

asAlias()

Parameters

Return values

db()

Return values

decrementParameterCount()

Return values

distinct()

Parameters

Return values

from()

Parameters

Return values

fullInnerJoin()

Parameters

Return values

fullJoin()

Parameters

Return values

fullOuterJoin()

Parameters

Return values

getAlias()

Select extends AbstractPredicateClause
in package

Pop

Db