POD查询 数据库BaseModel封装

使用每个表的 Model 集成BaseModel , 声明自己的 $this->table = ''，即可使用。 其他的例如 hidden字段，序列化，错误处理等可自行封装
示例 $modelA->select(['a', 'b'])->join('t2', 'id', 'pid')->where('a', 3)->row()

<?php

class BaseModel {

    private static $handle;
    protected $table;
    private $sql = [];

    /** @return \PDO */
    public static function instance() {
        if (self::$handle === NULL) {
            self::$handle = new PDO('mysql:host=xx;dbname=xx', 'root', 'xxx');
            self::$handle->query("SET NAMES utf8mb4");
            self::$handle->setAttribute(PDO::ATTR_TIMEOUT, 5); // 查询超时
            self::$handle->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); // 设置默认的提取模式
        }
     
        return self::$handle;
    }

    /** 插入数据  */
    public function insert(array $data) : int {
        $sql = 'INSERT INTO ' . $this->table . '(' . implode(', ', array_keys($data)) . ") VALUES('" . implode("', '", array_values($data)) . "')";
        $this->query($sql);
        return self::instance()->lastInsertId();
    }

    /** 批量插入数据  */
    public function batchInsert(array $data) {
        if (empty($data[0])) {
            return false;
        }
        $columns = array_keys($data[0]);
        $sql = 'INSERT INTO ' . $this->table . '(' . implode(',', $columns) . ') VALUES';
        foreach ($data as $item) {
            $sql .= "('" . implode("', '", array_values($item)) . "'), ";
        }

        $a = $this->query(rtrim($sql, ', '))->rowCount();
        $b = self::instance()->lastInsertId();
        return ; // 这里返回什么自己定夺
    }

    public function update(array $data) {
        if (empty($this->sql['where'])) {
            return false;
        }
        $str = '';
        foreach ($data as $k => $v) {
            $str .= '' . $k . '=' . "'{$v}', ";
        }
        $sql = 'UPDATE ' . $this->table . ' SET ' . rtrim($str, ', ') . ' WHERE ' . implode(' AND ', $this->sql['where']);
        return $this->query($sql)->rowCount();
    }

    public function delete() {
        if (empty($this->sql['where'])) {
            return false;
        }
        $sql = 'DELETE FROM ' . $this->table . ' WHERE ' . implode(' AND ', $this->sql['where']);
        return $this->query($sql)->rowCount(); 
    }

    public function select($columns)
    {
        if (is_array($columns)) {
            $columns = implode(',', $columns);
        }
        if (isset($this->sql['select'])) {
            $this->sql['select'] .= ',' . $columns;
        } else {
            $this->sql['select'] = $columns;
        }
        return $this;
    }

    public function from(string $table)
    {
        $this->table = $table;
        return $this;
    }

    public function join(string $table, string $left, string $right)
    {
        return $this->add('join', ' INNER JOIN ' . $table . ' ON ' . $left . ' = ' . $right);
    }

    public function leftJoin(string $table, string $left, string $right)
    {
        return $this->add('join', ' LEFT JOIN ' . $table . ' ON ' . $left . ' = ' . $right);
    }

    public function rightJoin(string $table, string $left, string $right)
    {
        return $this->add('join', ' RIGHT JOIN ' . $table . ' ON ' . $left . ' = ' . $right);
    }

    public function where(string $column, $val, $connector = '=') 
    {
        $this->sql['bind'][] = $val;
        return $this->add('where', $column . ' ' . $connector . ' ?');
    }

    public function whereIn(string $column, array $vals)
    {
        $this->sql['bind'] = array_merge($this->sql['bind'] ?? [], $vals);
        $str = join(',', array_pad([], count($vals), '?'));
        return $this->add('where', $column . ' IN (' . $str . ')');
    }

    public function whereLike(string $column, string $keyword)
    {
        $keyword = str_replace(array('\\', '%', '_'), array('\\\\', '\%', '\_'), $keyword);
        return $this->add('where', $column . ' LIKE ' . $keyword . '%');
    }

    public function groupBy(string $column)
    {
        return $this->add('groupBy', $column);
    }

    public function having(string $column, string $connector, $val)
    {
        $this->sql['bind'][] = $val;
        return $this->add('having', $column . ' ' . $connector . ' ?');
    }

    public function orderByAsc(string $column)
    {
        return $this->add('orderBy', $column . ' ASC');
    }

    public function orderByDesc(string $column)
    {
        return $this->add('orderBy', $column . ' DESC');
    }
 
    public function limit(int $i)
    {
        return $this->add('limit', $i);
    }

    public function offset(int $i)
    {
        return $this->add('offset', $i);
    }
 
    public function row(?array $columns = NULL)
    {
        if ($columns !== NULL) {
            $this->select($columns);
        }
        return $this->limit(1)->query()->fetch() ?: [];
    }

    public function all(?array $columns = NULL)
    {
        if ($columns !== NULL) {
            $this->select($columns);
        }
        return $this->query()->fetchAll();
    }

    public function sum(string $column)
    {
        $row = $this->select('sum(' . $column . ') as total')->query()->fetch();
        return $row['total'] ?? 0;
    }

    public function min(string $column)
    {
        $row = $this->select('min(' . $column . ') as minv')->query()->fetch();
        return $row['minv'] ?? null;
    }

    public function max(string $column)
    {
        $row = $this->select('max(' . $column . ') as maxv')->query()->fetch();
        return $row['maxv'] ?? null;
    }    

    public function value(string $column)
    {
        return $this->select($column)->row()[$column] ?? '';
    }

    public function find(int $id)
    {
        return $this->where('id', $id)->row();
    }

    public function pluck(string $column)
    {
        $result = $this->select($column)->all();
        return array_column($result, $column);
    }

    private function add(string $type, $item)
    {
        if (in_array($type, ['limit', 'offset'], true)) {
            $this->sql[$type] = $item; 
        } else {
            $this->sql[$type][] = $item;
        }
        
        return $this;
    }

    private function getSQL()
    {
        $sql = 'SELECT ';
        $sql .= $this->sql['select'] ?? '*';
        $sql .= ' FROM ' . $this->table;

        if (isset($this->sql['join'])) {
            $sql .= implode(' ', $this->sql['join']);
        }
        
        if (isset($this->sql['where'])) {
            $sql .= ' WHERE ';
            $sql .= implode(' AND ', $this->sql['where']);
        }

        if (isset($this->sql['groupBy'])) {
            $sql .= ' GROUP BY ';
            $sql .= implode(', ', $this->sql['groupBy']);
        }

        if (isset($this->sql['having'])) {
            $sql .= ' HAVING ';
            $sql .= implode(' AND ', $this->sql['having']);
        }

        if (isset($this->sql['orderBy'])) {
            $sql .= ' ORDER BY ';
            $sql .= implode(' , ', $this->sql['orderBy']);
        }

        if (isset($this->sql['limit'])) {
            $sql .= ' LIMIT ' . $this->sql['limit'];
        }

        if (isset($this->sql['offset'])) {
            $sql .= ' OFFSET ' . $this->sql['offset'];
        }

        // echo $sql . PHP_EOL;
        return $sql;
    }

    private function query(?string $sql = NULL)
    {
        if ($sql === NULL) {
            $sql = $this->getSQL();
        }

        $ch = self::instance()->prepare($sql);
        if ($ch === false) {
            echo '语句出错:' . json_encode(self::instance()->errorInfo());
            die;
        }
        $ch->execute($this->sql['bind'] ?? []);
        $this->sql = [];
        return $ch;
    }




}