SALT
Back to top  

SELECT Query

package salt\sql\queries
inherited_from \salt\BaseQuery

 Methods

Create a new SELECT query

__construct(\salt\Base $obj, boolean $withField = FALSE) 
inherited_from \salt\BaseQuery::__construct()

Parameters

$obj

\salt\Base

instance of object for query creation

$withField

boolean

TRUE for load all the fields

Construct a new query

__construct(\salt\Base $obj) 
Inherited

Parameters

$obj

\salt\Base

the object is used for retrieve metadata.

Force the query to return empty results without execute.

forceEmptyResults() 

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
if (count($values) === 0) $q->forceEmptyResults();
$db->execQuery($q); // valid, will not execute query, so not throw exception

Get the class of the object used to construct to query

getBindingClass() : string

Returns

stringthe class of object the query work on

Return all binds

getBinds() : array<mixed,mixed[]>
Inherited

Returns

array<mixed,mixed[]>list of binds : array of array('value' => ..., 'type' => ...)

Get a field as a SqlExpr for reuse it in another query / SqlExpr

getField(string $field) : \salt\SqlExpr

Parameters

$field

string

the field name to get

Returns

\salt\SqlExprthe SqlExpr of the field

Return the list of field or alias returned by SELECT clause

getSelectFields() : array<mixed,string>

Returns

array<mixed,string>array of all fields or aliases selected by the query

Get a sub query of this query

getSubQuery() : \salt\Query

Returns

\salt\Querya query on the same table with the same alias for using in whereAndQuery or whereOrQuery

Add a field in GROUP BY clause

groupBy(string|\salt\SqlExpr $fieldOrExpr) 

Parameters

$fieldOrExpr

string\salt\SqlExpr

field name to group by

Initialize DBResult. Called during query execution.

initResults(\salt\Pagination $pagination = NULL, string $count = null) : \salt\DBResult

Parameters

$pagination

\salt\Pagination

pagination object to update

$count

string

total number of elements. Set on pagination

Returns

\salt\DBResultwith selected columns populated

Check if forceEmptyResults() has been called return boolean TRUE if we force an empty result

isEmptyResults() 

Add a classic join : INNER JOIN table tX ON .

join(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr, string $type = 'INNER') 

..

Parameters

$other

\salt\Query

the other query to join with

$fieldOrExpr

string\salt\SqlExpr

field of ON clause

$operator

string

operator of ON clause

$valueOrExpr

mixed\salt\SqlExpr

value of ON clause

$type

string

type of join: 'INNER', 'OUTER', etc...

Exceptions

\Exception if this join already exists

add an ON clause to an existing join

joinOnAnd(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr) 

Parameters

$other

\salt\Query

the other query for retrieve the join clause

$fieldOrExpr

string\salt\SqlExpr

field of ON clause

$operator

string

operator of ON clause

$valueOrExpr

mixed\salt\SqlExpr

value of ON clause

Exceptions

\Exception if join don't exists

add an query to an ON clause of an existing join

joinOnAndQuery(\salt\Query $other, \salt\Query $whereQuery) 

Parameters

$other

\salt\Query

the other query for retrieve the join clause

$whereQuery

\salt\Query

the query to add in ON clause

add an ON clause to an existing join

joinOnOr(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr) 

Parameters

$other

\salt\Query

the other query for retrieve the join clause

$fieldOrExpr

string\salt\SqlExpr

field of ON clause

$operator

string

operator of ON clause

$valueOrExpr

mixed\salt\SqlExpr

value of ON clause

Exceptions

\Exception if join don't exists

add an query to an ON clause of an existing join

joinOnOrQuery(\salt\Query $other, \salt\Query $whereQuery) 

Parameters

$other

\salt\Query

the other query for retrieve the join clause

$whereQuery

\salt\Query

the query to add in ON clause

Add a join with a select : INNER JOIN (SELECT .

joinSelect(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr, string $type = 'INNER') 

.. ) tX ON ...

Parameters

$other

\salt\Query

the other query to join with

$fieldOrExpr

string\salt\SqlExpr

field of ON clause

$operator

string

operator of ON clause

$valueOrExpr

mixed\salt\SqlExpr

value of ON clause

$type

string

type of join: 'INNER', 'OUTER', etc...

Exceptions

\Exception if this join already exists

Add a field in order clause, with ASC

orderAsc(string|\salt\SqlExpr $fieldOrExpr) 

Parameters

$fieldOrExpr

string\salt\SqlExpr

the field to order by ASC

Add a field in order clause, with DESC

orderDesc(string|\salt\SqlExpr $fieldOrExpr) 

Parameters

$fieldOrExpr

string\salt\SqlExpr

the field to order by DESC

Add a SqlExpr in the SELECT clause

select(\salt\SqlExpr $expr, string $as = NULL) 

Parameters

$expr

\salt\SqlExpr

expression to add in select clause, of any type

$as

string

alias for the expression

Exceptions

\Exception

Add a field int the SELECT clause

selectField(string|\salt\SqlExpr $field, string $as = NULL) 

Parameters

$field

string\salt\SqlExpr

a field name to add in select clause. A SqlExpr can also be used if it's a SqlExpr of type FIELD

$as

string

alias if needed

Add a list of fields in the SELECT clause

selectFields(array<mixed,string> $fields) 

Parameters

$fields

array<mixed,string>

list of fields name to add in select clause

get the SQL text for COUNT query

toCountSQL() : string

Have to be overrided by child classes if needed

inherited_from \salt\BaseQuery::toCountSQL()

Returns

stringSQL text of count(*) query

Retrieve the SQL text for COUNT query

toCountSQL() : string
Inherited

Have to be overrided by child classes if needed

Exceptions

\salt\SaltException if called

Returns

stringthe SQL text for count query

get the SQL text for SELECT query

toSQL(\salt\Pagination $pagination = NULL) : string
inherited_from \salt\BaseQuery::toSQL()

Parameters

$pagination

\salt\Pagination

pagination object for LIMIT OFFSET in query

Returns

stringSQL text of the query

Retrieve the SQL text of the query

toSQL(\salt\Pagination $pagination = NULL) : string
Inherited

Parameters

$pagination

\salt\Pagination

the object for handle paging

Returns

stringthe SQL text of the query with bind placeholders

Add a where clause with AND

whereAnd(string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExp $valueOrExpr) 

Parameters

$fieldOrExpr

string\salt\SqlExpr

fieldName or SqlExpr

$operator

string

operator to use : '=', 'LIKE', 'IN', etc...

$valueOrExpr

mixed\salt\SqlExp

value or SqlExpr

Add a where clause AND EXISTS (SELECT 1 $otherQuery)

whereAndExists(\salt\Query $otherQuery, boolean $exists = TRUE) 

Parameters

$otherQuery

\salt\Query

$exists

boolean

if FALSE : AND NOT EXISTS (SELECT 1 $otherQuery)

Add a WHERE clause on every object IDs (registerId)

whereAndObject(\salt\Base|array<mixed,\salt\Base> $objects) 

Parameters

$objects

\salt\Basearray<mixed,\salt\Base>

Base object or array of Base objects. Objects have to be of the same type than the query

Add a sub query where clause : AND ( $subQuery where clauses )

whereAndQuery(\salt\Query $subQuery) 

Parameters

$subQuery

\salt\Query

have to be a query retrieved by getSubQuery()

Add a where clause with OR

whereOr(string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExp $valueOrExpr) 

Parameters

$fieldOrExpr

string\salt\SqlExpr

fieldName or SqlExpr

$operator

string

operator to use : '=', 'LIKE', 'IN', etc...

$valueOrExpr

mixed\salt\SqlExp

value or SqlExpr

Add a where clause OR EXISTS (SELECT 1 $otherQuery)

whereOrExists(\salt\Query $otherQuery, boolean $exists = TRUE) 

Parameters

$otherQuery

\salt\Query

$exists

boolean

if FALSE : OR NOT EXISTS (SELECT 1 $otherQuery)

Add a WHERE clause on every object IDs (registerId)

whereOrObject(\salt\Base|array<mixed,\salt\Base> $objects) 

Parameters

$objects

\salt\Basearray<mixed,\salt\Base>

Base object or array of Base objects. Objects have to be of the same type than the query

Add a sub query where clause : OR ( $subQuery where clauses )

whereOrQuery(\salt\Query $subQuery) 

Parameters

$subQuery

\salt\Query

have to be a query retrieved by getSubQuery()

Add a bind

addBind(mixed $value, integer $type) : string
Inherited

Parameters

$value

mixed

value of bind

$type

integer

(FieldType) type of the field

Returns

stringthe bind name

Link one or more binds to a source

addBindsSource(string $source, string|array<mixed,string> $binds) 
Inherited

Parameters

$source

string

caller of bind (SELECT, WHERE, GROUPBY, etc...)

$binds

stringarray<mixed,string>

bind name or list of binds name

Retrieve binds linked to one or more sources

getBindsBySource(string $sources) : array<mixed,string>
Inherited

Parameters

$sources

string

... $sources function take unlimited source bind parameters

Returns

array<mixed,string>binds registered with theses sources

get the SQL text for JOIN part

joinsToSQL() : string

Returns

stringSQL text query with JOIN part

get the SQL text for ORDER BY part

orderToSQL() : string

Returns

stringSQL text for ORDER BY part

Used for replace something that can be a value by a bind or a SQL text

resolveFieldName(string $source, mixed|\salt\SqlExpr $fieldNameOrValue, mixed|\salt\SqlExpr $fieldOfValue = NULL) : string | array<mixed,string>
Inherited

Parameters

$source

string

caller of resolve : SELECT, WHERE, etc...

$fieldNameOrValue

mixed\salt\SqlExpr

a FieldName (string) or SqlExpr or potential value (int, string, array) if 3rd argument is provided

$fieldOfValue

mixed\salt\SqlExpr

a Field related to the value. Can be a SqlExpr or a string. Can be NULL if 2nd argument is a string fieldName

Returns

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 values

get the table name for the query

resolveTable() : string

Returns

stringtable name, with or without alias (depends on noAlias)

Set or replace a bind linked to a specific source

setOrRemplaceBind(string $source, mixed $value, integer $type) : string
Inherited

Only work with 1 bind by source name

Parameters

$source

string

source of bind

$value

mixed

a value for bind

$type

integer

FieldType

Returns

stringthe bind added or replaced

get the SQL text for FROM, JOIN, WHERE, GROUP BY parts

toBaseSQL() : string

Returns

stringSQL text query with common parts only

get the SQL text for WHERE part

wheresToSQL() : string

Returns

stringSQL text query with WHERE part

Add a join clause

addJoin(\salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr, string $type = 'INNER', string $table, boolean $withOtherDatas = TRUE) 

Parameters

$other

\salt\Query

the other query to join with

$fieldOrExpr

string\salt\SqlExpr

field of ON clause

$operator

string

operator of ON clause

$valueOrExpr

mixed\salt\SqlExpr

value of ON clause

$type

string

type of join: 'INNER', 'OUTER', etc...

$table

string

object to join with : table name of subquery

$withOtherDatas

boolean

TRUE if we have to add fields, where, group by, order clause to main query

Exceptions

\Exception if this join already exists

add an ON clause to an existing join

addJoinOn(string $type, \salt\Query $other, string|\salt\SqlExpr $fieldOrExpr, string $operator, mixed|\salt\SqlExpr $valueOrExpr) 

Parameters

$type

string

AND|OR

$other

\salt\Query

the other query for retrieve the join clause

$fieldOrExpr

string\salt\SqlExpr

field of ON clause

$operator

string

operator of ON clause

$valueOrExpr

mixed\salt\SqlExpr

value of ON clause

Exceptions

\Exception if join don't exists

add an query to an ON clause of an existing join

addJoinOnQuery(string $type, \salt\Query $other, \salt\Query $whereQuery) 

Parameters

$type

string

AND|OR

$other

\salt\Query

the other query for retrieve the join clause

$whereQuery

\salt\Query

the query to add in ON clause

Add a where clause

addWhere(string $addType, string|\salt\SqlExpr $fieldOrExpr, string $operator, string|\salt\SqlExpr $valueOrExpr) 

Parameters

$addType

string

AND|OR

$fieldOrExpr

string\salt\SqlExpr

fieldName or SqlExpr

$operator

string

operator to use : '=', 'LIKE', 'IN', etc...

$valueOrExpr

string\salt\SqlExpr

value or SqlExpr

Add a where clause from SQL text

addWhereClause(string $type, string $whereClause) 

Parameters

$type

string

type of where clause : AND|OR

$whereClause

string

where clause

Add a where clause EXISTS (SELECT 1 $otherQuery)

addWhereExists(string $type, \salt\Query $otherQuery, boolean $exists = TRUE) 

Parameters

$type

string

AND|OR

$otherQuery

\salt\Query

$exists

boolean

if FALSE : NOT EXISTS (SELECT 1 $otherQuery)

Add a WHERE clause on every object IDs (registerId)

addWhereObject(string $type, \salt\Base|array<mixed,\salt\Base> $objects) 

Parameters

$type

string

AND|OR

$objects

\salt\Basearray<mixed,\salt\Base>

Base object or array of Base objects. Objects have to be of the same type than the query

Add a sub query where clause : OR ( $subQuery where clauses )

addWhereQuery(string $type, \salt\Query $subQuery) 

Parameters

$type

string

OR|AND

$subQuery

\salt\Query

have to be a query retrieved by getSubQuery()

Get the type of the field

getFieldType(string $field) : integer
Inherited

Parameters

$field

string

the fieldName

Returns

integerFiedType type of the field

 Properties

 

alias for the table

$alias : string

Default

NULL
 

bind unique number

$bindNumber : integer

Default

0
Static
 

list of binds

$binds : array<mixed,mixed>

Default

array()
content 
array of bindName => array(
					'value' => mixed // value of bind
					'type' => int FieldType // type of field
			)
 

by source (SELECT, WHERE, GROUPBY, LIMIT, etc...)

$bindsSource : \salt\binds

Default

array()
content array of source => array(binds)
 

TRUE if we force an empty result, without execute query

$emptyResults : boolean

Default

FALSE
 

list of fields to retrieve (SELECT)

$fields : array<mixed,mixed>

Default

array()
content array of array(SqlExpr, alias)
 

: list of field name for group by clause

$groups : array<mixed,string>

Default

array()
 

list of join clause

$joins : array<mixed,mixed>

Default

array()
content 
array of string => array( // key is table alias for join
			'meta' => array of Field // of the joined object
			'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
		)
 

TRUE if table name does not have to use alias (DELETE query)

$noAlias : boolean

Default

FALSE
 

internal instance of object the query is build for

$obj : \salt\Base

Default

 

: list of order by clause

$orders : array<mixed,string>

Default

array()
content field ASC|DESC
 

table alias unique number

$tableAliasNumber : integer

Default

0
Static
 

list of where clause

$wheres : array<mixed,string>

Default

array()
content AND / OR are parts of $where elements