<?php
namespace App\Utility;
/**
* 拼接 sql 查询条件助手
*/
class SqlHelper
{
private $params;
private $whereParams;
/**
* @param array $params
*/
public function __construct(array $params)
{
$this->params = $params;
$this->whereParams = [];
}
/**
* 初始化对象
* @param $params
* @return SqlHelper
* Created by PhpStorm
* User:jill
* Date:2022/7/14
* Time:1:57 PM
*/
public static function inc($params): SqlHelper
{
return new SqlHelper($params);
}
public function checkParam($field): bool
{
//需要检验字段是否存在的在这里校验
if (isset($this->params[$field]) && $this->params[$field] === 0) {
return true;
}
if (isset($this->params[$field]) && !superEmpty($this->params[$field])) {
return true;
}
return false;
}
/**
* @param string $field
* @param string|callable $condition
* @param bool $isCheck
* @return $this
* Created by PhpStorm
* User:jill
* Date:2022/7/14
* Time:10:06 AM
*/
public function addWhere(
string $field,
$condition,
bool $isCheck = true
): self
{
//不用校验参数的 直接写入where 条件
if (!$isCheck) {
$this->whereParams[$field] = $condition;
return $this;
}
//需要检验字段是否存在的在这里校验
if (isset($this->params[$field]) && !superEmpty($this->params[$field])) {
//支持回调函数
if (is_callable($condition)) {
$this->whereParams[$field] = $condition($this->params[$field]);
return $this;
}
//替换值防止出现 数据不存在而异常
$condition = str_replace("####", "'" . $this->params[$field] . "'", $condition);
$this->whereParams[$field] = $condition;
}
return $this;
}
/**
* @return string
* Created by PhpStorm
* User:jill
* Date:2022/7/14
* Time:10:06 AM
*/
public function getWhere(): string
{
return implode('', $this->whereParams);
}
/**
* @return array
*/
public function getParams(): array
{
return $this->params;
}
/**
* @param array $params
*/
public function setParams(array $params): void
{
$this->params = $params;
}
public function getLimit(): string
{
return empty($this->params['page']) ? "" : " LIMIT {$this->params['page'][0]}, {$this->params['page'][1]}";
}
}
public function getSqlWhere2(array $params): string
{
return SqlHelper::inc($params)
->addWhere("id", " and m.id = #### ")
->addWhere("keyword", " and m.keyword like '%####%'")
->addWhere("platform", function ($val) {
if ($val === "BS") {
return " and m.bs_flg = 1";
}
if ($val === "ASA") {
return " and m.asa_flg = 1";
}
return "";
})->addWhere("businesstype_id", function ($val) {
return "
and (
m.businesstype_range = 'ALL'
or
( m.businesstype_range = 'PART' and exists( select 1 from product_keyword_range mar where m.id = mar.keyword_id and mar.range_id={$val}))
)";
})
->getWhere();
}