Skip to content

Instantly share code, notes, and snippets.

@ghassani

ghassani/QueryBuilder.php

Created September 19, 2018 18:28
Show Gist options
  • Save ghassani/ca8b485879c609ea519f6a1eeaf6c4de to your computer and use it in GitHub Desktop.
Save ghassani/ca8b485879c609ea519f6a1eeaf6c4de to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ghassani created this gist Sep 19, 2018.
    995 changes: 995 additions & 0 deletions QueryBuilder.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,995 @@
    <?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;
    }
    }

    ?>
    389 changes: 389 additions & 0 deletions QueryBuilderExpression.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,389 @@
    <?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;
    }

    }

    ?>