SELECT Query
| package | salt\sql\queries |
|---|---|
| inherited_from | \salt\BaseQuery |
__construct(\salt\Base $obj, boolean $withField = FALSE)
| inherited_from | \salt\BaseQuery::__construct() |
|---|
\salt\Baseobject for query creation
booleanTRUE for load all the fields
__construct(\salt\Base $obj)
\salt\Basethe object used for retrieve metadata.
__get(string $fieldName) : \salt\SqlExpr
stringname of the field
\salt\SqlExprthe field as an SqlExprabsoluteDatabase(string $database) : static
Can be used to make join between tables of different database in same schema
stringthe name of the other database
staticcurrent querydisableIfEmpty(mixed $list)
This can be used if the query contains an IN where condition with an empty array : Executing the query result in an exception, but we can use this function for return an empty result without exception :
$q->whereAnd('ids', 'IN', $values); // will produce a bad where clause : "ids IN ()" if $values is empty
$q->disabledIfEmpty($values);
$db->execQuery($q); // valid, will not execute query, so not throw exception
mixed
escapeName(string $name) : string
stringa SQL name : database, table, field, alias, etc...
stringSQL escaped text with backquote.getBindingClass() : string
stringthe class of object the query work ongetBinds(string $source = \salt\ClauseType::ALL) : array<mixed,mixed[]>
string(Optional) ClauseType or specific text for restrict returned binds to specified source
array<mixed,mixed[]>list of binds : array of array('value' => ..., 'type' => ...)getPaginationBinds(\salt\Pagination $pagination) : array<mixed,mixed[]>
\salt\Paginationthe Pagination object
array<mixed,mixed[]>binds for the Pagination : array of bindName => array of ('value' => ..., 'type' => ...)getSelect(string $alias) : \salt\SqlExpr
Example :
$query->select(SqlExpr::_count('*'), 'nb');
$query->orderDesc($query->getSelect('nb'));stringname of an alias
\salt\SqlExprthe expression for this aliasgetSelectFields() : array<mixed,string>
array<mixed,string>array of all fields or aliases selected by the querygetSubQuery() : \salt\Query
\salt\Querya query on the same table with the same alias for using in whereAndQuery or whereOrQuerygroupBy(string|\salt\SqlExpr $fieldOrExpr)
string\salt\SqlExprfield name to group by
initResults(\salt\Pagination $pagination= NULL, string $count= NULL) : \salt\DBResult
\salt\Paginationpagination object to update
stringtotal number of elements. Set on pagination
\salt\DBResultwith selected columns populatedisEnabled() : boolean
booleanTRUE if the query can be executed, FALSE otherwiseisPrivateBinds() : boolean
booleanTRUE if privateBinds has been calledjoin(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr, string $type = 'INNER')
..
\salt\Querythe other query to join with
string\salt\SqlExprfield of ON clause
stringoperator of ON clause
mixed\salt\SqlExprvalue of ON clause
stringtype of join: 'INNER', 'OUTER', etc...
\salt\SaltException |
if this join already exists |
|---|
joinOnAnd(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr)
\salt\Querythe other query for retrieve the join clause
string\salt\SqlExprfield of ON clause
stringoperator of ON clause
mixed\salt\SqlExprvalue of ON clause
\salt\SaltException |
if join don't exists |
|---|
joinOnAndQuery(\salt\Query $other, \salt\Query $whereQuery)
\salt\Querythe other query for retrieve the join clause
\salt\Querythe query to add in ON clause
joinOnOr(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr)
\salt\Querythe other query for retrieve the join clause
string\salt\SqlExprfield of ON clause
stringoperator of ON clause
mixed\salt\SqlExprvalue of ON clause
\salt\SaltException |
if join don't exists |
|---|
joinOnOrQuery(\salt\Query $other, \salt\Query $whereQuery)
\salt\Querythe other query for retrieve the join clause
\salt\Querythe query to add in ON clause
joinSelect(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr, string $type = 'INNER')
.. ) tX ON ...
\salt\Querythe other query to join with
string\salt\SqlExprfield of ON clause
stringoperator of ON clause
mixed\salt\SqlExprvalue of ON clause
stringtype of join: 'INNER', 'OUTER', etc...
\salt\SaltException |
if this join already exists |
|---|
orderAsc(string|\salt\SqlExpr $fieldOrExpr)
string\salt\SqlExprthe field to order by ASC
orderDesc(string|\salt\SqlExpr $fieldOrExpr)
string\salt\SqlExprthe field to order by DESC
privateBinds(boolean $privateBinds = TRUE) : \salt\SqlBindField
boolean(Optional, TRUE) hide binds values in debug queries
\salt\SqlBindFieldthe current objectselect(\salt\SqlExpr $expr, string $as = NULL)
\salt\SqlExprexpression to add in select clause, of any type
stringalias for the expression
\salt\SaltException |
|---|
selectField(string|\salt\SqlExpr $field, string $as = NULL)
string\salt\SqlExpra field name to add in select clause. A SqlExpr can also be used if it's a SqlExpr of type FIELD
stringalias if needed
selectFields(array<mixed,string> $fields)
array<mixed,string>list of fields name to add in select clause
toCountQuery() : \salt\CountQuery
Have to be overrided by child classes if needed
| inherited_from | \salt\BaseQuery::toCountQuery() |
|---|
\salt\CountQuerythe query for count objects of this querytoCountQuery() : \salt\CountQuery
Have to be overrided by child classes if needed
\salt\SaltException |
if called on BaseQuery instance |
|---|
\salt\CountQuerythe SQL query for count querytoSQL() : string
stringthe memoized SQL textwhereAnd(string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExp $valueOrExpr)
string\salt\SqlExprfieldName or SqlExpr
stringoperator to use : '=', 'LIKE', 'IN', etc...
mixed\salt\SqlExpvalue or SqlExpr
whereAndExists(\salt\Query $otherQuery, boolean $exists = TRUE)
\salt\Query
booleanif FALSE : AND NOT EXISTS (SELECT 1 $otherQuery)
whereAndObject(\salt\Base|array<mixed,\salt\Base> $objects)
\salt\Basearray<mixed,\salt\Base>Base object or array of Base objects. Objects have to be of the same type than the query
whereAndQuery(\salt\Query $subQuery)
\salt\Queryhave to be a query retrieved by getSubQuery()
whereOr(string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExp $valueOrExpr)
string\salt\SqlExprfieldName or SqlExpr
stringoperator to use : '=', 'LIKE', 'IN', etc...
mixed\salt\SqlExpvalue or SqlExpr
whereOrExists(\salt\Query $otherQuery, boolean $exists = TRUE)
\salt\Query
booleanif FALSE : OR NOT EXISTS (SELECT 1 $otherQuery)
whereOrObject(\salt\Base|array<mixed,\salt\Base> $objects)
\salt\Basearray<mixed,\salt\Base>Base object or array of Base objects. Objects have to be of the same type than the query
whereOrQuery(\salt\Query $subQuery)
\salt\Queryhave to be a query retrieved by getSubQuery()
addBind(mixed $value, integer $type, string $source = \salt\ClauseType::ALL) : string
mixedvalue of bind
integer(FieldType) type of the field
string(Optional) ClauseType or other text : clause where the bind will be used, if we know it
stringthe bind namebuildGroupClause() : string
stringSQL text for GROUP BY partbuildJoinClause() : string
stringSQL text query with JOIN partbuildOrderClause() : string
stringSQL text for ORDER BY partbuildSQL() : string
stringthe SQL textbuildSelectClause() : string
stringSQL text of all retrieved fieldsbuildWhereClause() : string
stringSQL text query with WHERE partlinkBindsOf(\salt\SqlBindField $other, string $source= \salt\ClauseType::ALL, string $otherSource= \salt\ClauseType::ALL)
\salt\SqlBindFieldthe other SqlBindField to link
stringclause usage of the SqlBindField if we known it
stringrestrict link to this source in $other
\salt\SaltException |
if this SqlBindField is already resolved |
|---|
removeBinds(string $source)
stringClauseType of specific text
resolveFieldName(string $source, mixed|\salt\SqlExpr $fieldNameOrValue, mixed|\salt\SqlExpr $fieldOfValue = NULL) : string | array<mixed,string>
stringcaller of resolve : ClauseType or specific text
mixed\salt\SqlExpra FieldName (string) or SqlExpr or potential value (int, string, array) if 3rd argument is provided
mixed\salt\SqlExpra Field related to the value. Can be a SqlExpr or a string. Can be NULL if 2nd argument is a string fieldName
stringarray<mixed,string>absolute field name (alias.fieldname) or string with bind values or array of bind values if 2nd argument is an array of valuesresolveTable() : string
stringtable name, with or without alias (depends on noAlias)toBaseSQL() : string
stringSQL text query with common parts onlyaddJoin(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr, string $type= 'INNER', string $table, boolean $withOtherDatas= TRUE)
\salt\Querythe other query to join with
string\salt\SqlExprfield of ON clause
stringoperator of ON clause
mixed\salt\SqlExprvalue of ON clause
stringtype of join: 'INNER', 'OUTER', etc...
stringobject to join with : table name or subquery, with alias
booleanTRUE if we have to add fields, where, group by, order clause to main query
\salt\SaltException |
if this join already exists |
|---|
addJoinOn(string $type, \salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr)
stringAND|OR
\salt\Querythe other query for retrieve the join clause
string\salt\SqlExprfield of ON clause
stringoperator of ON clause
mixed\salt\SqlExprvalue of ON clause
\salt\SaltException |
if join don't exists |
|---|
addJoinOnClause(\salt\Query $other, string $type, string $onClause)
\salt\Querythe other query for retrieve the join clause
stringtype of ON clause : AND|OR
stringON clause
addJoinOnQuery(string $type, \salt\Query $other, \salt\Query $whereQuery)
stringAND|OR
\salt\Querythe other query for retrieve the join clause
\salt\Querythe query to add in ON clause
addWhere(string $addType, string|\salt\SqlExpr $fieldOrExpr, string $operator, string|\salt\SqlExpr $valueOrExpr)
stringAND|OR
string\salt\SqlExprfieldName or SqlExpr
stringoperator to use : '=', 'LIKE', 'IN', etc...
string\salt\SqlExprvalue or SqlExpr
addWhereClause(string $type, string $whereClause)
stringtype of where clause : AND|OR
stringwhere clause
addWhereExists(string $type, \salt\Query $otherQuery, boolean $exists = TRUE)
stringAND|OR
\salt\Query
booleanif FALSE : NOT EXISTS (SELECT 1 $otherQuery)
addWhereObject(string $type, \salt\Base|array<mixed,\salt\Base> $objects)
stringAND|OR
\salt\Basearray<mixed,\salt\Base>Base object or array of Base objects. Objects have to be of the same type than the query
addWhereQuery(string $type, \salt\Query $subQuery)
stringOR|AND
\salt\Queryhave to be a query retrieved by getSubQuery()
checkNotResolved()
\salt\SaltException |
if called after toSQL() |
|---|
getFieldType(string $field) : integer
stringthe fieldName
integerFiedType type of the field$_salt_alias : string
NULL$_salt_database : string
NULL$_salt_fields : array<mixed,mixed>
array()| content | array of array(SqlExpr, alias) |
|---|
$_salt_groups : array<mixed,string>
array()$_salt_joins : array<mixed,mixed>
array()| content | array of string => array( // key is table alias for join 'type' => string // join type 'INNER|OUTER|LEFT INNER|...' 'table' => string // table name or inner select table of the joined object 'on' => array of string // where clauses, like $wheres ) |
|---|
$_salt_noAlias : boolean
FALSE$_salt_obj : \salt\Base
$_salt_orders : array<mixed,string>
array()| content | field ASC|DESC |
|---|
$_salt_tableAliasNumber : integer
0$_salt_wheres : array<mixed,string>
array()| content | AND / OR are parts of $where elements |
|---|
$_salt_bindNumber : integer
0$_salt_bindPaginationNumber : integer
0$_salt_binds : array<mixed,mixed>
array()| content | array of bindName => array( 'value' => mixed // value of bind 'type' => int FieldType // type of field 'private' => boolean // if value is private data (like passwords) ) |
|---|
$_salt_enabled : boolean
TRUE$_salt_others : array<mixed,\salt\SqlBindField[][]>
array()| content | array of (ClauseType (source) => array of ClauseType(dest) => array of SqlBindField) |
|---|
$_salt_privateBinds : boolean
FALSE$_salt_sources : array<mixed,string[]>
array()| content | array of (ClauseType => array of bindName) |
|---|
$_salt_text : string
NULL