majestic/Model/SqlModel.php

<?php namespace Majestic\Model;

/**
 * Класс модели данных
 *
 * @copyright NetMonsters <team@netmonsters.ru>
 * @link http://netmonsters.ru
 * @package Majestic
 * @subpackage Model
 * @since 2011-11-11
 */

/**
 * @property SqlDbDriver $db
 */
abstract class SqlModel extends Model
{
    /**
     * @param string $ident
     * @return string Quoted identifier.
     */
    public function identify($ident)
    {
        return $this->db->quoteIdentifier($ident);
    }

    /**
     * @param mixed $value
     * @return string Quoted value.
     */
    public function quote($value)
    {
        return $this->db->quote($value);
    }

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

    /**
     * @param int $id
     * @return object
     */
    public function get($id)
    {
        $sql = 'SELECT * FROM :table WHERE :pk=?';
        return $this->fetch($sql, $id);
    }

    /**
     * @param array $data
     * @param mixed $where
     * @return int Number of affected rows
     */
    public function update($data, $where)
    {
        if (is_int($where) || $where === (string)(int)$where) {
            $where = $this->identify($this->key) . '=' . (int)$where;
        }
        return parent::update($data, $where);
    }

    /**
     * @param int $id Int id
     * @return int Number of affected rows
     */
    public function delete($id)
    {
        $where = $this->identify($this->key) . '=' . (int)$id;
        return $this->db->delete($this->table(), $where);
    }

    /**
     * Creates order sql string
     *
     * @param array $params
     * @param array $sortable
     * @return string
     */
    protected function order($params, $sortable = array('id'))
    {
        $sql = '';
        if (isset($params['sort'])) {
            if (!is_array($params['sort'])) {
                if (isset($params['order'])) {
                    $params['order'] = array($params['sort'] => $params['order']);
                }
                $params['sort'] = array($params['sort']);
            }
            $order_list = array();
            for ($i = 0; $i < count($params['sort']); $i++) {
                $order = (isset($params['order'][$params['sort'][$i]]) && $params['order'][$params['sort'][$i]] == 'desc') ? 'DESC' : 'ASC';
                if (in_array($params['sort'][$i], $sortable)) {
                    $order_list[] = $this->identify($params['sort'][$i]) . ' ' . $order;
                }
            }
            if ($order_list) {
                $sql = ' ORDER BY ' . implode(',', $order_list);
            }
        }
        return $sql;
    }

    /**
     * Searches using like
     *
     * @param array $params
     * @param array $searchable
     * @param string $table_prefix
     * @return string
     */
    protected function search($params, $searchable = array('id'), $table_prefix = '')
    {
        $sql = '';
        if (isset($params['q']) && isset($params['qt']) && in_array($params['qt'], $searchable)) {
            if ($table_prefix) {
                $sql = $table_prefix . '.';
            }
            $sql .= $this->identify($params['qt']) . ' LIKE ' . $this->quote('%' . $params['q'] . '%');
        }
        return $sql;
    }

    /**
     * This method appends to params table and primary key.
     * So they can be accessed through `:table` and `:pk` placeholders.
     *
     * @param string $sql
     * @param array $params
     * @return DbStatement
     */
    protected function query($sql, $params = array())
    {
        if (!is_array($params)) {
            $params = array($params);
        }
        $params = array(
            'table' => new DbExpr($this->identify($this->table())),
            'pk' => new DbExpr($this->identify($this->key)),
        ) + $params;
        return $this->db->query($sql, $params);
    }

    /**
     * @param string $data Request
     * @param array $params Request parameters
     * @param string $field Requested field name
     * @param CacheKey $cache_key Key for caching in
     * @return mixed
     */
    protected function fetchField($data, $params = array(), $field, $cache_key = null)
    {
        if (!$cache_key || !$result = $cache_key->get()) {
            $result = $this->query($data, $params)->fetchField($field);
            if ($cache_key) {
                $cache_key->set($result);
            }
        }
        return $result;
    }

    /**
     * @param string $data Request
     * @param array $params Request parameters
     * @param CacheKey $cache_key Key for caching in
     * @return mixed
     */
    protected function fetch($data, $params = array(), $cache_key = null)
    {
        if (!$cache_key || !$result = $cache_key->get()) {
            $result = $this->query($data, $params)->fetch();
            if ($cache_key) {
                $cache_key->set($result);
            }
        }
        return $result;
    }

    /**
     * @param string $data
     * @param array $params
     * @param CacheKey $cache_key
     * @return array
     */
    protected function fetchAll($data, $params = array(), $cache_key = null)
    {
        if (!$cache_key || !$result = $cache_key->get()) {
            $result = $this->query($data, $params)->fetchAll();
            if ($cache_key) {
                $cache_key->set($result);
            }
        }
        return $result;
    }

    /**
     * @param $select array
     * @param $distinct string|bool
     * @param $where array @ex array('field=?' => $value, 'field=1')
     * @param $order array @ex array('sort' => array('field1', 'field2'), 'order' => array('field2' => 'desc'))
     * @param $limit string @ex '30' or '30,30'
     * @param $heaving TODO
     * @param $group_by TODO
     * @param $sql_expression null|string
     * @param $sql_expression_params array
     * @param $cache_key CacheKey|null
     * @return SqlResultProvider
     */
    public function find($select, $distinct, $where, $order, $limit, $heaving = null, $group_by = null, $sql_expression = null, $sql_expression_params = array(), $cache_key = null)
    {
        $select = $this->db->selectExpr($select, $distinct);
        $where = $this->db->whereExpr($where);
        $group_by = $this->db->groupByExpr($group_by);
        $order = isset($order['sort']) ? $this->order($order, $order['sort']) : false;
        $limit = $this->db->limitExpr($limit);
        $result = $this->query(
            (($sql_expression) ? $sql_expression : ('SELECT ' . $select . ' FROM ' . $this->identify($this->table())))
                . (($where) ? (' WHERE ' . $where) : '')
                . (($group_by) ? (' GROUP BY ' . $group_by) : '')
                . (($order) ? ($order) : '')
                . (($limit) ? (' LIMIT ' . $limit) : ''),
            $sql_expression_params,
            $cache_key
        );
        return new SqlResultProvider($result);
    }

    public function count($select_parent_query = 'x.count', $where, $heaving = null, $group_by = null, $sql_expression = null, $sql_expression_params = array(), $cache_key = null)
    {
        $where = $this->db->whereExpr($where);
        $group_by = $this->db->groupByExpr($group_by);
        $result = $this->query(
            'SELECT (' .
            (($sql_expression) ? $sql_expression : ('SELECT * FROM ' . $this->identify($this->table())))
                . (($where) ? (' WHERE ' . $where) : '')
                . (($group_by) ? (' GROUP BY ' . $group_by) : '') . ' LIMIT 1) AS count',
            $sql_expression_params,
            $cache_key
        );
        return new SqlResultProvider($result);
    }

    /**
     * @param $sql_expression null
     * @param $sql_expression_params array
     * @return SqlCriteria
     */
    public function criteria($sql_expression = null, $sql_expression_params = array())
    {
        return new SqlCriteria($this, $sql_expression, $sql_expression_params);
    }
}