Simple PHP SQL Query Builder for PDO

QueryBuilder.php

<?php

class QueryBuilder {
	const QUERY_SELECT 		 = 1;
	const QUERY_INSERT 		 = 2;
	const QUERY_UPDATE 		 = 3;
	const QUERY_DELETE 		 = 4;
	const QUERY_REPLACE 	 = 5;

	const PARAMETER_STRING 	 = 1;
	const PARAMETER_INT 	 = 2;
	const PARAMETER_DATE 	 = 3;
	const PARAMETER_DATETIME = 4;

	const JOIN_LEFT  		 = 1;
	const JOIN_INNER 		 = 2;
	const JOIN_OUTER 		 = 3;
	const JOIN_CROSS 		 = 4;

	private $parts = array(
		'select' 	=> array(),
		'from' 		=> array(),
		'join' 		=> array(),
		'where' 	=> null,
		'groupBy' 	=> array(),
		'orderBy' 	=> array(),
		'limit' 	=> null,
		'offset' 	=> null,
		'indexBy'	=> null,
	);

	protected $fields = array();

	protected $parameters = array();

	protected $type;

	protected $db;

	protected $aliases = array();

	protected $isExplain = false;

	/**
	 * @param Database $db
	 */
	public function __construct(Database $db) 
	{
		$this->db = $db;
	}

	/**
	 * __toString
	 *
	 * Returns the query as a string
	 *
	 * @return string
	 */
	public function __toString() 
	{

		try {
			$query = $this->buildQuery();
		} catch (\Exception $e) {
			$query = null;
		}

		return $query;
	}

	/**
	 * select
	 *
	 * Set the select portion of the query for SELECT queries
	 *
	 * @param $table
	 * @return $this
	 */
	public function select($table) 
	{
		return $this->addSelect($table);
	}

	/**
	 * addSelect
	 *
	 * Add to the select portion of the query for SELECT queries
	 *
	 * @param $what
	 * @throws \InvalidArgumentException
	 * @return $this
	 */
	public function addSelect($what) 
	{
		$this->type = static::QUERY_SELECT;

		if (is_array($what)) {

			foreach ($what as $w) {
				if (!is_string($w)) {
					throw new \InvalidArgumentException('addSelect requires a string or an array of strings');
				}
				$this->addPart('select', $w);
			}

		} else {

			if (!is_string($what)) {
				throw new \InvalidArgumentException('addSelect requires a string or an array of strings');
			}

			$this->addPart('select', $what);
		}

		return $this;
	}

	/**
	 * setSelect - Set the select of the query, overwritting any existing declaration
	 * 
	 * @param string $what 
	 */
	public function setSelect($what)
	{
		$this->parts['select'] = array($what);
		return $this;
	}

	/**
	 * insert
	 *
	 * Sets the query type to insert
	 *
	 * @return $this
	 */
	public function insert($table = null, $alias = null) 
	{
		$this->type = static::QUERY_INSERT;

		if (!is_null($table)) {
			$this->from($table, $alias);
		}

		return $this;
	}

	/**
	 * replace
	 *
	 * Sets the query type to replace
	 *
	 * @return $this
	 */
	public function replace($table = null, $alias = null) 
	{
		$this->type = static::QUERY_REPLACE;

		if (!is_null($table)) {
			$this->from($table, $alias);
		}

		return $this;
	}

	/**
	 * update
	 *
	 * Sets the query type to update
	 *
	 * @return $this
	 */
	public function update($table = null, $alias = null) 
	{
		if (!is_null($table)) {
			$this->from($table, $alias);
		}

		$this->type = static::QUERY_UPDATE;

		return $this;
	}

	/**
	 * delete
	 *
	 * Sets the query type to delete
	 *
	 * @param string $table
	 * @return $this
	 */
	public function delete($table = null, $alias = null) 
	{
		if (!is_null($table)) {
			$this->from($table, $alias);
		}

		$this->type = static::QUERY_DELETE;

		return $this;
	}

	/**
	 * from
	 *
	 * Set the table to select/insert/update from
	 *
	 * @param $table
	 * @param $alias
	 * @return $this
	 */
	public function from($table, $alias = null) 
	{
		$this->registerAlias($table, $alias);		

		return $this->addPart('from', array(
			'table' => $table,
			'alias' => $alias,
		));
	}

	/**
	 * into
	 * 
	 * Alias function ::from()
	 * 
	 * @see self::from()
	 */
	public function into($table, $alias = null) 
	{
		return $this->from($table, $alias);
	}

	/**
	 * leftJoin
	 *
	 * Add a left join to the query
	 *
	 * @param $on
	 * @param $what
	 * @param $alias
	 * @param $conditions
	 * @return $this|QueryBuilder
	 */
	public function leftJoin($table, $alias, $conditions) 
	{
		return $this->addJoin(
			static::JOIN_LEFT,
			$table,
			$alias,
			$conditions
		);
	}

	/**
	 * innerJoin
	 *
	 * Add an inner join to the query
	 *
	 * @param $on
	 * @param $what
	 * @param $alias
	 * @param $conditions
	 * @return $this|QueryBuilder
	 */
	public function innerJoin($table, $alias, $conditions) 
	{
		return $this->addJoin(
			static::JOIN_INNER,
			$table,
			$alias,
			$conditions
		);
	}

	/**
	 * outerJoin
	 *
	 * Add an outer join to the query
	 *
	 * @param $on
	 * @param $what
	 * @param $alias
	 * @param $conditions
	 * @return $this|QueryBuilder
	 */
	public function outerJoin($table, $alias, $conditions) 
	{
		return $this->addJoin(
			static::JOIN_OUTER,
			$table,
			$alias,
			$conditions
		);
	}

	/**
	 * where
	 *
	 * Set the WHERE condition
	 *
	 * @param $conditions
	 * @param array $parameters
	 * @return $this
	 */
	public function where($conditions, array $parameters = array()) 
	{
		$this->addParameters($parameters);

		return $this->addPart('where', $conditions);
	}

	/**
	 * andWhere
	 *
	 * Add an AND WHERE condition to the expression
	 *
	 * @param $conditions
	 * @param array $parameters
	 * @return $this
	 */
	public function andWhere($conditions, array $parameters = array()) 
	{
		$part = $this->getPart('where');

		if (is_null($part)) {
			return $this->where($conditions, $parameters);
		}

		if ($part instanceof QueryBuilderExpression) {
			$part->andX($conditions);
		} else {
			$expr = $this->expr($part);
			$expr->andX($conditions);
			$this->parts['where'] = $expr;
		}

		$this->addParameters($parameters);

		return $this;
	}

	/**
	 * orWhere
	 *
	 * Add an OR WHERE condition to the expression
	 *
	 * @param $conditions
	 * @param array $parameters
	 * @return $this
	 */
	public function orWhere($conditions, array $parameters = array()) 
	{
		$part = $this->getPart('where');

		if (is_null($part)) {
			return $this->where($conditions, $parameters);
		}

		if ($part instanceof QueryBuilderExpression) {
			$part->orX($conditions);
		} else {
			$expr = $this->expr($part);
			$expr->orX($conditions);
			$this->setPart('where', $expr);
		}

		$this->addParameters($parameters);

		return $this;
	}

	/**
	 * orderBy
	 *
	 * Add a order by statement
	 *
	 * @param string $order [..] repeatable
	 * @return $this
	 */
	public function orderBy($order)
	{
		$args = func_get_args();
		foreach ($args as $o) {
			$this->addPart('orderBy', $o);
		}

		return $this;
	}
	
	/**
	* clearOrderBy
	*/
	public function clearOrderBy()
	{
		$this->parts['orderBy'] = array();
		return $this;
	}

	/**
	 * groupBy
	 *
	 * Add a group by statement
	 *
	 * @param string $field [..] repeatable
	 * @return $this
	 */
	public function groupBy($field)
	{
		$args = func_get_args();
		foreach ($args as $f) {
			$this->addPart('groupBy', $f);
		}
		return $this;
	}

	/**
	* clearGroupBy
	*/
	public function clearGroupBy()
	{
		$this->parts['groupBy'] = array();
		return $this;
	}

	/**
	 * setField
	 *
	 * Sets a fields value for UPDATE and INSERT statements
	 *
	 * @param string $field - Can be an array of field => value or a single field name
	 * @param string $value - Optional value to set the field to, required if $filed is not an array
	 * @param int $type - The type of field
	 * @return $this
	 */
	public function set($field, $value = null, $type = self::PARAMETER_STRING) 
	{
		if (is_array($field)) {
			foreach ($field as $name => $_value) {
				$this->fields[$name] = array(
					'value' 	  => $_value,
					'type'  	  => $type,
				);
			}
		} else {
			$this->fields[$field] = array(
				'value' 	  => $value,
				'type'  	  => $type,
			);
		}
		
		return $this;
	}

	/**
	 * setParameter
	 *
	 * Set/replace a single parameter
	 *
	 * @param $parameter
	 * @param $value
	 * @return $this
	 */
	public function setParameter($parameter, $value) 
	{
		$this->parameters[$parameter] = $value;
		return $this;
	}

	/**
	 * setParameters
	 *
	 * Set and replace the existing parameters with the provided
	 *
	 * @param $parameters
	 * @return $this
	 */
	public function setParameters($parameters) 
	{
		$this->parameters = $parameters;
		return $this;
	}

	/**
	 * addParameters
	 *
	 * Add an array of parameters
	 *
	 * @param $parameters
	 * @return $this
	 */
	public function addParameters(array $parameters) 
	{
		foreach ($parameters as $key => $value) {
			$this->setParameter($key, $value);
		}

		return $this;
	}

	/**
	 * hasParameter
	 *
	 * Check if the query has a parameter already set
	 *
	 * @param string $parameter
	 * @return bool
	 */
	public function hasParameter($parameter) 
	{
		return isset($this->parameters[$parameter]);
	}

	/**
	 * getParameters
	 *
	 * Get all currently set placeholder parameters
	 *
	 * @return array
	 */
	public function getParameters() 
	{
		return $this->parameters;
	}

	/**
	 * execute
	 *
	 * Executes the currently built query
	 * and returns the result set (if any)
	 *
	 * @return array|bool
	 */
	public function execute($type = PDO::FETCH_ASSOC) 
	{
		$query = $this->buildQuery();

		//echo $query . PHP_EOL;

		$statement = $this->db->prepare($query);

		$result = $statement->execute($this->getParameters());

		if ($this->type == static::QUERY_SELECT) {
			return $statement; 
		}

		return $result;
	}

	/**
	 * fetchAll
	 *
	 * Executes the statement and returns all results
	 *
	 * @param int $type - One of PDO::FETCH_*, defaults to FETCH_ASSOC
	 * @return Ambigous <multitype:, boolean>
	 */
	public function fetchAll($type = PDO::FETCH_ASSOC, $class = null, array $ctorargs = array()) 
	{
		if ($this->type != static::QUERY_SELECT) {
			return $statement;
		}	

		$statement = $this->execute();

		if ($type == PDO::FETCH_CLASS && !is_null($class)) {
			if (!class_exists($class)) {
				throw new \Exception(sprintf('Class %s does not exist', $class));
			}
			$statement->setFetchMode($type, $class, $ctorargs);
			$type = null;
		}

		if ($this->getIndexBy()) {
			$result = $statement->fetchAll($type);
			$return = array();
			foreach ($result as $row) {
				$return[$row[$this->getIndexBy()]] = $row;
			}
			return $return;
		} else {
			return $statement->fetchAll($type);
		}
		
	}

	/**
	 * fetchOne
	 *
	 * Executes the query and returns a single result
	 *
	 * @param unknown $type
	 * @return Ambigous <multitype:, boolean>
	 */
	public function fetchOne($type = PDO::FETCH_ASSOC, $class = null, array $ctorargs = array()) 
	{
		if ($this->type != static::QUERY_SELECT) {
			return $this->execute();
		}

		$statement = $this->execute();

		if ($type == PDO::FETCH_CLASS && !is_null($class)) {
			if (!class_exists($class)) {
				throw new \Exception(sprintf('Class %s does not exist', $class));
			}
			$statement->setFetchMode($type, $class, $ctorargs);
			$type = null;
		}

		$statement = $this->execute();

		return $statement->fetch($type);
	}

	/**
	 * buildQuery
	 *
	 * Builds the query into an string ready to be prepared
	 *
	 * @return string
	 * @throws Exception
	 */
	private function buildQuery() 
	{

		if ($this->type == static::QUERY_SELECT && !count($this->getPart('select'))) {
			throw new \Exception('Select query must contain select content');
		} else if (!$this->getPart('from')) {
			throw new \Exception('From query part not specified');
		}

		$query = array();

		if ($this->isExplain()) {
			$query[] = 'EXPLAIN';
		}

		switch ($this->type) {
			case static::QUERY_INSERT:	$query[] = 'INSERT INTO';	break;
			case static::QUERY_REPLACE:	$query[] = 'REPLACE INTO'; 	break;
			case static::QUERY_UPDATE:	$query[] = 'UPDATE'; 	 	break;
			case static::QUERY_DELETE:	$query[] = 'DELETE FROM'; 	break;
			default:					$query[] = 'SELECT';		break;
		}

		if ($this->type == static::QUERY_SELECT) {
			$query[] = implode(', ', $this->getPart('select'));
			$query[] = 'FROM';
		}

		foreach ($this->getPart('from') as $from) {
			$query[] = trim(sprintf('%s %s', $from['table'], $from['alias']));
		}

		foreach ($this->getPart('join') as $join) {
			switch ($join['type']) {
				case static::JOIN_INNER:	$symbol = 'INNER JOIN';			break;
				case static::JOIN_LEFT:		$symbol = 'LEFT JOIN';			break;
				case static::JOIN_OUTER:	$symbol = 'OUTER JOIN';			break;
				case static::JOIN_CROSS:	$symbol = 'CROSS JOIN';			break;
				default:					$symbol = 'JOIN';				break;
			}

			$query[] = sprintf('%s %s %s ON %s',
				$symbol,
				$join['table'],
				$join['alias'],
				$join['conditions']
			);
		}

		// set for update, replace, or insert
		if ($this->type == static::QUERY_INSERT || $this->type == static::QUERY_UPDATE || $this->type == static::QUERY_REPLACE) {
			$query[] = 'SET';
			$fields = array();
			foreach ($this->fields as $field => $data) {
				$placeholderName = $this->createPlaceholderName($field);

				$fields[] = sprintf('%s = :%s', $field, $placeholderName);
				if (!$this->hasParameter($placeholderName)) {
					$this->setParameter($placeholderName, $data['value']);
				}
			}

			$query[] = implode(', ', $fields);
		}

		if ($this->type != static::QUERY_INSERT && $this->hasPart('where')) {
			
			$where = $this->getPart('where');

			$query[] = ' WHERE ';

			if ($where instanceof QueryBuilderExpression) {
				$query[] = $where->build();
			} else {
				$query[] = $where;
			}
		}
		
		// grouping
		if ($this->hasPart('groupBy')) {
			$query[] = ' GROUP BY ' . implode(', ', $this->getPart('groupBy'));
		}

		// ordering
		if ($this->hasPart('orderBy')) {
			$query[] = ' ORDER BY ' . implode(', ', $this->getPart('orderBy'));
		}

		// limiting
		if (!is_null($this->parts['limit']) || $this->parts['limit'] === 0) {
			$query[] = sprintf('LIMIT %d, %d', (is_null($this->parts['offset']) ? 0 : $this->parts['offset']), $this->parts['limit']);
		}

		$query = trim(preg_replace('/\s{2,}/', ' ', implode(' ', $query)));

		// sanity checks on set parameters and passed parameters
		// right now query builder only supports NAMED parameters, and not generic
		// placeholder parameters
		preg_match_all('/:[A-Z0-9_]{1,}/i', $query, $matches);
		if (count($matches)) {
			$matches = array_unique($matches[0]);
			foreach ($matches as $match) {
				$match = str_replace(':', null, $match);
				if (!$this->hasParameter($match)) {
					throw new \BadMethodCallException(sprintf('Query expected parameter :%s to be defined but was not set', $match));
				}
			}
		} else if (!count($matches) && count($this->parameters)) {

		}

		return (string) $query;
	}

	/**
	 * limit
	 *
	 * Limit the number of records returned
	 *
	 * @param int $limit
	 * @param int|null $offset
	 * @return $this
	 */
	public function limit($limit, $offset = null) 
	{
		$this->parts['limit'] = (int) $limit;
		if (!is_null($offset)) {
			$this->parts['offset'] = (int) $offset;
		}
		return $this;
	}

	/**
	 * offset
	 *
	 * Set an offset in the query
	 *
	 * @param $offset
	 * @return $this
	 */
	public function offset($offset) 
	{
		$this->parts['offset'] = (int) $offset;
		return $this;
	}

	/**
	* indexBy
	*
	* Used to reindex the result set for multiple row queries. 
	* Use caution when using this, be sure the field is unique (i.e. primary key)
	* to avoid clashes.
	*
	* @param string $field
	*
	* @return $this
	*/
	public function indexBy($field)
	{
		$this->parts['indexBy'] = $field;
		return $this;
	}

	/**
	* getIndexBy
	*
	* @return string
	*/
	public function getIndexBy()
	{
		return $this->parts['indexBy'];
	}

	/**
	 * expr
	 *
	 * Creates a new QueryBuilderExpression object
	 *
	 * @param string|QueryBuilderExpression $expr
	 * @return QueryBuilderExpression
	 */
	public function expr($expr = null) 
	{
		return new QueryBuilderExpression($this, $expr);
	}

	/**
	 * addJoin
	 *
	 * @param int $type
	 * @param string $table
	 * @param string $alias
	 * @param string $conditions
	 * @return $this
	 */
	public function addJoin($type, $table, $alias, $conditions) 
	{
		
		$this->registerAlias($table, $alias);
		
		$this->addPart('join', array(
			'type' => $type,
			'table' => $table,
			'alias' => $alias,
			'conditions' => $conditions,
		));

		return $this;
	}

	/**
	 * addPart
	 *
	 * Adds on to a part of the query.
	 *
	 * @see self::$parts
	 * @param string $part
	 * @param mixed $value
	 */
	private function addPart($part, $value) 
	{
		if (is_array($this->parts[$part])) {
			$this->parts[$part][] = $value;
		} else {
			$this->parts[$part] = $value;
		}

		return $this;
	}

	/**
	 * setPart
	 *
	 * Set a part of the query
	 * @see self::$parts
	 *
	 * @param string $part
	 * @param mixed $value
	 */
	private function setPart($part, $value) 
	{
		$this->parts[$part] = $value;
		return $this;
	}

	/**
	 * getPart
	 *
	 * Gets a part of the query
	 *
	 * @see self::$parts
	 * @param string $part
	 */
	private function getPart($part) 
	{
		return isset($this->parts[$part]) ? $this->parts[$part] : null;
	}

	/**
	 * hasPart
	 *
	 * Check if a part is defined
	 *
	 * @see self::$parts
	 * @param string $part
	 * @return bool
	 */
	private function hasPart($part)
	{
		return isset($this->parts[$part]) && count($this->parts[$part]);
	}

	/**
	 * registerAlias
	 * 
	 * Registeres an alias for valiadation and reference
	 * 
	 * @param string $table
	 * @param string $alias
	 * 
	 * @return void
	 */
	private function registerAlias($table, $alias) 
	{
		if (is_null($alias)) {
			return;
		}

		if (isset($this->aliases[$alias])) {
			throw new \Exception(sprintf('Alias %s already defined for %s', 
				$alias, $this->aliases[$alias]
			));
		}

		$this->aliases[$alias] = $table;
	}

	/**
	 * isAliasDefined
	 * 
	 * Check if an alias has already been defined
	 * 
	 * @param string $alias
	 * @return bool
	 */
	public function isAliasDefined($alias)
	{
		return isset($this->aliases[$alias]);
	}

	/**
	 * getAlias
	 * 
	 * @param string $alias
	 * 
	 * @return array|bool
	 */
	public function getAlias($alias) 
	{
		return $this->isAliasDefined($alias) ?
			$this->aliases[$alias] : false;
	}

	/**
	 * getParentAlias
	 * 
	 * Get the parent alias of this query, i.e. the main table we are working with
	 * @return string
	 */ 
	public function getParentAlias()
	{
		$part = $this->getPart('from');

		return $part['alias'];
	}

	/**
	 * getParentTable
	 * 
	 * Get the parent table of this query, i.e. the main table we are working with
	 * @return string
	 */ 
	public function getParentTable()
	{
		$part = $this->getPart('from');

		return $part['table'];
	}

	/**
	 * createPlaceholderName
	 * 
	 * Creates a placeholder text for a field
	 */ 
	public function createPlaceholderName($field) 
	{
		return str_replace(array('%','?',':','.'), '_', $field);
	}

	public function setExplain($bool)
	{
		$this->isExplain = (bool) $bool;
		return $this;
	}

	public function isExplain()
	{
		return $this->isExplain;
	}
}

?>

QueryBuilderExpression.php

<?php

class QueryBuilderExpression 
{
	const EXPR_AND 		= 1;
	const EXPR_OR 		= 2;
	const EXPR_HAVING 	= 3;
	const EXPR_LIKE 	= 4;
	const EXPR_IN 		= 5;
	const EXPR_BETWEEN 	= 6;
	const EXPR_NOT_IN	= 7;

	const GROUP_LEFT  = ' (';
	const GROUP_RIGHT = ') ';

	/** @var array */
	public $words = array(
		self::EXPR_AND 		=> ' AND ',
		self::EXPR_OR 		=> ' OR ',
		self::EXPR_HAVING 	=> ' HAVING ',
		self::EXPR_LIKE 	=> ' LIKE ',
		self::EXPR_IN 		=> ' IN ',
		self::EXPR_BETWEEN 	=> ' BETWEEN ',
		self::EXPR_NOT_IN 	=> ' NOT IN ',
	);

	/** @var array */
	protected $expression = array(
		'base' 	=> null, // this can be a string or a QueryBuilderExpression
		'and' 	=> array(),
		'or' 	=> array(),
	);

	/**
	 * @param null $expression
	 */
	public function __construct(QueryBuilder $qb, $expression = null) 
	{
		$this->qb = $qb;
		$this->expression['base'] = $expression;
	}

	/**
	 * @return string
	 * @throws Exception
	 */
	public function __toString() {
		return $this->build();
	}

	/**
	 * addOr
	 *
	 * Add and OR caluse as a string, QueryBuilderExpression, or array of either.
	 *
	 * @param string|array[string|QueryBuilderExpression] $expr
	 * @return $this
	 */
	public function orX($expression) 
	{
		$args = func_get_args();
		foreach ($args as $expr) {
			$this->expression['or'][] = $expr;
		}
		return $this;
	}

	/**
	 * addAnd
	 *
	 * Add and AND caluse as a string, QueryBuilderExpression, or array of either.
	 *
	 * @param string|array[string|QueryBuilderExpression] $expr
	 * @return $this
	 */
	public function andX($expression) 
	{
		$args = func_get_args();
		foreach ($args as $expr) {
			$this->expression['and'][] = $expr;
		}
		return $this;
	}

	/**
	 * in
	 * 
	 *  Alias of andIn
	 *  @param string $field
	 *  @param array $values
	 *  @param $type
	 * 
	 *  @return $this;
	 */
	public function in($field, array $values, $type = QueryBuilder::PARAMETER_INT)
	{
		return $this->andIn($field, $values, $type);
	}

	/**
	 * andIn
	 * 
	 *  Checks if a field is contained in a set of values
	 * 
	 *  @param string $field
	 *  @param array $values
	 *  @param $type
	 * 
	 *  @return $this;
	 */
	public function andIn($field, array $values, $type = QueryBuilder::PARAMETER_INT) 
	{
		$values = array_values($values); // realign keys if needed
		$placeholders = array();

		foreach ($values as $k => $v) {	
			$placeholder = $this->qb->createPlaceholderName(sprintf('%s_in_%d', $field, $k));		
			$placeholders[] = $placeholder;
			$this->qb->setParameter($placeholder, $v);
		}

		$this->expression['and'][] = sprintf('%s %s (:%s)', $field, $this->words[static::EXPR_IN], implode(',:', $placeholders));

		return $this;
	}

	/**
	 * orIn
	 * 
	 *  Checks if a field is contained in a set of values
	 * 
	 *  @param string $field
	 *  @param array $values
	 *  @param $type
	 * 
	 *  @return $this
	 */
	public function orIn($field, array $values, $type = QueryBuilder::PARAMETER_INT) 
	{
		$values = array_values($values); // realign keys if needed
		$placeholders = array();

		foreach ($values as $k => $v) {	
			$placeholder = $this->qb->createPlaceholderName(sprintf('%s_in_%d', $field, $k));		
			$placeholders[] = $placeholder;
			$this->qb->setParameter($placeholder, $v);
		}

		$this->expression['or'][] = sprintf('%s %s (:%s)', $field, $this->words[static::EXPR_IN], implode(',:', $placeholders));

		return $this;
	}

	/**
	 * notIn
	 * 
	 *  Alias of andNotIn
	 * 
	 *  @param string $field
	 *  @param array $values
	 *  @param $type
	 * 
	 *  @return $this;
	 */
	public function notIn($field, array $values, $type = QueryBuilder::PARAMETER_INT)
	{
		return $this->andNotIn($field, $values, $type);
	}

	/**
	 * andNotIn
	 * 
	 *  Excludes where a field is in a given set of values
	 * 
	 *  @param string $field
	 *  @param array $values
	 *  @param $type
	 * 
	 *  @return $this;
	 */
	public function andNotIn($field, array $values, $type = QueryBuilder::PARAMETER_INT) 
	{
		$values = array_values($values); // realign keys if needed
		$placeholders = array();

		foreach ($values as $k => $v) {	
			$placeholder = $this->qb->createPlaceholderName(sprintf('%s_not_in_%d', $field, $k));		
			$placeholders[] = $placeholder;
			$this->qb->setParameter($placeholder, $v);
		}

		$this->expression['and'][] = sprintf('%s %s (:%s)', $field, $this->words[static::EXPR_NOT_IN], implode(',:', $placeholders));

		return $this;
	}

	/**
	 * orNotIn
	 * 
	 *  Excludes where a field is in a given set of values
	 * 
	 *  @param string $field
	 *  @param array $values
	 *  @param $type
	 * 
	 *  @return $this
	 */
	public function orNotIn($field, array $values, $type = QueryBuilder::PARAMETER_INT) 
	{
		$values = array_values($values); // realign keys if needed
		$placeholders = array();

		foreach ($values as $k => $v) {	
			$placeholder = $this->qb->createPlaceholderName(sprintf('%s_not_in_%d', $field, $k));		
			$placeholders[] = $placeholder;
			$this->qb->setParameter($placeholder, $v);
		}

		$this->expression['or'][] = sprintf('%s %s (:%s)', $field, $this->words[static::EXPR_NOT_IN], implode(',:', $placeholders));

		return $this;
	}


	/**
	 * between
	 * 
	 * @param string $field
	 * @param string|DateTime $from
	 * @param string|DateTime $to
	 * 
	 * @return $this
	 */
	public function between($field, $from, $to)
	{
		return $this->andBetween($field, $from, $to);
	}

	/**
	 * andBetween
	 * 
	 * @param string $field
	 * @param string|DateTime $from
	 * @param string|DateTime $to
	 * 
	 * @return $this
	 */
	public function andBetween($field, $from, $to)
	{
		$fromParameter = $this->qb->createPlaceholderName($field.'_from');
		$toParameter   = $this->qb->createPlaceholderName($field.'_to');

		if ($from instanceof \DateTime) {
			$from = $from->format('Y-m-d H:i:s');
		}
		
		if ($to instanceof \DateTime) {
			$to = $to->format('Y-m-d H:i:s');
		}

		$this->expression['and'][] = sprintf('%s %s :%s %s :%s', 
			$field, 
			$this->words[static::EXPR_BETWEEN], 
			$fromParameter,
			$this->words[static::EXPR_AND], 
			$toParameter
		);

		$this->qb->setParameter($fromParameter, $from);
		$this->qb->setParameter($toParameter, $to);

		return $this;
	}

	/**
	 * orBetween
	 * 
	 * @param string $field
	 * @param string|DateTime $from
	 * @param string|DateTime $to
	 * 
	 * @return $this
	 */
	public function orBetween($field, $from, $to)
	{
		$fromParameter = $this->qb->createPlaceholderName($field.'_from');
		$toParameter   = $this->qb->createPlaceholderName($field.'_to');

		if ($from instanceof \DateTime) {
			$from = $from->format('Y-m-d H:i:s');
		}
		
		if ($to instanceof \DateTime) {
			$to = $to->format('Y-m-d H:i:s');
		}

		$this->expression['or'][] = sprintf('%s %s :%s %s :%s', 
			$field, 
			$this->words[static::EXPR_BETWEEN], 
			$fromParameter,
			$this->words[static::EXPR_AND], 
			$toParameter
		);

		$this->qb->setParameter($fromParameter, $from);
		$this->qb->setParameter($toParameter, $to);

		return $this;
	}
	/**
	 * build
	 *
	 * @return string
	 */
	public function build() 
	{

		$return = null;
		$isExpression = $this->expression['base'] instanceof QueryBuilderExpression;
		$isNested = count($this->expression['and']) || count($this->expression['or']);

		if (empty($this->expression['base']) && !$isNested) {
			throw new \InvalidArgumentException(sprintf('Expression does not contain a valid expression'));
		}

		if (!$isNested && !$isExpression) {
			return $this->expression['base'];
		}

		if ($isNested) {
			$return .= static::GROUP_LEFT;
		}

		if ($this->expression['base']) {
			if ($this->expression['base'] instanceof QueryBuilderExpression) {
				$return .= $this->expression['base']->build();
			} else if (is_array($this->expression['base'])) {
				throw new \Exception('Expression is array');
			} else {
				if ($isNested) {
					$return .= static::GROUP_LEFT . $this->expression['base'] . static::GROUP_RIGHT;
				} else {
					$return .= $this->expression['base'];
				}
			}
		}

		if ($this->expression['base'] && count($this->expression['and'])) {
			$return .= $this->words[static::EXPR_AND];
		}

		if (count($this->expression['and'])) {
			if ($isNested) {
				$return .= static::GROUP_LEFT;
			}

			$return .= implode($this->words[static::EXPR_AND], $this->expression['and']);

			if ($isNested) {
				$return .= static::GROUP_RIGHT;
			}
		}

		if (count($this->expression['and']) && count($this->expression['or']) == 1) {
			$return .= $this->words[static::EXPR_OR];
		}

		if (count($this->expression['or'])) {
			if ($isNested) {
				$return .= static::GROUP_LEFT;
			}

			$return .= implode($this->words[static::EXPR_OR], $this->expression['or']);

			if ($isNested) {
				$return .= static::GROUP_RIGHT;
			}
		}

		if ($isNested) {
			$return .= static::GROUP_RIGHT;
		}

		return $return;
	}

}

?>