/** * 对查询参数做一些处理,使得它能适合我们的 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; }