Hobo_Query.php

<?php

class Hobo_Query
{
    /**
     * constant for SELECT queries
     */
    const SELECT = 0;

    /**
     * constant for CREATE queries
     */
    const CREATE = 4;

    /**
     * @var integer $type the query type
     *
     * @see Hobo_Query::* constants
     */
    protected $_type = self::SELECT;

    protected $_sqlParts = array(
        'tmpTable'  => array(),
        'select'  => array(),
        'from'    => array(),
        'join'    => array(),
        'where'   => array(),
        'groupBy' => array(),
        'orderBy' => array(),
        'limit'   => false,
        'offset'  => false);

    protected $_params = array(
        'exec'  => array(),
        'select' => array(),
        'where' => array());

    protected $_execParams = array();

    protected $_joinAliases = array();

    protected $_conn;

    protected $_sql;

    public function __construct(PDO $connection = null)
    {
        $this->_conn = $connection;
    }

    /**
     * create
     * returns a new Hobo_Query object
     *
     * @param PDO $connection     optional connection parameter
     * @return Hobo_Query
     */
    public static function create(PDO $connection = null)
    {
        return new Hobo_Query($connection);
    }

    /**
     * Copies a Hobo_Query object.
     *
     * @return Hobo_Query  Copy of the Hobo_Query instance.
     */
    public function copy(Hobo_Query $query = null)
    {
        if ( ! $query) {
            $query = $this;
        }

        $new = clone $query;

        return $new;
    }

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

    public function setConnection(PDO $connection)
    {
        $this->_conn = $connection;
    }

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

    public function getFlattenedParams($params = array())
    {
        return array_merge(
            (array) $params,
            (array) $this->_params['exec'],
            $this->_params['select'],
            $this->_params['where']
        );
    }

    public function getInternalParams($params = array())
    {
        return array_merge($params, $this->_execParams);
    }

    public function execute($params = array())
    {
        $this->_execParams = array();
        $stmt = $this->_execute($params);

        if (is_integer($stmt)) {
            $result = $stmt;
            return $result;
        } else {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
    }

    public function exec($query, array $params = array())
    {
        if ( ! empty($params)) {
            $stmt = $this->_conn->prepare($query);
            $stmt->execute($params);
            return $stmt->rowCount();
        } else {
            $count = $this->_conn->exec($query);
            return $count;
        }
    }

    protected function _execute($params)
    {
        $this->_params['exec'] = $params;
        $this->_execParams = $this->getFlattenedParams();
        $query = $this->getSqlQuery();
        $params = $this->getInternalParams();

        if ($this->_type !== self::SELECT) {
            return $this->exec($query, $params);
        }

        if ( ! empty($params)) {
            $stmt = $this->_conn->prepare($query);
            $stmt->execute($params);
        } else {
            $stmt = $this->_conn->query($query);
        }

        $this->_params['exec'] = array();
        return $stmt;
    }

    public function createTemporaryTable($table)
    {
        $this->_type = self::CREATE;
        return $this->_addSqlQueryPart('tmpTable', $table);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function select($select, $params = array())
    {
        $this->_params['select'] = array();
        if ($this->_type !== self::CREATE) {
            $this->_type = self::SELECT;
        }

        if (is_array($params)) {
            $this->_params['select'] = $params;
        } else {
            $this->_params['select'][] = $params;
        }
        return $this->_addSqlQueryPart('select', $select);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function addSelect($select, $params = array())
    {
        if (is_array($params)) {
            $this->_params['select'] = array_merge($this->_params['select'], $params);
        } else {
            $this->_params['select'][] = $params;
        }
        return $this->_addSqlQueryPart('select', $select, true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function from($from)
    {
        return $this->_addSqlQueryPart('from', $from);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function leftJoin($join, $joinAlias = null)
    {
        if (!is_null($joinAlias)) {
            if (isset($this->_joinAliases[$joinAlias])) {
                throw new Hobo_Query_Exception(
                    "The table alias '$joinAlias' has already been joined to this query."
                );
            }
            $this->_joinAliases[$joinAlias] = true;
        }
        return $this->_addSqlQueryPart('from', 'LEFT JOIN ' . $join, true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function innerJoin($join, $joinAlias = null)
    {
        if (!is_null($joinAlias)) {
            if (isset($this->_joinAliases[$joinAlias])) {
                throw new Hobo_Query_Exception(
                    "The table alias '$joinAlias' has already been joined to this query."
                );
            }
            $this->_joinAliases[$joinAlias] = true;
        }
        return $this->_addSqlQueryPart('from', 'INNER JOIN ' . $join, true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function where($where, $params = array())
    {
        $this->_params['where'] = array();
        if (is_array($params)) {
            $this->_params['where'] = $params;
        } else {
            $this->_params['where'][] = $params;
        }
        return $this->_addSqlQueryPart('where', $where);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function addWhere($where, $params = array())
    {
        return $this->andWhere($where, $params);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function andWhere($where, $params = array())
    {
        if (is_array($params)) {
            $this->_params['where'] = array_merge($this->_params['where'], $params);
        } else {
            $this->_params['where'][] = $params;
        }

        if ($this->_hasSqlQueryPart('where')) {
            $this->_addSqlQueryPart('where', 'AND', true);
        }
        return $this->_addSqlQueryPart('where', $where, true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function orWhere($where, $params = array())
    {
        if (is_array($params)) {
            $this->_params['where'] = array_merge($this->_params['where'], $params);
        } else {
            $this->_params['where'][] = $params;
        }

        if ($this->_hasSqlQueryPart('where')) {
            $this->_addSqlQueryPart('where', 'OR', true);
        }
        return $this->_addSqlQueryPart('where', $where, true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function whereIn($expr, $params = array(), $not = false)
    {
        return $this->andWhereIn($expr, $params, $not);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function andWhereIn($expr, $params = array(), $not = false)
    {
        if (! count($params)) {
            return $this;
        }

        if ($this->_hasSqlQueryPart('where')) {
            $this->_addSqlQueryPart('where', 'AND', true);
        }

        return $this->_addSqlQueryPart('where', $this->_processWhereIn($expr, $params, $not), true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function orWhereIn($expr, $params = array(), $not = false)
    {
        if (! count($params)) {
            return $this;
        }

        if ($this->_hasSqlQueryPart('where')) {
            $this->_addSqlQueryPart('where', 'OR', true);
        }

        return $this->_addSqlQueryPart('where', $this->_processWhereIn($expr, $params, $not), true);
    }

    protected function _processWhereIn($expr, $params = array(), $not = false)
    {
        $params = (array) $params;

        if (! count($params)) {
            return;
        }

        $a = array_fill(0, count($params), '?');
        $this->_params['where'] = array_merge($this->_params['where'], $params);

        return $expr. ($not === true ? ' NOT' : '') . ' IN (' . implode(',', $a) . ')';
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function whereNotIn($expr, $params = array())
    {
        return $this->whereIn($expr, $params, true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function andWhereNotIn($expr, $params = array())
    {
        return $this->andWhereIn($expr, $params, true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function orWhereNotIn($expr, $params = array())
    {
        return $this->orWhereIn($expr, $param, true);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function groupBy($groupBy)
    {
        return $this->_addSqlQueryPart('groupBy', $groupBy);
    }

    /**
     * @param string $select
     * @return Hobo_Query
     */
    public function addGroupBy($groupBy)
    {
        return $this->_addSqlQueryPart('groupBy', $groupBy, true);
    }

    public function orderBy($orderBy)
    {
        return $this->_addSqlQueryPart('orderBy', $orderBy);
    }

    public function addOrderBy($orderBy)
    {
        return $this->_addSqlQueryPart('orderBy', $orderBy, true);
    }

    public function limit($limit)
    {
        return $this->_addSqlQueryPart('limit', $limit);
    }

    public function offset($offset)
    {
        return $this->_addSqlQueryPart('offset', $offset);
    }

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

    protected function _hasSqlQueryPart($partName)
    {
        return count($this->_sqlParts[$partName]) > 0;
    }

    protected function _addSqlQueryPart($name, $part, $append = false)
    {
        if ($name !== 'limit' && $name !== 'offset') {
            if (is_array($part)) {
                if ($append) {
                    $this->_sqlParts[$name] = array_merge($this->_sqlParts[$name], $part);
                } else {
                    $this->_sqlParts[$name] = $part;
                }
            } else {
                if ($append) {
                    $this->_sqlParts[$name][] = $part;
                } else {
                    $this->_sqlParts[$name] = array($part);
                }
            }
        } else {
            $this->_sqlParts[$name] = $part;
        }
        return $this;
    }

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

    public function getSqlQuery()
    {
        $q = '';
        $q .= (! empty($this->_sqlParts['tmpTable'])) ? 'CREATE TEMPORARY TABLE ' . implode(' ', $this->_sqlParts['tmpTable']) : '';
        $q .= (! empty($this->_sqlParts['select'])) ? ' SELECT ' . implode(', ', array_unique($this->_sqlParts['select'])) : '';
        $q .= (! empty($this->_sqlParts['from'])) ? ' FROM ' . implode(' ', $this->_sqlParts['from']) : '';
        $q .= (! empty($this->_sqlParts['where'])) ? ' WHERE ' . implode(' ', $this->_sqlParts['where']) : '';
        $q .= (! empty($this->_sqlParts['groupBy'])) ? ' GROUP BY ' . implode(', ', $this->_sqlParts['groupBy']) : '';
        $q .= (! empty($this->_sqlParts['orderBy'])) ? ' ORDER BY ' . implode(', ', $this->_sqlParts['orderBy']) : '';
        $q .= (! empty($this->_sqlParts['limit'])) ? ' LIMIT ' . $this->_sqlParts['limit'] : '';
        $q .= (! empty($this->_sqlParts['offset'])) ? ' OFFSET ' . $this->_sqlParts['offset'] : '';
        $this->_sql = $q;
        return $q;
    }

    public function checkJoinAlias($joinAlias)
    {
        return isset($this->_joinAliases[$joinAlias]);
    }

    public function isJoined($joinAlias)
    {
        return $this->checkJoinAlias($joinAlias);
    }

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