Class for SQL SELECT generation and results. More...

Inheritance diagram for SqlQueryBuilder:

Public Member Functions
	__construct ($adapter=null, $queryType=self::QUERY_SELECT)
	Class constructor.
	getClone ()
	Returns clone of this builder.
	getSql ()
	getBind ($name=null)
	Get bind variables.
	bind ($bind, $name=false)
	Add bind variables.
	bindKnown ($bind)
	Binds only known values.
	autoBind ($value, $bindName, $bindAlways=true)
	Decides whether to use bind placeholder, quoted value, or subselect.
	distinct ($flag=true)
	Makes the query SELECT DISTINCT.
	from ($name, $cols= '*', $schema=null)
	Adds a FROM table and optional columns to the query.
	table ($name, $cols=false, $schema=null)
	columns ($cols= '*', $correlationName=null, $replace=false)
	Specifies the columns used in the FROM clause.
	union ($select=array(), $type=self::SQL_UNION)
	Adds a UNION clause to the query.
	join ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
	Adds a JOIN table and columns to the query.
	joinInner ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
	Add an INNER JOIN table and colums to the query Rows in both tables are matched according to the expression in the $cond argument.
	joinLeft ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
	Add a LEFT OUTER JOIN table and colums to the query All rows from the left operand table are included, matching rows from the right operand table included, and the columns from the right operand table are filled with NULLs if no row exists matching the left table.
	joinRight ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
	Add a RIGHT OUTER JOIN table and colums to the query.
	joinFull ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
	Add a FULL OUTER JOIN table and colums to the query.
	joinCross ($name, $cols=self::SQL_WILDCARD, $schema=null)
	Add a CROSS JOIN table and colums to the query.
	joinNatural ($name, $cols=self::SQL_WILDCARD, $schema=null)
	Add a NATURAL JOIN table and colums to the query.
	where ($cond, $value=null, $type=null, $bindName=false, $bindAlways=true)
	Adds a WHERE condition to the query by AND.
	orWhere ($cond, $value=null, $type=null, $bindName=false, $bindAlways=true)
	Adds a WHERE condition to the query by OR.
	sqlEquals ($column, $value, $equals=true, $unifyType=true)
	Returns SQL for 'column = "Value"', or 'column IN ("val1", "val2")', or 'column IS NULL'.
	whereEquals ($column, $value, $equals=true, $unifyType=true)
	orWhereEquals ($column, $value, $equals=true, $unifyType=true)
	group ($spec)
	Adds grouping to the query.
	having ($cond, $value=null, $type=null, $bindName=false, $bindAlways=true)
	Adds a HAVING condition to the query by AND.
	orHaving ($cond, $value=null, $type=null, $bindName=false, $bindAlways=true)
	Adds a HAVING condition to the query by OR.
	order ($spec)
	Adds a row order to the query.
	limit ($count=null, $offset=null)
	Sets a limit count and offset to the query.
	limitPage ($page, $rowCount)
	Sets the limit and count by page number.
	forUpdate ($flag=true)
	Makes the query SELECT FOR UPDATE.
	set ($name, $value=null, $bindName=false, $bindAlways=true)
	Adds SET element.
	duplicateKeyUpdate ($name, $value=null, $bindName=false, $bindAlways=true)
	Adds ON DUPLICATE KEY UPDATE element.
	values ($values, $bindName=false, $bindAlways=true)
	Adds VALUES values.
	value ($name, $value, $bindName=false, $bindAlways=true)
	nextValues ()
	Starts a next set of values.
	prefix ($sql)
	Adds SQL statements at the beginning of the query.
	suffix ($sql)
	Adds SQL statements at the end of the query.
	hasTable ($table)
	Return true if the table or correlation exists.
	hasTables ()
	Return true if any tables exist.
	findTable ($table)
	Finds table's correlation name.
	getPart ($part)
	Get part of the structured information for the currect query.
	getQueryType ()
	getAdapter ()
	setAutoBindsEnabled ($autoBindsEnabled)
	setAutoBindNamedPlaceholders ($autoBindNamedPlaceholders)
	setFindTableCorrelations ($findTableCorrelations)
	getPartSql ($part, $sql= '')
	Get part part of the SQL.
	assemble ($reassemble=false)
	Converts this object to an SQL SELECT string.
	reset ($part=null)
	Clear parts of the Select object, or an individual part.
	parseTableName ($name, $schema= '', $createCorrelation=false)
	Parses tablename and returns it's parts - array(correlation, table, schema)
	_joinUsing ($type, $name, $cond, $cols= '*', $schema=null)
	Handle JOIN...
	__call ($method, array $args)
	Turn magic function calls into non-magic function calls for joinUsing syntax.
	__toString ()
	Implements magic method.
	quote ($value, $type=null)
	Safely quotes a value for an SQL statement.
	quoteInto ($text, $value, $type=null, $count=null, $bindName=false, $bindAlways=true)
	Quotes a value and places into a piece of text at a placeholder.
	quoteIdentifier ($ident, $auto=false)
	Quotes an identifier.
	quoteColumnAs ($ident, $alias, $auto=false)
	Quote a column identifier and alias.
	quoteTableAs ($ident, $alias=null, $auto=false)
	Quote a table identifier and alias.
	getQuoteIdentifierSymbol ()
	Returns the symbol the adapter uses for delimited identifiers.
	sqlLimit ($sql, $count, $offset=0)
	Adds an adapter-specific LIMIT clause to the SELECT statement.
Static Public Member Functions
static	factory ($adapter=null, $queryType=self::QUERY_SELECT)
static	store ($sql, $bind=null, $adapter=null)
	Creates QUERY_SQL builder for storing already prepared SQLs.
static	parse ($sql, $bind=null, $adapter=null)
	Tries to parse a query.
Data Fields
const	INT_TYPE = 0
	Use the INT_TYPE, BIGINT_TYPE, and FLOAT_TYPE with the quote() method.
const	BIGINT_TYPE = 1
const	FLOAT_TYPE = 2
const	STRING_TYPE = 3
const	QUERY_SELECT = 'select'
const	QUERY_INSERT = 'insert'
const	QUERY_DELETE = 'delete'
const	QUERY_UPDATE = 'update'
const	QUERY_REPLACE = 'replace'
const	QUERY_SQL = 'sql'
	Special query type to store any SQL inside.
const	DISTINCT = 'distinct'
const	COLUMNS = 'columns'
const	FROM = 'from'
const	UNION = 'union'
const	WHERE = 'where'
const	GROUP = 'group'
const	HAVING = 'having'
const	ORDER = 'order'
const	LIMIT_COUNT = 'limitcount'
const	LIMIT_OFFSET = 'limitoffset'
const	FOR_UPDATE = 'forupdate'
const	PREFIX = 'prefix'
const	SUFFIX = 'suffix'
const	VALUES = 'values'
const	SET = 'set'
const	DUPLICATE_KEY_UPDATE = 'duplicateKeyUpdate'
const	INNER_JOIN = 'inner join'
const	LEFT_JOIN = 'left join'
const	RIGHT_JOIN = 'right join'
const	FULL_JOIN = 'full join'
const	CROSS_JOIN = 'cross join'
const	NATURAL_JOIN = 'natural join'
const	SQL_WILDCARD = '*'
const	SQL_SELECT = 'SELECT'
const	SQL_REPLACE = 'REPLACE'
const	SQL_INSERT = 'INSERT'
const	SQL_UPDATE = 'UPDATE'
const	SQL_DELETE = 'DELETE'
const	SQL_UNION = 'UNION'
const	SQL_UNION_ALL = 'UNION ALL'
const	SQL_FROM = 'FROM'
const	SQL_WHERE = 'WHERE'
const	SQL_DISTINCT = 'DISTINCT'
const	SQL_GROUP_BY = 'GROUP BY'
const	SQL_ORDER_BY = 'ORDER BY'
const	SQL_HAVING = 'HAVING'
const	SQL_FOR_UPDATE = 'FOR UPDATE'
const	SQL_AND = 'AND'
const	SQL_AS = 'AS'
const	SQL_OR = 'OR'
const	SQL_ON = 'ON'
const	SQL_ASC = 'ASC'
const	SQL_DESC = 'DESC'
const	SQL_SET = 'SET'
const	SQL_INTO = 'INTO'
const	SQL_COLUMNS = 'COLUMNS'
const	SQL_VALUES = 'VALUES'
const	SQL_DEFAULT = 'DEFAULT'
const	SQL_ON_DUPLICATE_KEY_UPDATE = 'ON DUPLICATE KEY UPDATE'
const	COLTABLE_CORRELATION = 0
const	COLTABLE_COLUMN = 1
const	COLTABLE_ALIAS = 2
	$findTableCorrelations = true
	If enabled, it's possible to use table names instead of their correlation name in some functions.
	$autoBindsEnabled = true
	If set to false, autoBind will always quote values.
	$autoBindNamedPlaceholders = false
	If true, every named autobind will bind a value.
Protected Member Functions
	_set ($name, $value=null, $bindName=false, $bindAlways=true)
	_value ($name, $value=null, $bindName=false, $bindAlways=true)
	_join ($type, $name, $cond, $cols, $schema=null)
	Populate the $_parts 'join' key.
	_tableCols ($correlationName, $cols, $afterCorrelationName=null)
	Adds to the internal table-to-column mapping array.
	_where ($condition, $value=null, $type=null, $bool=true, $bindName=false, $bindAlways=true)
	Internal function for creating the where clause.
	_getDummyTable ()
	_getQuotedSchema ($schema=null)
	Return a quoted schema name.
	_getQuotedTable ($tableName, $correlationName=null)
	Return a quoted table name.
	_renderDistinct ($sql)
	Render DISTINCT clause.
	_renderColumns ($sql)
	Render DISTINCT clause.
	_renderFrom ($sql)
	Render FROM clause.
	_renderUnion ($sql)
	Render UNION query.
	_renderWhere ($sql)
	Render WHERE clause.
	_renderGroup ($sql)
	Render GROUP clause.
	_renderHaving ($sql)
	Render HAVING clause.
	_renderOrder ($sql)
	Render ORDER clause.
	_renderLimitoffset ($sql)
	Render LIMIT OFFSET clause.
	_renderForupdate ($sql)
	Render FOR UPDATE clause.
	_renderSet ($sql)
	Render SET clause.
	_renderDuplicateKeyUpdate ($sql)
	Render DUPLICATE_KEY_UPDATE clause.
	_renderValues ($sql)
	Render VALUES clause.
	_renderPrefix ($sql)
	Render WHERE clause.
	_renderSuffix ($sql)
	Render WHERE clause.
	_quote ($value, $type=null)
	Quote a raw string.
	_quoteIdentifierAs ($ident, $alias=null, $auto=false, $as= 'AS ')
	Quote an identifier and an optional alias.
	_quoteIdentifier ($value, $auto=false)
	Quote an identifier.
Protected Attributes
	$_autoQuoteIdentifiers = true
	$_bind = array()
	$_parts = array()
	$_tableCols = array()
	$_queryType
	$_adapter
	$_assembledSql = null
Static Protected Attributes
static	$_partsInit
static	$_joinTypes
static	$_queryTypes
static	$_unionTypes

Detailed Description

Class for SQL SELECT generation and results.

Constructor & Destructor Documentation

SqlQueryBuilder::__construct	(	$	adapter = `null`,
		$	queryType = `self::QUERY_SELECT`
	)

Class constructor.

Parameters:

$adapter Database object

Member Function Documentation

SqlQueryBuilder::__call	(	$	method,
		array $	args
	)

Turn magic function calls into non-magic function calls for joinUsing syntax.

Parameters:

string	$method
array	$args	OPTIONAL Zend_Db_Table_Select query modifier

Returns:: SqlQueryBuilder

Exceptions:

SqlQueryBuilderException If an invalid method is called.

Recognize methods for Has-Many cases: findParent<Class>() findParent<Class>By<Rule>() Use the non-greedy pattern repeat modifier e.g. +?

SqlQueryBuilder::__toString ( )

Implements magic method.

Returns:: string This object as a SELECT string.

SqlQueryBuilder::_getDummyTable ( ) [protected]

Returns:: array

SqlQueryBuilder::_getQuotedSchema ( $ schema = null ) [protected]

Return a quoted schema name.

Parameters:

string $schema The schema name OPTIONAL

Returns:: string|null

SqlQueryBuilder::_getQuotedTable	(	$	tableName,
		$	correlationName = `null`
	)		`[protected]`

Return a quoted table name.

Parameters:

string	$tableName	The table name
string	$correlationName	The correlation name OPTIONAL

Returns:: string

SqlQueryBuilder::_join	(	$	type,
		$	name,
		$	cond,
		$	cols,
		$	schema = `null`
	)		`[protected]`

Populate the $_parts 'join' key.

Does the dirty work of populating the join key.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters:

null \| string	$type	Type of join; inner, left, and null are currently supported
array \| string \| SqlQueryExpr	$name	Table name
string	$cond	Join on this condition
array \| string	$cols	The columns to select from the joined table
string	$schema	The database name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object

Exceptions:

SqlQueryBuilderException

See also:: SqlQueryBuilderException; SqlQueryBuilderException

SqlQueryBuilder::_joinUsing	(	$	type,
		$	name,
		$	cond,
		$	cols = `'*'`,
		$	schema = `null`
	)

Handle JOIN...

USING... syntax

This is functionality identical to the existing JOIN methods, however the join condition can be passed as a single column name. This method then completes the ON condition by using the same field for the FROM table and the JOIN table.

$select = $db->select()->from('table1') ->joinUsing('table2', 'column1');

// SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2

These joins are called by the developer simply by adding 'Using' to the method name. E.g. * joinUsing * joinInnerUsing * joinFullUsing * joinRightUsing * joinLeftUsing

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::_quote	(	$	value,
		$	type = `null`
	)		`[protected]`

Quote a raw string.

Parameters:

string $value Raw string

Returns:: string Quoted string

SqlQueryBuilder::_quoteIdentifier	(	$	value,
		$	auto = `false`
	)		`[protected]`

Quote an identifier.

Parameters:

string	$value	The identifier or expression.
boolean	$auto	If true, heed the AUTO_QUOTE_IDENTIFIERS config option.

Returns:: string The quoted identifier and alias.

SqlQueryBuilder::_quoteIdentifierAs	(	$	ident,
		$	alias = `null`,
		$	auto = `false`,
		$	as = `' AS '`
	)		`[protected]`

Quote an identifier and an optional alias.

Parameters:

string \| array \| SqlQueryExpr	$ident	The identifier or expression.
string	$alias	An optional alias.
boolean	$auto	If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
string	$as	The string to add between the identifier/expression and the alias.

Returns:: string The quoted identifier and alias.

SqlQueryBuilder::_renderColumns ( $ sql ) [protected]

Render DISTINCT clause.

Parameters:

string $sql SQL query

Returns:: string|null

SqlQueryBuilder::_renderDistinct ( $ sql ) [protected]

Render DISTINCT clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderDuplicateKeyUpdate ( $ sql ) [protected]

Render DUPLICATE_KEY_UPDATE clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderForupdate ( $ sql ) [protected]

Render FOR UPDATE clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderFrom ( $ sql ) [protected]

Render FROM clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderGroup ( $ sql ) [protected]

Render GROUP clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderHaving ( $ sql ) [protected]

Render HAVING clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderLimitoffset ( $ sql ) [protected]

Render LIMIT OFFSET clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderOrder ( $ sql ) [protected]

Render ORDER clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderPrefix ( $ sql ) [protected]

Render WHERE clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderSet ( $ sql ) [protected]

Render SET clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderSuffix ( $ sql ) [protected]

Render WHERE clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderUnion ( $ sql ) [protected]

Render UNION query.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderValues ( $ sql ) [protected]

Render VALUES clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_renderWhere ( $ sql ) [protected]

Render WHERE clause.

Parameters:

string $sql SQL query

Returns:: string

SqlQueryBuilder::_set	(	$	name,
		$	value = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)		`[protected]`

SqlQueryBuilder::_tableCols	(	$	correlationName,
		$	cols,
		$	afterCorrelationName = `null`
	)		`[protected]`

Adds to the internal table-to-column mapping array.

Parameters:

string	$tbl	The table/join the columns come from.
array \| string	$cols	The list of columns; preferably as an array, but possibly as a string containing one column.
	bool\|string	True if it should be prepended, a correlation name if it should be inserted

Returns:: void

SqlQueryBuilder::_value	(	$	name,
		$	value = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)		`[protected]`

SqlQueryBuilder::_where	(	$	condition,
		$	value = `null`,
		$	type = `null`,
		$	bool = `true`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)		`[protected]`

Internal function for creating the where clause.

Parameters:

string	$condition
mixed	$value	optional
string	$type	optional
boolean	$bool	true = AND, false = OR

Returns:: string clause

SqlQueryBuilder::assemble ( $ reassemble = false )

Converts this object to an SQL SELECT string.

Returns:: string|null This object as a SELECT string. (or null if a string cannot be produced.)

SqlQueryBuilder::autoBind	(	$	value,
		$	bindName,
		$	bindAlways = `true`
	)

Decides whether to use bind placeholder, quoted value, or subselect.

Parameters:

	$bindName	True for unnamed bind, False to always quote, string to bind to named value
string \| SqlQueryBuilder \| SqlQueryExpr	$value
boolean	$bindAlways	If true, values will be always bound, if false null values won't be bound

SqlQueryBuilder::bind	(	$	bind,
		$	name = `false`
	)

Add bind variables.

Parameters:

mixed	$bind	Array of binds to add, or single value
	$name	Name of the value to bind

Returns:: SqlQueryBuilder

SqlQueryBuilder::bindKnown ( $ bind )

Binds only known values.

Returns:: SqlQueryBuilder

SqlQueryBuilder::columns	(	$	cols = `'*'`,
		$	correlationName = `null`,
		$	replace = `false`
	)

Specifies the columns used in the FROM clause.

The parameter can be a single string or SqlQueryExpr object, or else an array of strings or SqlQueryExpr objects.

Parameters:

array \| string \| SqlQueryExpr	$cols	The columns to select from this table.
string	$correlationName	Correlation name of target table. OPTIONAL

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::distinct ( $ flag = true )

Makes the query SELECT DISTINCT.

Parameters:

bool $flag Whether or not the SELECT is DISTINCT (default true).

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::duplicateKeyUpdate	(	$	name,
		$	value = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

Adds ON DUPLICATE KEY UPDATE element.

Parameters:

string \| array	$name	Name of the value to add, or array of name=>value
	string\|SqlQueryExpr\|SqlQueryBuilder	Value
	boolean\|string	Bind name to use, or true for unqalified bind ,

See also:: autoBind()

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

static SqlQueryBuilder::factory	(	$	adapter = `null`,
		$	queryType = `self::QUERY_SELECT`
	)		`[static]`

Returns:: SqlQueryBuilder

SqlQueryBuilder::findTable ( $ table )

Finds table's correlation name.

SqlQueryBuilder::forUpdate ( $ flag = true )

Makes the query SELECT FOR UPDATE.

Parameters:

bool $flag Whether or not the SELECT is FOR UPDATE (default true).

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::from	(	$	name,
		$	cols = `'*'`,
		$	schema = `null`
	)

Adds a FROM table and optional columns to the query.

The first parameter $name can be a simple string, in which case the correlation name is generated automatically. If you want to specify the correlation name, the first parameter must be an associative array in which the key is the correlation name, and the value is the physical table name. For example, array('alias' => 'table'). The correlation name is prepended to all columns fetched for this table.

The second parameter can be a single string or SqlQueryExpr object, or else an array of strings or SqlQueryExpr objects.

The first parameter can be null or an empty string, in which case no correlation name is generated or prepended to the columns named in the second parameter.

Parameters:

array \| string \| SqlQueryExpr	$name	The table name or an associative array relating correlation name to table name.
array \| string \| SqlQueryExpr	$cols	The columns to select from this table.
string	$schema	The schema name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::getAdapter ( )

Reimplemented in KWebQueryBuilder.

SqlQueryBuilder::getBind ( $ name = null )

Get bind variables.

Returns:: array

SqlQueryBuilder::getClone ( )

Returns clone of this builder.

Returns:: SqlQueryBuilder

SqlQueryBuilder::getPart ( $ part )

Get part of the structured information for the currect query.

Parameters:

string $part

Returns:: mixed

Exceptions:

SqlQueryBuilderException

SqlQueryBuilder::getPartSql	(	$	part,
		$	sql = `''`
	)

Get part part of the SQL.

Parameters:

string $part

Returns:: mixed

Exceptions:

SqlQueryBuilderException

SqlQueryBuilder::getQueryType ( )

SqlQueryBuilder::getQuoteIdentifierSymbol ( )

Returns the symbol the adapter uses for delimited identifiers.

Returns:: string

SqlQueryBuilder::getSql ( )

SqlQueryBuilder::group ( $ spec )

Adds grouping to the query.

Parameters:

array | string $spec The column(s) to group by.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::hasTable ( $ table )

Return true if the table or correlation exists.

SqlQueryBuilder::hasTables ( )

Return true if any tables exist.

SqlQueryBuilder::having	(	$	cond,
		$	value = `null`,
		$	type = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

Adds a HAVING condition to the query by AND.

If a value is passed as the second param, it will be quoted and replaced into the condition wherever a question-mark appears. See where() for an example

Parameters:

string	$cond	The HAVING condition.
mixed	$value	OPTIONAL The value to quote into the condition.
int	$type	OPTIONAL The type of the given value

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::join	(	$	name,
		$	cond,
		$	cols = `self::SQL_WILDCARD`,
		$	schema = `null`
	)

Adds a JOIN table and columns to the query.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters:

array \| string \| SqlQueryExpr	$name	The table name.
string	$cond	Join on this condition.
array \| string	$cols	The columns to select from the joined table.
string	$schema	The database name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::joinCross	(	$	name,
		$	cols = `self::SQL_WILDCARD`,
		$	schema = `null`
	)

Add a CROSS JOIN table and colums to the query.

A cross join is a cartesian product; there is no join condition.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters:

array \| string \| SqlQueryExpr	$name	The table name.
array \| string	$cols	The columns to select from the joined table.
string	$schema	The database name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::joinFull	(	$	name,
		$	cond,
		$	cols = `self::SQL_WILDCARD`,
		$	schema = `null`
	)

Add a FULL OUTER JOIN table and colums to the query.

A full outer join is like combining a left outer join and a right outer join. All rows from both tables are included, paired with each other on the same row of the result set if they satisfy the join condition, and otherwise paired with NULLs in place of columns from the other table.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters:

array \| string \| SqlQueryExpr	$name	The table name.
string	$cond	Join on this condition.
array \| string	$cols	The columns to select from the joined table.
string	$schema	The database name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::joinInner	(	$	name,
		$	cond,
		$	cols = `self::SQL_WILDCARD`,
		$	schema = `null`
	)

Add an INNER JOIN table and colums to the query Rows in both tables are matched according to the expression in the $cond argument.

The result set is comprised of all cases where rows from the left table match rows from the right table.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters:

array \| string \| SqlQueryExpr	$name	The table name.
string	$cond	Join on this condition.
array \| string	$cols	The columns to select from the joined table.
string	$schema	The database name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::joinLeft	(	$	name,
		$	cond,
		$	cols = `self::SQL_WILDCARD`,
		$	schema = `null`
	)

Add a LEFT OUTER JOIN table and colums to the query All rows from the left operand table are included, matching rows from the right operand table included, and the columns from the right operand table are filled with NULLs if no row exists matching the left table.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters:

array \| string \| SqlQueryExpr	$name	The table name.
string	$cond	Join on this condition.
array \| string	$cols	The columns to select from the joined table.
string	$schema	The database name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::joinNatural	(	$	name,
		$	cols = `self::SQL_WILDCARD`,
		$	schema = `null`
	)

Add a NATURAL JOIN table and colums to the query.

A natural join assumes an equi-join across any column(s) that appear with the same name in both tables. Only natural inner joins are supported by this API, even though SQL permits natural outer joins as well.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters:

array \| string \| SqlQueryExpr	$name	The table name.
array \| string	$cols	The columns to select from the joined table.
string	$schema	The database name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::joinRight	(	$	name,
		$	cond,
		$	cols = `self::SQL_WILDCARD`,
		$	schema = `null`
	)

Add a RIGHT OUTER JOIN table and colums to the query.

Right outer join is the complement of left outer join. All rows from the right operand table are included, matching rows from the left operand table included, and the columns from the left operand table are filled with NULLs if no row exists matching the right table.

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters:

array \| string \| SqlQueryExpr	$name	The table name.
string	$cond	Join on this condition.
array \| string	$cols	The columns to select from the joined table.
string	$schema	The database name to specify, if any.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::limit	(	$	count = `null`,
		$	offset = `null`
	)

Sets a limit count and offset to the query.

Parameters:

int	$count	OPTIONAL The number of rows to return.
int	$offset	OPTIONAL Start returning after this many rows.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::limitPage	(	$	page,
		$	rowCount
	)

Sets the limit and count by page number.

Parameters:

int	$page	Limit results to this page number.
int	$rowCount	Use this many rows per page.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::nextValues ( )

Starts a next set of values.

Returns:: SqlQueryBuilder

SqlQueryBuilder::order ( $ spec )

Adds a row order to the query.

Parameters:

mixed $spec The column(s) and direction to order by.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::orHaving	(	$	cond,
		$	value = `null`,
		$	type = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

Adds a HAVING condition to the query by OR.

Otherwise identical to orHaving().

Parameters:

string	$cond	The HAVING condition.
mixed	$value	OPTIONAL The value to quote into the condition.
int	$type	OPTIONAL The type of the given value

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

See also:: having()

SqlQueryBuilder::orWhere	(	$	cond,
		$	value = `null`,
		$	type = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

Adds a WHERE condition to the query by OR.

Otherwise identical to where().

Parameters:

string	$cond	The WHERE condition.
mixed	$value	OPTIONAL The value to quote into the condition.
int	$type	OPTIONAL The type of the given value

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

See also:: where()

SqlQueryBuilder::orWhereEquals	(	$	column,
		$	value,
		$	equals = `true`,
		$	unifyType = `true`
	)

Parameters:

$column	Column
$equals	TRUE for equals, FALSE for not equals
$value	string\|null\|array
$unifyType	treats everything in the array as strings. If you are not sure if everything in the array is of the same type, and it doesn't contain any null values, leave this as true. Otherwise it may lead to inconsistent results.

Returns:: string

static SqlQueryBuilder::parse	(	$	sql,
		$	bind = `null`,
		$	adapter = `null`
	)		`[static]`

Tries to parse a query.

Currently it only creates QUERY_SQL builder

Returns:: SqlQueryBuilder

SqlQueryBuilder::parseTableName	(	$	name,
		$	schema = `''`,
		$	createCorrelation = `false`
	)

Parses tablename and returns it's parts - array(correlation, table, schema)

SqlQueryBuilder::prefix ( $ sql )

Adds SQL statements at the beginning of the query.

Returns:: SqlQueryBuilder

SqlQueryBuilder::quote	(	$	value,
		$	type = `null`
	)

Safely quotes a value for an SQL statement.

If an array is passed as the value, the array values are quoted and then returned as a comma-separated string.

Parameters:

mixed	$value	The value to quote.
mixed	$type	OPTIONAL the SQL datatype name, or constant, or null.

Returns:: mixed An SQL-safe quoted value (or string of separated values).

SqlQueryBuilder::quoteColumnAs	(	$	ident,
		$	alias,
		$	auto = `false`
	)

Quote a column identifier and alias.

Parameters:

string \| array \| SqlQueryExpr	$ident	The identifier or expression.
string	$alias	An alias for the column.
boolean	$auto	If true, heed the AUTO_QUOTE_IDENTIFIERS config option.

Returns:: string The quoted identifier and alias.

SqlQueryBuilder::quoteIdentifier	(	$	ident,
		$	auto = `false`
	)

Quotes an identifier.

Accepts a string representing a qualified indentifier. For Example: $adapter->quoteIdentifier('myschema.mytable') Returns: "myschema"."mytable"

Or, an array of one or more identifiers that may form a qualified identifier: $adapter->quoteIdentifier(array('myschema','my.table')) Returns: "myschema"."my.table"

The actual quote character surrounding the identifiers may vary depending on the adapter.

Parameters:

string \| array \| SqlQueryExpr	$ident	The identifier.
boolean	$auto	If true, heed the AUTO_QUOTE_IDENTIFIERS config option.

Returns:: string The quoted identifier.

SqlQueryBuilder::quoteInto	(	$	text,
		$	value,
		$	type = `null`,
		$	count = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

Quotes a value and places into a piece of text at a placeholder.

The placeholder is a question-mark; all placeholders will be replaced with the quoted value. For example:

$text = "WHERE date < ?"; $date = "2005-01-02"; $safe = $sql->quoteInto($text, $date); // $safe = "WHERE date < '2005-01-02'"

Parameters:

string	$text	The text with a placeholder.
mixed	$value	The value to quote.
string	$type	OPTIONAL SQL datatype
integer	$count	OPTIONAL count of placeholders to replace

Returns:: string An SQL-safe quoted value placed into the original text.

SqlQueryBuilder::quoteTableAs	(	$	ident,
		$	alias = `null`,
		$	auto = `false`
	)

Quote a table identifier and alias.

Parameters:

string \| array \| SqlQueryExpr	$ident	The identifier or expression.
string	$alias	An alias for the table.
boolean	$auto	If true, heed the AUTO_QUOTE_IDENTIFIERS config option.

Returns:: string The quoted identifier and alias.

SqlQueryBuilder::reset ( $ part = null )

Clear parts of the Select object, or an individual part.

Parameters:

string $part OPTIONAL

Returns:: SqlQueryBuilder

SqlQueryBuilder::set	(	$	name,
		$	value = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

Adds SET element.

Parameters:

string \| array	$name	Name of the value to add, or array of name=>value
	string\|SqlQueryExpr\|SqlQueryBuilder	Value
	boolean\|string	Bind name to use, or true for unqalified bind ,

See also:: autoBind()

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::setAutoBindNamedPlaceholders ( $ autoBindNamedPlaceholders )

Returns:: SqlQueryBuilder

SqlQueryBuilder::setAutoBindsEnabled ( $ autoBindsEnabled )

Returns:: SqlQueryBuilder

SqlQueryBuilder::setFindTableCorrelations ( $ findTableCorrelations )

Returns:: SqlQueryBuilder

SqlQueryBuilder::sqlEquals	(	$	column,
		$	value,
		$	equals = `true`,
		$	unifyType = `true`
	)

Returns SQL for 'column = "Value"', or 'column IN ("val1", "val2")', or 'column IS NULL'.

Parameters:

$column	Column
$equals	TRUE for equals, FALSE for not equals
$value	string\|null\|array
$unifyType	treats everything in the array as strings. If you are not sure if everything in the array is of the same type, and it doesn't contain any null values, leave this as true. Otherwise it may lead to inconsistent results.

Returns:: string

SqlQueryBuilder::sqlLimit	(	$	sql,
		$	count,
		$	offset = `0`
	)

Adds an adapter-specific LIMIT clause to the SELECT statement.

Parameters:

string	$sql
integer	$count
integer	$offset	OPTIONAL

Exceptions:

Zend_Db_Adapter_Exception

Returns:: string

static SqlQueryBuilder::store	(	$	sql,
		$	bind = `null`,
		$	adapter = `null`
	)		`[static]`

Creates QUERY_SQL builder for storing already prepared SQLs.

Returns:: SqlQueryBuilder

SqlQueryBuilder::suffix ( $ sql )

Adds SQL statements at the end of the query.

Returns:: SqlQueryBuilder

SqlQueryBuilder::table	(	$	name,
		$	cols = `false`,
		$	schema = `null`
	)

See also:: from()

SqlQueryBuilder::union	(	$	select = `array()`,
		$	type = `self::SQL_UNION`
	)

Adds a UNION clause to the query.

The first parameter has to be an array of SqlQueryBuilder or sql query strings.

$sql1 = $db->select(); $sql2 = "SELECT ..."; $select = $db->select() ->union(array($sql1, $sql2)) ->order("id");

Parameters:

array $select Array of select clauses for the union.

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::value	(	$	name,
		$	value,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

SqlQueryBuilder::values	(	$	values,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

Adds VALUES values.

Parameters:

string \| array	$name	Name of the value to add, or array of name=>value
	string\|SqlQueryExpr\|SqlQueryBuilder	Value
	boolean\|string	Bind name to use, or true for unqalified bind

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::where	(	$	cond,
		$	value = `null`,
		$	type = `null`,
		$	bindName = `false`,
		$	bindAlways = `true`
	)

Adds a WHERE condition to the query by AND.

If a value is passed as the second param, it will be quoted and replaced into the condition wherever a question-mark appears. Array values are quoted and comma-separated.

// simplest but non-secure $select->where("id = $id");

// secure (ID is quoted but matched anyway) $select->where('id = ?', $id);

// alternatively, with named binding $select->where('id = :id');

Note that it is more correct to use named bindings in your queries for values other than strings. When you use named bindings, don't forget to pass the values when actually making a query:

$db->fetchAll($select, array('id' => 5));

Parameters:

string	$cond	The WHERE condition.
mixed	$value	OPTIONAL The value to quote into the condition.
int	$type	OPTIONAL The type of the given value

Returns:: SqlQueryBuilder This SqlQueryBuilder object.

SqlQueryBuilder::whereEquals	(	$	column,
		$	value,
		$	equals = `true`,
		$	unifyType = `true`
	)

Parameters:

$column	Column
$equals	TRUE for equals, FALSE for not equals
$value	string\|null\|array
$unifyType	treats everything in the array as strings. If you are not sure if everything in the array is of the same type, and it doesn't contain any null values, leave this as true. Otherwise it may lead to inconsistent results.

Returns:: string

Field Documentation

SqlQueryBuilder::$_adapter [protected]

SqlQueryBuilder::$_assembledSql = null [protected]

SqlQueryBuilder::$_autoQuoteIdentifiers = true [protected]

SqlQueryBuilder::$_bind = array() [protected]

SqlQueryBuilder::$_joinTypes [static, protected]

Initial value:

 array(
  self::INNER_JOIN,
  self::LEFT_JOIN,
  self::RIGHT_JOIN,
  self::FULL_JOIN,
  self::CROSS_JOIN,
  self::NATURAL_JOIN,
 )

SqlQueryBuilder::$_parts = array() [protected]

SqlQueryBuilder::$_partsInit [static, protected]

SqlQueryBuilder::$_queryType [protected]

SqlQueryBuilder::$_queryTypes [static, protected]

Initial value:

 array(
  self::QUERY_SELECT,
  self::QUERY_DELETE,
  self::QUERY_INSERT,
  self::QUERY_REPLACE,
  self::QUERY_UPDATE,
 )

SqlQueryBuilder::$_tableCols = array() [protected]

SqlQueryBuilder::$_unionTypes [static, protected]

Initial value:

 array(
  self::SQL_UNION,
  self::SQL_UNION_ALL
 )

SqlQueryBuilder::$autoBindNamedPlaceholders = false

If true, every named autobind will bind a value.

SqlQueryBuilder::$autoBindsEnabled = true

If set to false, autoBind will always quote values.

SqlQueryBuilder::$findTableCorrelations = true

If enabled, it's possible to use table names instead of their correlation name in some functions.

const SqlQueryBuilder::BIGINT_TYPE = 1

const SqlQueryBuilder::COLTABLE_ALIAS = 2

const SqlQueryBuilder::COLTABLE_COLUMN = 1

const SqlQueryBuilder::COLTABLE_CORRELATION = 0

const SqlQueryBuilder::COLUMNS = 'columns'

const SqlQueryBuilder::CROSS_JOIN = 'cross join'

const SqlQueryBuilder::DISTINCT = 'distinct'

const SqlQueryBuilder::DUPLICATE_KEY_UPDATE = 'duplicateKeyUpdate'

const SqlQueryBuilder::FLOAT_TYPE = 2

const SqlQueryBuilder::FOR_UPDATE = 'forupdate'

const SqlQueryBuilder::FROM = 'from'

const SqlQueryBuilder::FULL_JOIN = 'full join'

const SqlQueryBuilder::GROUP = 'group'

const SqlQueryBuilder::HAVING = 'having'

const SqlQueryBuilder::INNER_JOIN = 'inner join'

const SqlQueryBuilder::INT_TYPE = 0

Use the INT_TYPE, BIGINT_TYPE, and FLOAT_TYPE with the quote() method.

const SqlQueryBuilder::LEFT_JOIN = 'left join'

const SqlQueryBuilder::LIMIT_COUNT = 'limitcount'

const SqlQueryBuilder::LIMIT_OFFSET = 'limitoffset'

const SqlQueryBuilder::NATURAL_JOIN = 'natural join'

const SqlQueryBuilder::ORDER = 'order'

const SqlQueryBuilder::PREFIX = 'prefix'

const SqlQueryBuilder::QUERY_DELETE = 'delete'

const SqlQueryBuilder::QUERY_INSERT = 'insert'

const SqlQueryBuilder::QUERY_REPLACE = 'replace'

const SqlQueryBuilder::QUERY_SELECT = 'select'

const SqlQueryBuilder::QUERY_SQL = 'sql'

Special query type to store any SQL inside.

Sql should be set using prefix() (preferable) or suffix()

The best way is to use SqlQueryBuilder::store or SqlQueryBuilder::parse

You can still use WHERE, ORDER and LIMIT parts, but the stored sql can't already use them.

const SqlQueryBuilder::QUERY_UPDATE = 'update'

const SqlQueryBuilder::RIGHT_JOIN = 'right join'

const SqlQueryBuilder::SET = 'set'

const SqlQueryBuilder::SQL_AND = 'AND'

const SqlQueryBuilder::SQL_AS = 'AS'

const SqlQueryBuilder::SQL_ASC = 'ASC'

const SqlQueryBuilder::SQL_COLUMNS = 'COLUMNS'

const SqlQueryBuilder::SQL_DEFAULT = 'DEFAULT'

const SqlQueryBuilder::SQL_DELETE = 'DELETE'

const SqlQueryBuilder::SQL_DESC = 'DESC'

const SqlQueryBuilder::SQL_DISTINCT = 'DISTINCT'

const SqlQueryBuilder::SQL_FOR_UPDATE = 'FOR UPDATE'

const SqlQueryBuilder::SQL_FROM = 'FROM'

const SqlQueryBuilder::SQL_GROUP_BY = 'GROUP BY'

const SqlQueryBuilder::SQL_HAVING = 'HAVING'

const SqlQueryBuilder::SQL_INSERT = 'INSERT'

const SqlQueryBuilder::SQL_INTO = 'INTO'

const SqlQueryBuilder::SQL_ON = 'ON'

const SqlQueryBuilder::SQL_ON_DUPLICATE_KEY_UPDATE = 'ON DUPLICATE KEY UPDATE'

const SqlQueryBuilder::SQL_OR = 'OR'

const SqlQueryBuilder::SQL_ORDER_BY = 'ORDER BY'

const SqlQueryBuilder::SQL_REPLACE = 'REPLACE'

const SqlQueryBuilder::SQL_SELECT = 'SELECT'

const SqlQueryBuilder::SQL_SET = 'SET'

const SqlQueryBuilder::SQL_UNION = 'UNION'

const SqlQueryBuilder::SQL_UNION_ALL = 'UNION ALL'

const SqlQueryBuilder::SQL_UPDATE = 'UPDATE'

const SqlQueryBuilder::SQL_VALUES = 'VALUES'

const SqlQueryBuilder::SQL_WHERE = 'WHERE'

const SqlQueryBuilder::SQL_WILDCARD = '*'

const SqlQueryBuilder::STRING_TYPE = 3

const SqlQueryBuilder::SUFFIX = 'suffix'

const SqlQueryBuilder::UNION = 'union'

const SqlQueryBuilder::VALUES = 'values'

const SqlQueryBuilder::WHERE = 'where'

The documentation for this class was generated from the following file:

include/core/SqlQueryBuilder.php

Public Member Functions

Static Public Member Functions

Data Fields

Protected Member Functions

Protected Attributes

Static Protected Attributes

Detailed Description

Constructor & Destructor Documentation

Member Function Documentation

Field Documentation