core/xpdo/om/xpdoquery.class.php

Show:

inherited

Classes
- \xPDOQuery
- \xPDOQueryCondition

Table of Contents

A class for constructing complex SQL statements using a model-aware API.

Package: xpdo
Subpackage: om

\xPDOQuery
jump to top

Package: xpdo\om

An xPDOCriteria derivative with methods for constructing complex statements.

Parent(s): \xPDOCriteria
Children: \xPDOQuery_sqlite; \xPDOQuery_mysql; \xPDOQuery_sqlsrv
Abstract

Constants

 
                SQL_AND
                = 'AND'

 
                SQL_OR
                = 'OR'

 
                SQL_JOIN_CROSS
                = 'JOIN'

 
                SQL_JOIN_LEFT
                = 'LEFT JOIN'

 
                SQL_JOIN_RIGHT
                = 'RIGHT JOIN'

 
                SQL_JOIN_NATURAL_LEFT
                = 'NATURAL LEFT JOIN'

 
                SQL_JOIN_NATURAL_RIGHT
                = 'NATURAL RIGHT JOIN'

 
                SQL_JOIN_STRAIGHT
                = 'STRAIGHT_JOIN'

Properties


                 $_alias= 'null'

Default valuenullDetails

Type: n/a


                 $_class= 'null'

Default valuenullDetails

Type: n/a

array
                 $_operators= 'array (
        '=',
        '!=',
        '<',
        '<=',
        '>',
        '>=',
        '<=>',
        ' LIKE ',
        ' IS NULL',
        ' IS NOT NULL',
        ' BETWEEN ',
        ' IN ',
        ' IN(',
        ' NOT(',
        ' NOT (',
        ' NOT IN ',
        ' NOT IN(',
        ' EXISTS (',
        ' EXISTS(',
        ' NOT EXISTS (',
        ' NOT EXISTS(',
        ' COALESCE(',
        ' GREATEST(',
        ' INTERVAL(',
        ' LEAST(',
        'MATCH(',
        'MATCH ('
    )'

An array of symbols and keywords indicative of SQL operators.

Default value

array (
        '=',
        '!=',
        '<',
        '<=',
        '>',
        '>=',
        '<=>',
        ' LIKE ',
        ' IS NULL',
        ' IS NOT NULL',
        ' BETWEEN ',
        ' IN ',
        ' IN(',
        ' NOT(',
        ' NOT (',
        ' NOT IN ',
        ' NOT IN(',
        ' EXISTS (',
        ' EXISTS(',
        ' NOT EXISTS (',
        ' NOT EXISTS(',
        ' COALESCE(',
        ' GREATEST(',
        ' INTERVAL(',
        ' LEAST(',
        'MATCH(',
        'MATCH ('
    )

Details

Type: array
Todo: Refactor this to separate xPDOQuery operators from db-specific conditional statement identifiers.


                 $_quotable= 'array ('string', 'password', 'date', 'datetime', 'timestamp', 'time')'

Default valuearray ('string', 'password', 'date', 'datetime', 'timestamp', 'time')Details

Type: n/a


                 $_tableClass= 'null'

Default valuenullDetails

Type: n/a


                 $bindings= 'array ()'

inheritedInherited from: \xPDOCriteria::$$bindings

Default valuearray ()Details

Type: n/a
Inherited_from: \xPDOCriteria::$$bindings


                 $cacheFlag= 'false'

inheritedInherited from: \xPDOCriteria::$$cacheFlag

Default valuefalseDetails

Type: n/a
Inherited_from: \xPDOCriteria::$$cacheFlag


                 $graph= 'array ()'

Default valuearray ()Details

Type: n/a


                 $query= 'array (
        'command' => 'SELECT',
        'distinct' => '',
        'columns' => '',
        'from' => array (
            'tables' => array (),
            'joins' => array (),
        ),
        'set' => array (),
        'where' => array (),
        'groupby' => array (),
        'having' => array (),
        'orderby' => array (),
        'offset' => '',
        'limit' => '',
    )'

Default value

array (
        'command' => 'SELECT',
        'distinct' => '',
        'columns' => '',
        'from' => array (
            'tables' => array (),
            'joins' => array (),
        ),
        'set' => array (),
        'where' => array (),
        'groupby' => array (),
        'having' => array (),
        'orderby' => array (),
        'offset' => '',
        'limit' => '',
    )

Details

Type: n/a


                 $sql= ''''

inheritedInherited from: \xPDOCriteria::$$sql

Default value''Details

Type: n/a
Inherited_from: \xPDOCriteria::$$sql


                 $stmt= 'null'

inheritedInherited from: \xPDOCriteria::$$stmt

Default valuenullDetails

Type: n/a
Inherited_from: \xPDOCriteria::$$stmt

Methods

__construct(
          \xPDO $xpdo,  $class,  $criteria
              =
              null
          )
        
        :
          \xPDOCriteria

The constructor for a new xPDOCriteria instance.

The constructor optionally prepares provided SQL and/or parameter bindings. Setting the bindings via the constructor or with the {@link xPDOCriteria::bind()} function allows you to make use of the data object caching layer.

The statement will not be prepared immediately if the cacheFlag value is true or a positive integer, in order to allow the result to be found in the cache before being queried from an actual database connection.

Parameters

Name	Type	Description
$xpdo	\xPDO	&$xpdo An xPDO instance that will control this criteria.
$class
$criteria

Returns

Type	Description
\xPDOCriteria

andCondition(
           $conditions,  $binding
              =
              null,  $group
              =
              0
          )
        
        :
          void

Parameters

Name	Type	Description
$conditions
$binding
$group

bind(
          array $bindings
              =
              array (), boolean $byValue
              =
              true, boolean | integer $cacheFlag
              =
              false
          )
        
        :
          void

inherited

Binds an array of key/value pairs to the xPDOCriteria prepared statement.

Inherited from: \xPDOCriteria::bind()

Use this method to bind parameters in a way that makes it possible to cache results of previous executions of the criteria or compare the criteria to other individual or collections of criteria.

Parameters

Name	Type	Description
$bindings	array	Bindings to merge with any existing bindings defined for this xPDOCriteria instance. Bindings can be simple associative array of key-value pairs or the value for each key can contain elements titled value, type, and length corresponding to the appropriate parameters in the PDOStatement::bindValue() and PDOStatement::bindParam() functions.
$byValue	boolean	Determines if the $bindings are to be bound as parameters (by variable reference, the default behavior) or by direct value (if true).
$cacheFlag	boolean \| integer	The cacheFlag indicates the cache state of the xPDOCriteria object and can be absolutely off (false), absolutely on (true), or an integer indicating the number of seconds the result will live in the cache.

bindGraph(
          mixed $graph
          )
        
        :
          \xPDOQuery

Bind an object graph to the query.

Parameters

Name	Type	Description
$graph	mixed	An array or JSON graph of related objects.

Returns

Type	Description
\xPDOQuery	Returns the instance.

Details

Used_by: \modX::getCollectionGraph()
Used_by: \xPDO::getCollectionGraph()

bindGraphNode(
          string $parentClass, string $parentAlias, string $classAlias, array $relations
          )
        
        :
          void

Bind the node of an object graph to the query.

Parameters

Name	Type	Description
$parentClass	string	The class representing the relation parent.
$parentAlias	string	The alias the class is assuming.
$classAlias	string	The class representing the related graph node.
$relations	array	Child relations of the current graph node.

buildConditionalClause(
          array | \xPDOQueryCondition $conditions, string $conjunction
              =
              xPDOQuery::SQL_AND, boolean $isFirst
              =
              true
          )
        
        :
          string

Builds conditional clauses from xPDO condition expressions.

Parameters

Name	Type	Description
$conditions	array \| \xPDOQueryCondition	An array of conditions or an xPDOQueryCondition instance.
$conjunction	string	Either xPDOQuery:SQL_AND or xPDOQuery::SQL_OR
$isFirst	boolean	Indicates if this is the first condition in an array.

Returns

Type	Description
string	The generated SQL clause.

command(
          string $command
              =
              'SELECT'
          )
        
        :
          \xPDOQuery

Set the type of SQL command you want to build.

The default is SELECT, though it also supports DELETE and UPDATE.

Parameters

Name	Type	Description
$command	string	The type of SQL statement represented by this object. Default is 'SELECT'.

Returns

Type	Description
\xPDOQuery	Returns the current object for convenience.

condition(
          string $target, mixed $conditions
              =
              '1', string $conjunction
              =
              xPDOQuery::SQL_AND, mixed $binding
              =
              null, integer $condGroup
              =
              0
          )
        
        :
          \xPDOQuery

Add a condition to the query.

Parameters

Name	Type	Description
$target	string	The target clause for the condition.
$conditions	mixed	A valid xPDO criteria expression.
$conjunction	string	The conjunction to use when appending this condition, i.e., AND or OR.
$binding	mixed	A value or PDO binding representation of a value for the condition.
$condGroup	integer	A numeric identifier for associating conditions into groups.

Returns

Type	Description
\xPDOQuery	Returns the instance.

construct(
          
          )
        
        :
          boolean

abstract

Constructs the SQL query from the xPDOQuery definition.

Returns

Type	Description
boolean	Returns true if a SQL statement was successfully constructed.

distinct(
          null | boolean $on
              =
              null
          )
        
        :
          \xPDOQuery

Set the DISTINCT attribute of the query.

Parameters

Name	Type	Description
$on	null \| boolean	Defines how to set the distinct attribute: - null (default) indicates the distinct attribute should be toggled - any other value is treated as a boolean, i.e. true to set DISTINCT, false to unset

Returns

Type	Description
\xPDOQuery	Returns the current object for convenience.

equals(
          object $obj
          )
        
        :
          boolean

inherited

Compares to see if two xPDOCriteria instances are the same.

Inherited from: \xPDOCriteria::equals()

Parameters

Name	Type	Description
$obj	object	A xPDOCriteria object to compare to this one.

Returns

Type	Description
boolean	true if they are both equal is SQL and bindings, otherwise false.

from(
          string $class, string $alias
              =
              ''
          )
        
        :
          \xPDOQuery

Add a FROM clause to the query.

Parameters

Name	Type	Description
$class	string	The class representing the table to add.
$alias	string	An optional alias for the class.

Returns

Type	Description
\xPDOQuery	Returns the instance.

getAlias(
          
          )
        
        :
          void

getClass(
          
          )
        
        :
          void

getTableClass(
          
          )
        
        :
          void

groupby(
          string $column, string $direction
              =
              ''
          )
        
        :
          \xPDOQuery

Add an GROUP BY clause to the query.

Parameters

Name	Type	Description
$column	string	Column identifier to group by.
$direction	string	The direction to sort by, ASC or DESC.

Returns

Type	Description
\xPDOQuery	Returns the instance.

having(
           $conditions
          )
        
        :
          void

Parameters

Name	Type	Description
$conditions

hydrateGraph(
          array $rows,  $cacheFlag
              =
              true
          )
        
        :
          array

Hydrates a graph of related objects from a single result set.

Parameters

Name	Type	Description
$rows	array	A collection of result set rows for hydrating the graph.
$cacheFlag

Returns

Type	Description
array	A collection of objects with all related objects from the graph pre-populated.

hydrateGraphNode(
          array $row, \xPDOObject $instance, string $alias, array $relations
          )
        
        :
          void

Hydrates a node of the object graph.

Parameters

Name	Type	Description
$row	array	The result set representing the current node.
$instance	\xPDOObject	The xPDOObject instance to be hydrated from the node.
$alias	string	The alias identifying the object in the parent relationship.
$relations	array	Child relations of the current node.

hydrateGraphParent(
           $instances,  $row
          )
        
        :
          void

Parameters

Name	Type	Description
$instances
$row

innerJoin(
           $class,  $alias
              =
              '',  $conditions
              =
              array (),  $conjunction
              =
              xPDOQuery::SQL_AND,  $binding
              =
              null,  $condGroup
              =
              0
          )
        
        :
          void

Parameters

Name	Type	Description
$class
$alias
$conditions
$conjunction
$binding
$condGroup

isConditionalClause(
          string $string
          )
        
        :
          boolean

Determines if a string contains a conditional operator.

Parameters

Name	Type	Description
$string	string	The string to evaluate.

Returns

Type	Description
boolean	True if the string is a complete conditional SQL clause.

join(
          string $class, string $alias
              =
              '', string $type
              =
              xPDOQuery::SQL_JOIN_CROSS, mixed $conditions
              =
              array (), string $conjunction
              =
              xPDOQuery::SQL_AND, array $binding
              =
              null, int $condGroup
              =
              0
          )
        
        :
          \xPDOQuery

Join a table represented by the specified class.

Parameters

Name	Type	Description
$class	string	The classname (or relation alias for aggregates and composites) of representing the table to be joined.
$alias	string	An optional alias to represent the joined table in the constructed query.
$type	string	The type of join to perform. See the xPDOQuery::SQL_JOIN constants.
$conditions	mixed	Conditions of the join specified in any xPDO compatible criteria object or expression.
$conjunction	string	A conjunction to be applied to the condition or conditions supplied.
$binding	array	Optional bindings to accompany the conditions.
$condGroup	int	An optional identifier for adding the conditions to a specific set of conjoined expressions.

Returns

Type	Description
\xPDOQuery	Returns the current object for convenience.

leftJoin(
           $class,  $alias
              =
              '',  $conditions
              =
              array (),  $conjunction
              =
              xPDOQuery::SQL_AND,  $binding
              =
              null,  $condGroup
              =
              0
          )
        
        :
          void

Parameters

Name	Type	Description
$class
$alias
$conditions
$conjunction
$binding
$condGroup

limit(
          integer $limit, integer $offset
              =
              0
          )
        
        :
          \xPDOQuery

Add a LIMIT/OFFSET clause to the query.

Parameters

Name	Type	Description
$limit	integer	The number of records to return.
$offset	integer	The location in the result set to start from.

Returns

Type	Description
\xPDOQuery	Returns the instance.

orCondition(
           $conditions,  $binding
              =
              null,  $group
              =
              0
          )
        
        :
          void

Parameters

Name	Type	Description
$conditions
$binding
$group

parseConditions(
          mixed $conditions, string $conjunction
              =
              xPDOQuery::SQL_AND
          )
        
        :
          void

Parses an xPDO condition expression.

Parameters

Name	Type	Description
$conditions	mixed	A valid xPDO condition expression.
$conjunction	string	The optional conjunction for the condition( s ).

prepare(
          array $bindings
              =
              array (), boolean $byValue
              =
              true, boolean | integer $cacheFlag
              =
              null
          )
        
        :
          \PDOStatement

Prepares the xPDOQuery for execution.

The {@link xPDOCriteria::$sql} attribute must be set in order to prepare the statement. You can also pass bindings directly to this function and they will be run through {@link xPDOCriteria::bind()} if the statement is successfully prepared.

If the {@link xPDOCriteria::$stmt} already exists, it is simply returned.

Parameters

Name	Type	Description
$bindings	array	Bindings to merge with any existing bindings defined for this xPDOCriteria instance. Bindings can be simple associative array of key-value pairs or the value for each key can contain elements titled value, type, and length corresponding to the appropriate parameters in the PDOStatement::bindValue() and PDOStatement::bindParam() functions.
$byValue	boolean	Determines if the $bindings are to be bound as parameters (by variable reference, the default behavior) or by direct value (if true).
$cacheFlag	boolean \| integer	The cacheFlag indicates the cache state of the xPDOCriteria object and can be absolutely off (false), absolutely on (true), or an integer indicating the number of seconds the result will live in the cache.

Returns

Type	Description
\PDOStatement	The PDOStatement representing the prepared query.

rightJoin(
           $class,  $alias
              =
              '',  $conditions
              =
              array (),  $conjunction
              =
              xPDOQuery::SQL_AND,  $binding
              =
              null,  $condGroup
              =
              0
          )
        
        :
          void

Parameters

Name	Type	Description
$class
$alias
$conditions
$conjunction
$binding
$condGroup

select(
          string $columns
              =
              '*'
          )
        
        :
          \xPDOQuery

Specify columns to return from the SQL query.

Parameters

Name	Type	Description
$columns	string	Columns to return from the query.

Returns

Type	Description
\xPDOQuery	Returns the current object for convenience.

set(
          array $values
          )
        
        :
          \xPDOQuery

Specify the SET clause(s) for a SQL UPDATE query.

Parameters

Name	Type	Description
$values	array	An associative array of fields and the values to set them to.

Returns

Type	Description
\xPDOQuery	Returns a reference to the current instance for convenience.

setClassAlias(
          string $alias
              =
              ''
          )
        
        :
          \xPDOQuery

Sets a SQL alias for the table represented by the main class.

Parameters

Name	Type	Description
$alias	string	An alias for the main table for the SQL statement.

Returns

Type	Description
\xPDOQuery	Returns the current object for convenience.

sortby(
          string $column, string $direction
              =
              'ASC'
          )
        
        :
          \xPDOQuery

Add an ORDER BY clause to the query.

Parameters

Name	Type	Description
$column	string	Column identifier to sort by.
$direction	string	The direction to sort by, ASC or DESC.

Returns

Type	Description
\xPDOQuery	Returns the instance.

toSQL(
          
          )
        
        :
          string

inherited

Converts the current xPDOQuery to parsed SQL.

Inherited from: \xPDOCriteria::toSQL()

Returns

Type	Description
string	The parsed SQL query.

Details

Access: public

where(
          mixed $conditions
              =
              '', string $conjunction
              =
              xPDOQuery::SQL_AND, mixed $binding
              =
              null, integer $condGroup
              =
              0
          )
        
        :
          \xPDOQuery

Add a WHERE condition to the query.

Parameters

Name	Type	Description
$conditions	mixed	A valid xPDO criteria expression.
$conjunction	string	The conjunction to use when appending this condition, i.e., AND or OR.
$binding	mixed	A value or PDO binding representation of a value for the condition.
$condGroup	integer	A numeric identifier for associating conditions into groups.

Returns

Type	Description
\xPDOQuery	Returns the instance.

wrap(
          \xPDOCriteria $criteria
          )
        
        :
          void

Wrap an existing xPDOCriteria into this xPDOQuery instance.

Parameters

Name	Type	Description
$criteria	\xPDOCriteria

\xPDOQueryCondition
jump to top

Package: xpdo\om

Abstracts individual query conditions used in xPDOQuery instances.

Properties

array
                 $binding= 'array()'

An array of value/parameter bindings for the condition.

Default valuearray()Details

Type: array

string
                 $conjunction= 'xPDOQuery::SQL_AND'

The conjunction identifying how the condition is related to the previous condition(s).

Default valuexPDOQuery::SQL_ANDDetails

Type: string

string
                 $sql= ''''

The SQL string for the condition.

Default value''Details

Type: string

Methods

__construct(
          array $properties
          )
        
        :
          void

The constructor for creating an xPDOQueryCondition instance.

Parameters

Name	Type	Description
$properties	array	An array of properties representing the condition.

core/xpdo/om/xpdoquery.class.php

\xPDOQueryjump to top

Constants

Properties

Methods

\xPDOQueryConditionjump to top

Properties

Methods

\xPDOQuery
jump to top

\xPDOQueryCondition
jump to top