使用laravel-admin grid 大多时候我们单表model或者多表简单联接(参考 )就可以满足我们的需求了。一些特殊需求时不得不使用比较复杂的查询结果作为grid的数据源,接下来看看如何使用。
首先同样要先创建一个model:
$> php artisan make:model App\Models\User
然后编辑model文件:
通过覆盖模型的paginate和with方法获取数据。
<?php
namespace App\Admin\Models;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Request;
use Illuminate\Pagination\LengthAwarePaginator;
use Encore\Admin\Grid\Column;
/**
* Class User
*
* @property mixed id
* @property mixed name
* @property mixed nick
* @property mixed profile
* @property mixed created_at
*
* @method Column id(string $fieldName)
* @method Column name(string $fieldName)
* @method Column nick(string $fieldName)
* @method Column profile(string $fieldName)
* @method Column created_at(string $fieldName)
*
* @package App\Admin\Models\AbstractModel
*/class User extends AbstractModel
{
protected $table = "user";
protected $primaryKey = "id";
public $incrementing = false;
/**
* 加载数据入口方法
*/ public function paginate()
{
// 分页设置:每页记录数、当前页数
$perPage = Request::get('per_page', 100);
if (empty(intval($perPage))) {
$perPage = 100;
}
$page = intval(Request::get('page', 1));
$start = ($page-1) * intval($perPage);
// 查询条件设置:默认查询条件以及filter中的查询条件
$dates = Request::get('created_at',[]);
$start_date = $dates['start'] ?? '';
$end_date = $dates['end'] ?? '';
// 运行sql获取数据数组
$sqls = self::getAdsReportsDaily(
Request::get('name',''),
Request::get('nick',''),
$start_date,
$end_date
);
// 分页数据sql
$sql = $sqls['data'] . " LIMIT {$start}, {$perPage}";
// 查询总记录数
$count = DB::select($sqls['count']);
$total = 0;
if (count($count) > 0) {
$total = $count[0]->cnt;
}
$result = DB::select($sql);
$users = static::hydrate($result);
$paginator = new LengthAwarePaginator($users, $total, $perPage);
$paginator->setPath(url()->current());
return $paginator;
}
public static function with($relations)
{
return new static;
}
/**
* 覆盖filter中等值查询方法
* @params $column
* @params $operator
* @params $value
* @params $boolean
* @return mixed
*/ public function where($column,
$operator = null,
$value = null,
$boolean = 'and')
{
return new static;
}
/**
* 覆盖 filter中 范围查询方法
* @params $column
* @params $values
* @params $boolean
* @params $not
*/ public function whereBetween($column,
array $values,
$boolean = 'and',
$not = false)
{
return new static;
}
/**
* 组织数据查询使用的sql
* @params $name //name查询条件
* @params $nick//nick查询条件
* @params $start_date //时间范围
* @params $end_date
*/
private static function getAdsReportsDaily($name="",
$nick="",
$start_date="",
$end_date="")
{
$wheres = [];
if (!empty($name)) {
$wheres[] = "name like '%{$name}%'";
}
if (!empty($nick)) {
$wheres[] = "nick like '%{$nick}%'";
}
if (!empty($start_date)) {
$wheres[] = "created_at >= '{$start_date}'";
}
if (!empty($end_date)) {
$wheres[] = "created_at <= '{$end_date}'";
}
$where = "WHERE 1";
if (count($wheres) > 0) {
$where .= " and ".implode(" and ", $wheres);
}
$sqlTotal = "SELECT name, nick, profile, created_at FROM user {$where}";
$sqlCount = "SELECT count(1) as cnt FROM ({$sqlTotal}) AS t";
//return DB::select($sql);
return [
'data' => $sqlTotal,
'count' => $sqlCount
];
}
}
以上就是自定义查询作为grid的数据源,如果需要查询单项数据可以通过覆盖model的 findOrFail 方法实现
public function findOrFail($id) {}
如果要在form表单中保存数据通过覆盖model的 save 方法实现
public function save(array $options = []) {}
定义好model后,grid中使用与其他model相同:
$grid = new Grid(new User);
注:除本文方法外,还可以将复杂的查询创建为mysql的view 然后将model与改view绑定来实现。