baoshi-was/app/Services/common/QueryService.php

<?php 

namespace App\Services\common;


use Carbon\Carbon;
use Illuminate\Http\Request;

Class QueryService
{

    /**
     * parameter - query(sql) - special column description
     *
     * @param Request $request
     * @param object $query
     * @param array $columnQueryRules
     * @param string $tableName
     * @return object
     */
    public function query(Request $request,$query,array $columnQueryRules,$tableName = null)
    {
        foreach ($request->input() as $param => $value){
            if ($param === 'paginate' || $param === 'page')continue;
            if (!$value || isset($columnQueryRules[$param]))continue;
            $query = $query->where($tableName.$param,$value);
        }
        //rules: alias timeLimit startDate endDate like
        foreach ($columnQueryRules as $param => $rules){
            if (!$request->input($param))continue;
            $isExecute = true;
            $column = $param;
            foreach ($rules as $rule => $value){
                if ($rule === 'alias'){
                    $column = $value;
                    $isExecute = false;
                }
                if ($rule === 'timeLimit'){
                    $today=Carbon::now()->subDays($value);
                    $queryTemp=clone $query;
                    $queryTemp=$queryTemp->where($tableName.$column,'like','%'.$request->input($param).'%')
                        ->where($tableName.'created_at','>',$today->format('Y-m-d'));
                    if($queryTemp->count()==0 || $queryTemp->first()[$column]==$request->input($param)){
                        $query=$query->where($tableName.$column,$request->input($param));
                    }else{
                        $query=$query->where($tableName.$column,'like','%'.$request->input($param).'%')
                            ->where($tableName.'created_at','>',$today->format('Y-m-d'));
                    }
                    $isExecute = true;
                }
                if ($rule === 'startDate'){
                    $startDate = $request->input($param).$value;
                    $query = $query->where($tableName.$column,'>=',$startDate);
                    $isExecute = true;
                }
                if ($rule === 'endDate'){
                    $endDate = $request->input($param).$value;
                    $query = $query->where($tableName.$column,'<=',$endDate);
                    $isExecute = true;
                }
                if ($rule === 'like'){
                    $query = $query->where($tableName.$column,'like','%'.$request->input($param).'%');
                    $isExecute = true;
                }
            }
            if (!$isExecute) $query = $query->where($tableName.$column,$request->input($param));
        }
        return $query;
    }

}