使用原生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;
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
