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 array $params
     * @param object $query
     * @param array $columnQueryRules
     * @param string $tableName
     * @return object
     */
    public function query(array $params,$query,array $columnQueryRules,$tableName = null)
    {
        if ($tableName) $tableName .= ".";
        foreach ($params as $param => $value){
            if ($param === 'paginate' || $param === 'page')continue;
            if (!$value || $columnQueryRules[$param] ?? false)continue;
            $query = $query->where($tableName.$param,$value);
        }
        //rules: alias timeLimit startDate endDate like multi
        foreach ($columnQueryRules as $param => $rules){
            if (!($params[$param] ?? false) || !$params[$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','%'.$params[$param].'%')
                        ->where($tableName.'created_at','>',$today);
                    if($queryTemp->count()==0 || $queryTemp->first()[$column]==$params[$param]){
                        $query=$query->where($tableName.$column,$params[$param]);
                    }else{
                        $query=$query->where($tableName.$column,'like','%'.$params[$param].'%')
                            ->where($tableName.'created_at','>',$today);
                    }
                    $isExecute = true;
                }
                if ($rule === 'startDate'){
                    $startDate = $params[$param].$value;
                    $query = $query->where($tableName.$column,'>=',$startDate);
                    $isExecute = true;
                }
                if ($rule === 'endDate'){
                    $endDate = $params[$param].$value;
                    $query = $query->where($tableName.$column,'<=',$endDate);
                    $isExecute = true;
                }
                if ($rule === 'like'){
                    $query = $query->where($tableName.$column,'like',$value.$params[$param].$value);
                    $isExecute = true;
                }
                if ($rule === 'multi'){
                    $query = $query->whereIn($tableName.$column,explode($value,$params[$param]));
                    $isExecute = true;
                }
            }
            if (!$isExecute) $query = $query->where($tableName.$column,$params[$param]);
        }
        return $query;
    }

}