PHP Varien_Db_Adapter_Interface::getConcatSql方法代碼示例

 /**
  * Fill temporary table with category (and children) redirects
  *
  * @param Mage_Catalog_Model_Category $category
  * @param int $storeId
  *
  * @return void
  */
 protected function _insertCategoryRedirectsToTemporaryTable($category, $storeId)
 {
     $concatSql = $this->_connection->getConcatSql(array('urc.request_path', $this->_getCategorySeoSuffixCaseSql('cr.store_id', $category)));
     $select = $this->_connection->select();
     $categoryExpr = sprintf("cr.category_id = %d OR ce.path LIKE '%s'", (int) $category->getId(), $this->_getCategoryPath($category->getId()) . '/%');
     $select->from(array('cr' => $this->_resource->getTableName('enterprise_catalog/category')), array($concatSql, 'urc.target_path', 'cr.store_id', 'ce.entity_id', new Zend_Db_Expr('NULL')))->joinInner(array('ce' => $this->_resource->getTableName('catalog/category')), 'ce.entity_id = cr.category_id', array(''))->joinInner(array('urc' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'cr.url_rewrite_id = urc.url_rewrite_id AND cr.store_id = urc.store_id
             AND urc.entity_type = ' . Enterprise_Catalog_Model_Category::URL_REWRITE_ENTITY_TYPE, array(''))->where($categoryExpr);
     if (!empty($storeId)) {
         $select->where('urc.store_id = ?', (int) $storeId);
     }
     $query = $select->insertFromSelect(self::TMP_TABLE_NAME, array('identifier', 'target_path', 'store_id', 'category_id', 'product_id'), Varien_Db_Adapter_Interface::INSERT_ON_DUPLICATE);
     $this->_connection->query($query);
 }

 /**
  * Retrieve select for reindex products of non anchor categories
  *
  * @param Mage_Core_Model_Store $store
  * @return Varien_Db_Select
  */
 protected function _getAnchorCategoriesSelect(Mage_Core_Model_Store $store)
 {
     if (!isset($this->_anchorCategoriesSelect[$store->getId()])) {
         /** @var $eavConfig Mage_Eav_Model_Config */
         $eavConfig = $this->_factory->getSingleton('eav/config');
         $isAnchorAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Category::ENTITY, 'is_anchor')->getId();
         $statusAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'status')->getId();
         $visibilityAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'visibility')->getId();
         $rootCatIds = explode('/', $this->_getPathFromCategoryId($store->getRootCategoryId()));
         array_pop($rootCatIds);
         $select = $this->_connection->select()->from(array('cc' => $this->_getTable('catalog/category')), array())->joinInner(array('cc2' => $this->_getTable('catalog/category')), 'cc2.path LIKE ' . $this->_connection->getConcatSql(array($this->_connection->quoteIdentifier('cc.path'), $this->_connection->quote('/%'))) . ' AND cc.entity_id NOT IN (' . implode(',', $rootCatIds) . ')', array())->joinInner(array('ccp' => $this->_getTable('catalog/category_product')), 'ccp.category_id = cc2.entity_id', array())->joinInner(array('cpw' => $this->_getTable('catalog/product_website')), 'cpw.product_id = ccp.product_id', array())->joinInner(array('cpsd' => $this->_getTable(array('catalog/product', 'int'))), 'cpsd.entity_id = ccp.product_id AND cpsd.store_id = 0 AND cpsd.attribute_id = ' . $statusAttributeId, array())->joinLeft(array('cpss' => $this->_getTable(array('catalog/product', 'int'))), 'cpss.entity_id = ccp.product_id AND cpss.attribute_id = cpsd.attribute_id' . ' AND cpss.store_id = ' . $store->getId(), array())->joinInner(array('cpvd' => $this->_getTable(array('catalog/product', 'int'))), 'cpvd.entity_id = ccp.product_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, array())->joinLeft(array('cpvs' => $this->_getTable(array('catalog/product', 'int'))), 'cpvs.entity_id = ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' . $store->getId(), array())->joinInner(array('ccad' => $this->_getTable(array('catalog/category', 'int'))), 'ccad.entity_id = cc.entity_id AND ccad.store_id = 0' . ' AND ccad.attribute_id = ' . $isAnchorAttributeId, array())->joinLeft(array('ccas' => $this->_getTable(array('catalog/category', 'int'))), 'ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id' . ' AND ccas.store_id = ' . $store->getId(), array())->where('cpw.website_id = ?', $store->getWebsiteId())->where($this->_connection->getIfNullSql('cpss.value', 'cpsd.value') . ' = ?', Mage_Catalog_Model_Product_Status::STATUS_ENABLED)->where($this->_connection->getIfNullSql('cpvs.value', 'cpvd.value') . ' IN (?)', array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG))->where($this->_connection->getIfNullSql('ccas.value', 'ccad.value') . ' = ?', 1)->columns(array('category_id' => 'cc.entity_id', 'product_id' => 'ccp.product_id', 'position' => new Zend_Db_Expr('ccp.position + 10000'), 'is_parent' => new Zend_Db_Expr('0'), 'store_id' => new Zend_Db_Expr($store->getId()), 'visibility' => new Zend_Db_Expr($this->_connection->getIfNullSql('cpvs.value', 'cpvd.value'))));
         $this->_anchorCategoriesSelect[$store->getId()] = $select;
     }
     return $this->_anchorCategoriesSelect[$store->getId()];
 }

 /**
  * Prepare temporary data
  *
  * @param Mage_Core_Model_Website $website
  * @return Varien_Db_Select
  */
 protected function _prepareTemporarySelect(Mage_Core_Model_Website $website)
 {
     /** @var $catalogFlatHelper Mage_Catalog_Helper_Product_Flat */
     $catalogFlatHelper = $this->_factory->getHelper('catalog/product_flat');
     /** @var $eavConfig Mage_Eav_Model_Config */
     $eavConfig = $this->_factory->getSingleton('eav/config');
     $priceAttribute = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'price');
     $select = $this->_connection->select()->from(array('rp' => $this->_resource->getTable('catalogrule/rule_product')), array())->joinInner(array('r' => $this->_resource->getTable('catalogrule/rule')), 'r.rule_id = rp.rule_id', array())->where('rp.website_id = ?', $website->getId())->order(array('rp.product_id', 'rp.customer_group_id', 'rp.sort_order', 'rp.rule_product_id'))->joinLeft(array('pg' => $this->_resource->getTable('catalog/product_attribute_group_price')), 'pg.entity_id = rp.product_id AND pg.customer_group_id = rp.customer_group_id' . ' AND pg.website_id = rp.website_id', array())->joinLeft(array('pgd' => $this->_resource->getTable('catalog/product_attribute_group_price')), 'pgd.entity_id = rp.product_id AND pgd.customer_group_id = rp.customer_group_id' . ' AND pgd.website_id = 0', array());
     $storeId = $website->getDefaultStore()->getId();
     if ($catalogFlatHelper->isEnabled() && $storeId && $catalogFlatHelper->isBuilt($storeId)) {
         $select->joinInner(array('p' => $this->_resource->getTable('catalog/product_flat') . '_' . $storeId), 'p.entity_id = rp.product_id', array());
         $priceColumn = $this->_connection->getIfNullSql($this->_connection->getIfNullSql('pg.value', 'pgd.value'), 'p.price');
     } else {
         $select->joinInner(array('pd' => $this->_resource->getTable(array('catalog/product', $priceAttribute->getBackendType()))), 'pd.entity_id = rp.product_id AND pd.store_id = 0 AND pd.attribute_id = ' . $priceAttribute->getId(), array())->joinLeft(array('p' => $this->_resource->getTable(array('catalog/product', $priceAttribute->getBackendType()))), 'p.entity_id = rp.product_id AND p.store_id = ' . $storeId . ' AND p.attribute_id = pd.attribute_id', array());
         $priceColumn = $this->_connection->getIfNullSql($this->_connection->getIfNullSql('pg.value', 'pgd.value'), $this->_connection->getIfNullSql('p.value', 'pd.value'));
     }
     $select->columns(array('grouped_id' => $this->_connection->getConcatSql(array('rp.product_id', 'rp.customer_group_id'), '-'), 'product_id' => 'rp.product_id', 'customer_group_id' => 'rp.customer_group_id', 'from_date' => 'r.from_date', 'to_date' => 'r.to_date', 'action_amount' => 'rp.action_amount', 'action_operator' => 'rp.action_operator', 'action_stop' => 'rp.action_stop', 'sort_order' => 'rp.sort_order', 'price' => $priceColumn, 'rule_product_id' => 'rp.rule_product_id', 'from_time' => 'rp.from_time', 'to_time' => 'rp.to_time'));
     return $select;
 }

 /**
  * Fill temporary table with category (and children) redirects
  *
  * @param Mage_Catalog_Model_Category $category
  *
  * @return void
  */
 protected function _insertCategoryRedirectsToTemporaryTable($category)
 {
     $concatSql = $this->_connection->getConcatSql(array('urc.request_path', $this->_getCategorySeoSuffixCaseSql('cr.store_id', $category)));
     $select = $this->_connection->select();
     $categoryExpr = sprintf("cr.category_id = %d OR ce.path LIKE '%s'", (int) $category->getId(), $this->_getCategoryPath($category->getId()) . '/%');
     $select->from(array('cr' => $this->_resource->getTableName('enterprise_catalog/category')), array($concatSql, 'urc.target_path'))->joinInner(array('ce' => $this->_resource->getTableName('catalog/category')), 'ce.entity_id = cr.category_id', array(''))->joinInner(array('urc' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'cr.url_rewrite_id = urc.url_rewrite_id', array(''))->where($categoryExpr)->where('ce.level > 1');
     $query = $select->insertFromSelect(self::TMP_TABLE_NAME, array('identifier', 'target_path'), Varien_Db_Adapter_Interface::INSERT_ON_DUPLICATE);
     $this->_connection->query($query);
 }