您的位置 首页 php

Laravel-admin Grid自定义数据源

使用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绑定来实现。

文章来源:智云一二三科技

文章标题:Laravel-admin Grid自定义数据源

文章地址:https://www.zhihuclub.com/79701.shtml

关于作者: 智云科技

热门文章

网站地图