thinkphp6自定义查询方法

数据表

控制器

执行

list第四个值可以为空、*、id,username

use app\common\model\system\UserModel;

    public function index()
    {
	$where = $this->where(where_delete());

		
	$model = new UserModel();
	$pk = $model->getPk();
	// echo $pk;
	$this->list('list', [where_delete()], $this->order(), '*');
    }

列表

    /**
     * 列表
     * 
     * @param string $type  list列表
     * @param array  $where 条件
     * @param array  $order 排序
     * @param string $field 字段
     *
     * @return array 
     */
    public function list($type = 'list', $where = [], $order = [], $field = '')
    {
        $model = new UserModel();
        $pk = $model->getPk();

        if (empty($field)) {
            $field = $pk . ',username,password,email,sort';
        }
        if (empty($order)) {
            $order = ['sort' => 'desc', $pk => 'asc'];
        }

        if ($type == 'list') {
            $data = $model->field($field)->where($where)->order($order)->select()->toArray();
            echo $model->getLastSQL();
        } else {
			
        }

        return $data;
    }

查询条件

    /**
     * 列表查询条件
     *
     * @param array $other 其它条件，eg：['field', 'exp', 'value'] or [['field', 'exp', 'value']]
     *
     * @return array
     */
    protected function where($other = [])
    {
        $search_field = $this->request->param('search_field/s', '');
        $search_exp   = $this->request->param('search_exp/s', '');
        $search_value = $this->request->param('search_value', '');
        $date_field   = $this->request->param('date_field/s', '');
        $date_value   = $this->request->param('date_value/a', []);

        if (is_array($search_value) && empty($search_value)) {
            $search_value = '';
        }
        if ($search_field && $search_exp && $search_value !== '') {
            $where_exp = where_exps();
            $where_exp = array_column($where_exp, 'exp');
            if (!in_array($search_exp, $where_exp)) {
                exception('查询方式错误：' . $search_exp);
            }

            if (in_array($search_exp, ['like', 'not like', '=', '<>', '>=', '<', '<=']) && is_array($search_value)) {
                exception('查询方式错误：' . $search_exp . '，请选择其它方式');
            }

            if ($search_exp == 'like' || $search_exp == 'not like') {
                $search_value = '%' . $search_value . '%';
            } elseif ($search_exp == 'between' || $search_exp == 'not between') {
                $search_value = str_replace('，', ',', $search_value);
                $search_value = explode(',', $search_value);
                $search_value = [$search_value[0] ?? '', $search_value[1] ?? ''];
            } elseif ($search_exp == 'in' || $search_exp == 'not in') {
                $search_value = str_replace('，', ',', $search_value);
            }

            $where[] = [$search_field, $search_exp, $search_value];
        }

        if ($date_field && $date_value) {
            $start_date = $date_value[0] ?? '';
            $end_date   = $date_value[1] ?? '';
            if ($start_date) {
                $where[] = [$date_field, '>=',  $start_date . ' 00:00:00'];
            }
            if ($end_date) {
                $where[] = [$date_field, '<=',  $end_date . ' 23:59:59'];
            }
        }

        if ($other) {
            foreach ($other as $val) {
                if (is_array($val)) {
                    $where[] = $val;
                } else {
                    $where[] = $other;
                    break;
                }
            }
        }

        return $where ?? [];
    }

排序

	/**
     * 列表排序
     *
     * @param array $default 默认排序
     * 
     * @return array
     */
    protected function order($default = [])
    {
        $order = $default;
        $sort_field = $this->request->param('sort_field/s', '');
        $sort_value = $this->request->param('sort_value/s', '');
        if ($sort_field && $sort_value) {
            $order = [$sort_field => $sort_value];
        }
        return $order;
    }

模型

UserModel.php

<?php


namespace app\common\model\system;

use think\Model;


/**
 * 模型
 */
class UserModel extends Model
{
    // 表名
    protected $name = 'user';
    // 表主键
    protected $pk = 'id';
}

公共方法common.php

/**
 * 查询条件是否删除
 *
 * @param array $where  其它条件
 * @param int   $delete 0未删除，1已删除
 *
 * @return array
 */
function where_delete($where = [], $delete = 0)
{
    $where_other = [];
    $where_delete = ['is_delete', '=', $delete];
    if ($where) {
        foreach ($where as $value) {
            if (is_array($value)) {
                $where_other[] = $value;
            } else {
                $where_other[] = $where;
                break;
            }
        }
        $where_other[] = $where_delete;
        return $where_other;
    }
    return $where_delete;
}