Laravel ORM

@航空母舰

已于 2022-10-17 10:26:03 修改

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： phpMVC 文章标签： php 数据库

于 2019-06-19 13:05:31 首次发布

本文链接：https://blog.youkuaiyun.com/hudeyong926/article/details/99540746

phpMVC 专栏收录该内容

43 篇文章

订阅专栏

使用原生sql

$list = DB::select($sql);
$list = array_map(function ($value) {
    return (array) $value;
}, $list);
$bool = DB::insert("insert into user(name,sex,age) values(?,?,?,?)", [ '小明', '女', 670 ]);
$bool = DB::update('update user set age= ? where ID= ? ', [ 20, 5 ]);
$num = DB::delete('delete from user where ID= ?', [ 5 ]);

$users = DB::table('users')->leftJoin('posts', 'users.id', '=', 'posts.user_id')->get();

$users = DB::table('users')->count();
$total = DB::table('users')->sum('votes');

Laravel多 where并搜索。闭包写法与Eloquent 风格一致，可读性较好

$where = function ($query) use ($request) {
    if ($request->has('nickname') && $request->nickname) {
        $search = "%{$request->nickname}%";
        $query->where('nickname', 'like', $search);
    }
};
$list = Customer::where($where)->get();

where条件也可以写到Model中然后通过级联调用。当你在 Eloquent 模型实例中获取或设置某些属性值的时候，访问器和修改器允许你对 Eloquent 属性值进行格式化.怎么获得属性没有被修改的值$user->getOriginal('first_name');

group自定义字段year

$list = self::selectRaw("sum(money) money,left(month,4) year,group_code")->groupBy("year")->groupBy('group_code')->get();

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    protected $hidden = ['password']; //隐藏属性

    public function modules()
    {
        return $this->hasMany('App\Module');
    }

    /**
     * $users = App\User::active()->get();
     * Scope a query to only include active users.
     *
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeActive($query)
    {
        return $query->where('active', 1);
    }

    /**
     * $users = App\User::ofType('admin')->get();
     * Scope a query to only include users of a given type.
     *
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @param  mixed                                 $type
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeOfType($query, $type)
    {
        if (!is_null($agentId)) {
            return $query->where('type', $type);
        }
    }

    /**
     * 获取之前首字母大写
     *
     * @param  $value
     * @return string
     */
    public function getFirstNameAttribute($value)
    {
        return ucfirst($value);
    }

    /**
     * 存入之前的改变
     *
     * @param $value
     */
    public function setFirstNameAttribute($value)
    {
        $this->attributes['first_name'] = strtolower($value);
    }

    // 处理没登陆天数
    public function getHadNotLoginDaysAttribute()
    {
        $login = $this->attributes['last_login_time'];  // $this->attributes表的字段属性
        $create = $this->attributes['created_at'];
        if ($login > 0) {
            $time = time() - $login;   // 有最后登陆时间
        } else {
            $time = time() - $create;  // 没，创建时间
        }

        return intval(floor($time / 86400)).'天';
    }
}

whereHas就是在关联关系（user > hasMany > posts）上筛选，只筛选符合条件的

$users = User::whereHas('posts', function ($q) {
    $q->where('created_at', '>=', '2017-11-29');
})->get();
//只返回用户的post记录在2017年11月29之后的数据

能不连表就不要连,很多查询都可以用户whereHas来处理就可以满足

BaseModel

<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Schema;
use DB;

class BaseModel extends Model
{
    public static $hasOrderBy = false;

    /**
     * 新增
     *
     * @param array $params
     * @return mixed
     */
    public static function store(array $params = [])
    {
        $params = self::filterFields($params);

        return self::create($params);
    }

    /**
     * 只获取某一列的数据
     *
     * @param       $field
     * @param array $where
     * @return mixed
     */
    public static function fetchColumn($field, array $where = [])
    {
        return self::select($field)->whereMap($where)->value($field);
    }

    /**
     * 从数据表中获取一行数据
     *
     * @param array $where
     * @return mixed
     */
    public static function fetchRow(array $where = [])
    {
        return self::whereMap($where)->first();
    }

    /**
     * 根据主键详情
     *
     * @param mixed $id 1 or [1,2]
     * @return mixed
     */
    public static function fetchRowByPk($id)
    {
        return self::find($id);
    }

    /**
     * 查询列表数据主键倒序
     *
     * @param array $where    where条件
     * @param int   $page     页码
     * @param int   $pageSize 设为-1不启用分页数据
     * @return mixed
     */
    public static function fetchAll(array $where = [], $page = 1, $pageSize = 12)
    {
        $query = self::whereMap($where);
        if ($pageSize > 0) {
            $offset = ($page - 1) * $pageSize;
            $query = $query->skip($offset)->take($pageSize);
        }
        //对象调用方式,可以调用非静态方法
        if (false === static::$hasOrderBy) {
            $pkName = (new static())->getKeyName();
            $query->orderBy($pkName, 'desc');
        }
        $list = $query->get();

        return $list;
    }

    /**
     * 统计记录数
     *
     * @param array $where
     * @return mixed
     */
    public static function getCount($where)
    {
        return self::whereMap($where)->count();
    }

    /**
     * 修改数据
     *
     * @param array $where
     * @param array $params
     * @return mixed
     */
    public static function modify(array $where = [], array $params = [])
    {
        $params = self::filterFields($params);

        return self::whereMap($where)->update($params);
    }

    /**
     * 字段增加或减少.
     *
     * @param string $column 字段名
     * @param        $where  限制条件
     * @param int    $step   正负数 eg: -5
     */
    public static function modifyColumnIncrement(string $column, $where = [], $step = 1)
    {
        return self::whereMap($where)->increment($column, $step);
    }

    /**
     * 根据主键删除
     *
     * @param $id 1 or [1,2]
     * @return mixed
     */
    public static function removeByPk($id)
    {
        return self::destroy($id);
    }

    /**
     * 删除
     *
     * @param array $where
     * @return mixed
     */
    public static function remove(array $where)
    {
        return self::whereMap($where)->delete();
    }

    /**
     *  批量新增
     *
     * @param array $list
     * @return mixed
     */
    public static function storeBatch(array $list = [])
    {
        return self::insert($list);
    }

    /**
     * 批量更新
     *
     * @param array $multipleData
     * @return bool
     */
    public function updateBatchCaseWhen(array $multipleData = [])
    {
        DB::beginTransaction();
        try {
            $tableName = DB::getTablePrefix().(new static())->getTable(); // 表名
            $firstRow = current($multipleData);

            $updateColumn = array_keys($firstRow);
            // 默认以id为条件更新，如果没有ID则以第一个字段为条件
            $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
            unset($updateColumn[0]);
            // 拼接sql语句
            $updateSql = "UPDATE ".$tableName." SET ";
            $sets = [];
            $bindings = [];
            foreach ($updateColumn as $uColumn) {
                $setSql = "`".$uColumn."` = CASE ";
                foreach ($multipleData as $data) {
                    $setSql .= "WHEN `".$referenceColumn."` = ? THEN ? ";
                    $bindings[] = $data[$referenceColumn];
                    $bindings[] = $data[$uColumn];
                }
                $setSql .= "ELSE `".$uColumn."` END ";
                $sets[] = $setSql;
            }
            $updateSql .= implode(', ', $sets);
            $whereIn = collect($multipleData)->pluck($referenceColumn)->values()->all();
            $bindings = array_merge($bindings, $whereIn);
            $whereIn = rtrim(str_repeat('?,', count($whereIn)), ',');
            $updateSql = rtrim($updateSql, ", ")." WHERE `".$referenceColumn."` IN (".$whereIn.")";

            // 传入预处理sql语句和对应绑定数据
            DB::update($updateSql, $bindings);
            DB::commit();
        } catch (\Exception $e) {
            DB::rollback();

            return false;
        }

        return true;
    }

    /**
     * 使用作用域扩展 Builder 链式操作
     *
     * @param       $query
     * @param array $map
     *
     * @return mixed
     *
     *  示例:
     * $map = [
     *     'id' => 1,
     *     'id' => ['in', [1,2,3]],
     *     'id' => ['or', 22],
     *     ['custom', "right(mobile,11) like '1%'"],
     *     ['desc', 'id'],
     * ]
     */
    public function scopeWhereMap($query, array $map = [])
    {
        // 如果是空直接返回
        if (empty($map)) {
            return $query;
        }

        // 判断各种方法
        foreach ($map as $field => $v) {
            if (is_array($v)) {
                $sign = strtoupper(current($v));
                $last = end($v);
                switch ($sign) {
                    case 'IN':
                        $query->whereIn($field, $last);
                        break;
                    case 'OR':
                        $query->orWhere($field, $last);
                        break;
                    case 'NOTIN':
                        $query->whereNotIn($field, $last);
                        break;
                    case 'BETWEEN':
                        $query->whereBetween($field, $last);
                        break;
                    case 'NOTBETWEEN':
                        $query->whereNotBetween($field, $last);
                        break;
                    case 'NULL':
                        $query->whereNull($field);
                        break;
                    case 'NOTNULL':
                        $query->whereNotNull($field);
                        break;
                    case 'EQ':
                        $query->where($field, '=', $last);
                        break;
                    case 'GT':
                        $query->where($field, '>', $last);
                        break;
                    case 'EGT':
                        $query->where($field, '>=', $last);
                        break;
                    case 'LT':
                        $query->where($field, '<', $last);
                        break;
                    case 'ELT':
                        $query->where($field, '<=', $last);
                        break;
                    case 'NEQ':
                        $query->where($field, '<>', $last);
                        break;
                    case 'LIKE':
                        $query->where($field, $sign, '%'.$last.'%');
                        break;
                    case 'END':
                        $query->where($field, 'like', '%'.$last);
                        break;
                    case 'START':
                        $query->where($field, 'like', $last.'%');
                        break;
                    case 'FIND_IN_SET':
                        $query->whereRaw('FIND_IN_SET(?,'.$field.')', [$last]);
                        break;
                    case 'CUSTOM':  //个性化查询
                        $query->whereRaw($last);
                        break;
                    case 'DESC': //排序
                        $query->orderBy($last, $sign);
                        static::$hasOrderBy = true;
                        break;
                    case 'ASC': //排序
                        $query->orderBy($last, $sign);
                        static::$hasOrderBy = true;
                        break;
                    default:
                        $query->whereIn($field, $v);
                        break;
                }
            } else {
                is_string($v) ? $query->whereRaw("`$field`='{$v}'") : is_null($v) ? $query : $query->where($field, $v);
            }
        }

        return $query;
    }

    /**
     * 获取where条件拼接SQL
     * @param $map
     * @return string
     */
    public function getWhereSql($map): string
    {
        $tmpArr = [];
        $where = '';
        foreach ($map as $field => $v) {
            if (is_array($v)) {
                $field = "`$field`";
                $sign = strtoupper(current($v));
                $last = end($v);
                switch ($sign) {
                    case 'IN':
                        $last = is_array($last) ? str_replace(',', "','", $last) : $last;
                        $tmpArr[] = " AND $field IN('$last')";
                        break;
                    case 'OR':
                        $tmpArr[] = " OR ($field='$last')";
                        break;
                    case 'NOTIN':
                        $last = is_array($last) ? str_replace(',', "','", $last) : $last;
                        $tmpArr[] = " AND $field NOT IN('$last')";
                        break;
                    case 'BETWEEN':
                        $start = $last[0];
                        $end = $last[1];
                        $tmpArr[] = " AND $field BETWEEN '$start' AND '$end'";
                        break;
                    case 'NOTBETWEEN':
                        $start = $last[0];
                        $end = $last[1];
                        $tmpArr[] = " AND $field NOT BETWEEN '$start' AND '$end'";
                        break;
                    case 'NULL':
                        $tmpArr[] = " AND $field IS NULL";
                        break;
                    case 'NOTNULL':
                        $tmpArr[] = " AND $field IS NOT NULL";
                        break;
                    case 'EQ':
                        $tmpArr[] = " AND $field='$last'";
                        break;
                    case 'GT':
                        $tmpArr[] = " AND $field>'$last'";
                        break;
                    case 'EGT':
                        $tmpArr[] = " AND $field>='$last'";
                        break;
                    case 'LT':
                        $tmpArr[] = " AND $field<'$last'";
                        break;
                    case 'ELT':
                        $tmpArr[] = " AND $field<='$last'";
                        break;
                    case 'NEQ':
                        $tmpArr[] = " AND $field<>'$last'";
                        break;
                    case 'LIKE':
                        $tmpArr[] = " AND $field LIKE '%$last%'";
                        break;
                    case 'END':
                        $tmpArr[] = " AND $field LIKE '%$last'";
                        break;
                    case 'START':
                        $tmpArr[] = " AND $field LIKE '$last%'";
                        break;
                    case 'FIND_IN_SET':
                        $tmpArr[] = " AND FIND_IN_SET($last, $field)";
                        break;
                    case 'CUSTOM':
                        $tmpArr[] = " AND $last";
                        break;
                    default:
                        $last = is_array($last) ? str_replace(',', "','", $last) : $last;
                        $tmpArr[] = " AND $field IN('$last')";
                        break;
                }
            } else {
                $tmpArr[] = " AND `$field`='$v'";
            }
        }
        //拼接查询参数
        if ($tmpArr) {
            $where = 'WHERE '.ltrim(implode('', $tmpArr), ' AND ');
        }

        return $where;
    }

    /**
     * 相同表结构的切换表，省去新建model: users_1,users_2
     *
     * @param $query
     * @param $tableName
     */
    public function scopeFromTable($query, $tableName)
    {
        $query->from($tableName);
    }

    /**
     * 过滤数据库没有的字段
     *
     * @param $data
     * @return mixed
     */
    public static function filterFields($data)
    {
        $fields = Schema::getColumnListing((new static())->getTable());
        $tableColumn = array_keys(current($data));
        $columns = array_intersect($tableColumn, $fields);

        return $columns;

        $fields = array_keys($data);

        $list = self::getDbFields();
        foreach ($fields as $key) {
            if (!array_key_exists($key, $list)) {
                unset($data[$key]);
            }
        }

        return $data;
    }

    /**
     * 取得数据表的字段信息.
     *
     * @return array
     */
    public static function getDbFields()
    {
        $table = (new static())->getTable();
        $result = DB::select('SHOW FULL COLUMNS FROM '.$table);

        $columns = [];
        foreach ($result as $val) {
            $val = (array) $val;
            $columns[$val['Field']] = [
                'name' => $val['Field'],
                'type' => $val['Type'],
                'notnull' => (bool) ('' === $val['Null']), // not null is empty, null is yes
                'default' => $val['Default'],
                'comment' => $val['Comment'],
                'primary' => ('pri' == strtolower($val['Key'])),
                'autoinc' => ('auto_increment' == strtolower($val['Extra'])),
            ];
        }
        //$columns = Schema::getColumnListing(new static());
        
        return $columns;
    }

    /**
     * @param string $tablePref
     * @return array
     */
    public static function checkTables($tablePref = '')
    {
        $config = DB::connection()->getConfig();
        $key = 'Tables_in_'.$config['database'];

        $sql = "SHOW TABLES";
        if ($tablePref) {
            $sql .= " LIKE '%$tablePref%'";
            $key .= " (%$tablePref%)";
        }
        $list = DB::select($sql);

        return array_column($list, $key);
    }

    /**
     * 执行原生SQL语句
     * @param $sql
     * @return mixed
     */
    public static function querySql($sql, $whereMap = '')
    {
        $tableName = DB::getTablePrefix() . (new static())->getTable(); // 表名
        $whereSql = self::getWhereSql($whereMap);
        $sql = str_replace(["#table#", "#where#"], [$tableName, $whereSql], $sql);
        $list = DB::select($sql);
        return array_map(function ($value) {
            return (array)$value;
        }, $list);
    }

    /**
     * 获取最后一条SQL的小函数
     *
     * @return mixed
     */
    protected static function lastSql()
    {
        DB::enableQueryLog();
        $sql = DB::getQueryLog();
        $query = end($sql);

        return $query;
    }
}