/**
* 对查询参数做一些处理，使得它能适合我们的 sql 搜索
*
* @param $searchParamArray
*
* @return array
* @throws \InvalidArgumentException
*/
protected function prepareSearchParam($searchParamArray)
{
if (!is_array($searchParamArray)) {
throw new \InvalidArgumentException('searchParam illegal : ' . var_export($searchParamArray, true));
}
$resultParamArray = array();
foreach ($searchParamArray as $searchParam) {
if (is_array($searchParam) && count($searchParam) == 3) {
switch ($searchParam[0]) {
// 取分类下面的商品，包括子分类的商品，我们需要取得所有子分类的 ID 然后重新构造查询条件
case 'category_id':
case 'g.category_id':
case 'cat_id':
case 'g.cat_id':
$goodsCategoryService = new GoodsCategoryService();
// 最多取 5 层分类，缓存 10 分钟
$childrenIdArray = $goodsCategoryService->fetchCategoryChildrenIdArray($searchParam[2], 5, 600);
$childrenIdArray[] = $searchParam[2];
// 加入父节点
// 构造新的查询条件
$searchParam = array(QueryBuilder::buildInCondition('g.cat_id', $childrenIdArray, \PDO::PARAM_INT));
break;
// 允许多品牌查询，需要生成 OR 查询条件，多品牌查询的情况 brand_id = 123_45_65_35 ，采用 _ 作为分隔
// 允许多品牌查询，需要生成 OR 查询条件，多品牌查询的情况 brand_id = 123_45_65_35 ，采用 _ 作为分隔
case 'brand_id':
case 'g.brand_id':
// 只处理 = 的情况
if ('=' != $searchParam[1] || false === strpos($searchParam[2], '_')) {
break;
}
$brandValueArray = explode('_', $searchParam[2]);
$brandQueryCondArray = array();
foreach ($brandValueArray as $brandValue) {
if (!empty($brandValue)) {
$brandQueryCondArray[] = array('g.brand_id = ?', intval($brandValue));
}
}
// 生成一串的 OR 查询
$searchParam = QueryBuilder::buildOrFilter($brandQueryCondArray);
break;
default:
break;
}
}
$resultParamArray[] = $searchParam;
}
return $resultParamArray;
}
protected function prepareSearchParam($searchParamArray)
{
if (!is_array($searchParamArray)) {
throw new \InvalidArgumentException('searchParam illegal : ' . var_export($searchParamArray, true));
}
// 调用父类先处理
$searchParamArray = parent::prepareSearchParam($searchParamArray);
$resultParamArray = array();
foreach ($searchParamArray as $searchParam) {
$addParam = true;
if (is_array($searchParam) && count($searchParam) == 3) {
switch ($searchParam[0]) {
/** 根据过滤规则，我们构造子查询
* 结构 array('ga.filter', '123.321.45', '100_20.34.67_78')
* 其中 123.321.45 为 attr_item_id
* 100_20.34.67_78 为 goods_attr_id 对应的值
*/
case 'ga.filter':
// 不加入这个参数
$addParam = false;
// 没有值，不需要过滤
$trimSearchParam2 = trim(str_replace('.', '', $searchParam[2]));
// 有可能没有值，全部为点 "..."
if (empty($searchParam[1]) || empty($searchParam[2]) || empty($trimSearchParam2)) {
break;
}
$goodsTypeService = new GoodsTypeService();
// 构造子查询
$queryJoinTable = '';
$firstJoinTable = '';
$queryCondArray = array();
// 构造子查询
$attrItemIdArray = explode('.', $searchParam[1]);
$goodsAttrIdStrArray = explode('.', $searchParam[2]);
$count = min(count($attrItemIdArray), count($goodsAttrIdStrArray));
for ($index = 0; $index < $count; $index++) {
$attrItemId = abs(intval($attrItemIdArray[$index]));
$goodsAttrIdArray = explode('_', $goodsAttrIdStrArray[$index]);
// 跳过无效值
if ($attrItemId <= 0 || empty($goodsAttrIdArray)) {
continue;
}
$goodsAttrItemCond = array();
foreach ($goodsAttrIdArray as $goodsAttrId) {
$goodsAttrId = abs(intval($goodsAttrId));
$goodsAttr = $goodsTypeService->loadGoodsAttrById($goodsAttrId);
// 无效的属性，返回
if ($goodsAttr->isEmpty()) {
continue;
}
$goodsAttrItemCond[] = array("attr_item_value = ?", $goodsAttr['attr_item_value']);
}
if (!empty($goodsAttrItemCond)) {
$condArray = QueryBuilder::buildAndFilter(array(array('attr_item_id = ?', $attrItemId), QueryBuilder::buildOrFilter($goodsAttrItemCond)));
$tmpTableName = 'ga' . $index;
$tmpTable = '(select distinct(goods_id) from ' . DataMapper::tableName('goods_attr') . ' where ' . array_shift($condArray) . ') as ' . $tmpTableName;
$queryCondArray = array_merge($queryCondArray, $condArray);
if (empty($queryJoinTable)) {
$queryJoinTable = $tmpTable;
$firstJoinTable = $tmpTableName;
} else {
$queryJoinTable .= ' INNER JOIN ' . $tmpTable . ' on ' . $firstJoinTable . '.goods_id = ' . $tmpTableName . '.goods_id ';
}
}
}
// 构造子查询
$this->searchTable = DataMapper::tableName('goods') . ' as g INNER JOIN ' . '(select distinct(' . $firstJoinTable . '.goods_id) from (' . $queryJoinTable . ')) as ga on g.goods_id = ga.goods_id';
/**
* 这里是一个很 tricky 的构造查询的方法
*
* 我们不想拼接 SQL 语句，比如 attr_item_value = $attr_item_value，
* 而是采用 array('attr_item_value = ?', $attr_item_value)，这样可以 SQL Bind 避免 SQL 注入
*
* 由于前面的 子查询带了很多 ? 查询，所以我们需要把参数值 unshift 到第一个的位置
*
*/
// 头部压入一个空条件
array_unshift($queryCondArray, '1=1');
// 把这个参数压入到头部
array_unshift($resultParamArray, $queryCondArray);
break;
default:
break;
}
}
// 是否加入参数
if ($addParam) {
$resultParamArray[] = $searchParam;
}
}
return $resultParamArray;
}
