/** * 把一个表的数据转换到另外一个表中 * * @param string $srcTableName 源表名 * @param array $condArray * array( * array('supplier_id = ?', $supplier_id) * array('is_on_sale = ?', 1) * array('supplier_price > ? or supplier_price < ?', $priceMin, $priceMax) * ) * @param array $optionArray 排序分页等条件 ,格式例如 array('order' => 'goods_id asc', 'offset' => 100, 'limit' => '10') * @param string $dstTableName 目的表名 * @param array $columnMap 列对应,格式如 array('id' => 'goods_id' , 'name' => 'goods_name') * @param array $srcValueConvertFuncArray * 对源数据做装换,例如: array('id' => function($srcValue, $srcRecord){ return $srcValue + 1;} , ...),用闭包函数做转化 * @param callable $recordPreFunc 在查询到 src 的记录之后调用 ,函数原型 function(&$srcRecord){ ... } * @param callable $recordPostFunc 在 src 已经往 dst 赋值完成之后,dst 还没有写入数据库之前调用,函数原型 function(&$srcRecord, &dstRecord){...} * */ public function convertTable($srcTableName, $condArray, $optionArray, $dstTableName, $columnMap, $srcValueConvertFuncArray, $recordPreFunc = null, $recordPostFunc = null) { $srcFieldArray = array(); $dstFieldArray = array(); foreach ($columnMap as $srcField => $dstField) { $srcFieldArray[] = $srcField; $dstFieldArray[] = $dstField; } $srcTable = new SrcDataMapper($srcTableName); // 构造查询条件 $filter = null; if (!empty($condArray)) { $filter = QueryBuilder::buildAndFilter($condArray); } // 获得总数 $totalRecordCount = $srcTable->count($filter); printLog('begin convertTable ' . $srcTableName . '-->' . $dstTableName . ' totalRecordCount:' . $totalRecordCount, self::$loggerSource); $recordLeft = $totalRecordCount; // 剩余多少记录需要处理 $queryOffset = 0; // 查询的起始位置 while ($recordLeft > 0) { // 这次需要处理多少记录 $processCount = $recordLeft > $this->batchProcessCount ? $this->batchProcessCount : $recordLeft; // 记录处理进度 printLog('totalRecordCount:' . $totalRecordCount . ' recordLeft:' . $recordLeft . ' processCount:' . $processCount, self::$loggerSource); $recordLeft -= $processCount; // 从源表查询数据 $srcField = '*'; if (!empty($srcFieldArray)) { $srcField = implode(',', $srcFieldArray); } // 处理查询的起始位置 $optionArray = array_merge($optionArray, array('offset' => $queryOffset, 'limit' => $processCount)); $queryOffset += $processCount; // 查询数据 $srcRecordList = $srcTable->select($srcField, $filter, $optionArray); // 转换数据到目标表中 foreach ($srcRecordList as $srcRecordItem) { // PreFunc 处理 if ($recordPreFunc) { $recordPreFunc($srcRecordItem); } $dstTable = new DstDataMapper($dstTableName); //字段复制 foreach ($columnMap as $srcField => $dstField) { // 无意义的字段,不复制 if (null == $dstField) { continue; } if (isset($srcValueConvertFuncArray) && isset($srcValueConvertFuncArray[$srcField])) { if (!is_callable($srcValueConvertFuncArray[$srcField])) { printLog($srcField . ' value convert is not function ', self::$loggerSource, \Core\Log\Base::ERROR); continue; } // 做数据转化 $dstTable->{$dstField} = $srcValueConvertFuncArray[$srcField]($srcRecordItem[$srcField], $srcRecordItem); } else { $dstTable->{$dstField} = $srcRecordItem[$srcField]; } } // postFunc 处理 if ($recordPostFunc) { $recordPostFunc($srcRecordItem, $dstTable); } $dstTable->save(); unset($dstTable); // 及时释放数据,优化内存使用 } unset($srcRecordList); // 及时释放数据,优化内存使用 gc_collect_cycles(); // 主动做一次垃圾回收 } printLog('finish convertTable ' . $srcTableName . '-->' . $dstTableName . ' totalRecordCount:' . $totalRecordCount, self::$loggerSource); }
/** * * 取得一组记录的数目,用于分页 * * @return int 查询条数 * * @param mixed $table 需要查询的数据表,可以是单个表,例如:'user', * 也可以是多个表的数组,例如:array('user' ,'order_info' => 'oi') * * @param array $condArray 查询条件数组,例如: * array( * array('supplier_id = ?', $supplier_id) * array('is_on_sale = ?', 1) * array('supplier_price > ? or supplier_price < ?', $priceMin, $priceMax) * ) * 这些查询条件最终会用 and 拼接起来 * @param array $optionArray 目前不支持 Having 查询,留待以后扩展 * @param int $ttl 缓存多少时间 * */ public function _countArray($table, array $condArray = null, array $optionArray = null, $ttl = 0) { // 构造参数验证数组 $validatorArray = array(); $validatorArray['table'] = $table; $validatorArray['ttl'] = $ttl; if (null != $condArray) { $validatorArray['condArray'] = $condArray; } if (null != $optionArray) { $validatorArray['optionArray'] = $optionArray; } // 参数验证 $validator = new Validator($validatorArray, ''); $table = $validator->required()->validate('table'); $ttl = $validator->digits()->min(0)->validate('ttl'); if (null != $condArray) { $condArray = $validator->requireArray(false)->validate('condArray'); } if (null != $optionArray) { $optionArray = $validator->requireArray(false)->validate('optionArray'); } $this->validate($validator); // 构造查询条件 $filter = null; if (!empty($condArray)) { $filter = QueryBuilder::buildAndFilter($condArray); } // 创建 DataMapper $dataMapper = new DataMapper($table); if (is_string($table)) { //简单的单表查询 $table = array($table); } if (is_array($table)) { // 复杂的多表查询 return $dataMapper->selectCount($table, $filter, $optionArray, $ttl); } throw new \InvalidArgumentException('table should be string or array'); }
/** * 取得对应分类下面商品的总数,用于分页显示 * * @return int 商品总数 * * @param int $categoryId 分类的ID * @param int $level 取得多少层,子分类有可能很深,我们只取有限层次 * @param string $systemTag 系统标记 * @param int $ttl 缓存时间 */ public function countGoodsArray($categoryId, $level, $systemTag, $ttl = 0) { // 参数验证 $validator = new Validator(array('categoryId' => $categoryId, 'level' => $level, 'systemTag' => $systemTag, 'ttl' => $ttl)); $categoryId = $validator->digits()->min(0)->validate('categoryId'); $level = $validator->required()->digits()->min(1)->validate('level'); $systemTag = $validator->validate('systemTag'); $ttl = $validator->digits()->min(0)->validate('ttl'); $this->validate($validator); $childrenIdArray = $this->fetchCategoryChildrenIdArray($categoryId, $level, $ttl); $childrenIdArray[] = $categoryId; // 加入父节点 $queryCondArray = array(); $queryCondArray[] = array('is_delete = 0 AND is_on_sale = 1 AND is_alone_sale = 1'); // 构建 SQL 的 in 语句, cat_id in (100,20,30) $queryCondArray[] = array(QueryBuilder::buildInCondition('cat_id', $childrenIdArray, \PDO::PARAM_INT)); if (!empty($systemTag)) { $queryCondArray[] = array('system_tag_list like ? ', '%' . Utils::makeTagString(array($systemTag)) . '%'); } $dataMapper = new DataMapper('goods'); return $dataMapper->count(QueryBuilder::buildAndFilter($queryCondArray), null, $ttl); }
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; }