PHP Varien_Db_Select类代码示例

 /**
  * Add stock status limitation to catalog product price index select object
  *
  * @param Varien_Db_Select $select
  * @param string|Zend_Db_Expr $entityField
  * @param string|Zend_Db_Expr $websiteField
  * @return Mage_CatalogInventory_Model_Resource_Stock_Status
  */
 public function prepareCatalogProductIndexSelect(Varien_Db_Select $select, $entityField, $websiteField)
 {
     $subquery = $this->getReadConnection()->select()->from(array('sub_ciss' => $this->getMainTable()), array('product_id', 'website_id'))->join(array('cis' => $this->getTable('cataloginventory/stock')), 'sub_ciss.stock_id=cis.stock_id', array())->join(array('acsw' => $this->getTable('adm_warehouse/stock_website')), 'acsw.stock_id=cis.stock_id', array())->columns(new Zend_Db_Expr("MAX(sub_ciss.stock_status) AS stock_status"))->where('cis.is_active = 1')->where('acsw.website_id=sub_ciss.website_id OR acsw.website_id=0')->group(array('sub_ciss.product_id', 'sub_ciss.website_id'));
     $select->join(array('ciss' => $subquery), "ciss.product_id = {$entityField} AND ciss.website_id = {$websiteField}", array());
     $select->where('ciss.stock_status = ?', Mage_CatalogInventory_Model_Stock_Status::STATUS_IN_STOCK);
     return $this;
 }

 protected function applyMultipleValuesFilter($ids)
 {
     $collection = $this->getLayer()->getProductCollection();
     $attribute = $this->getAttributeModel();
     $table = Mage::getSingleton('core/resource')->getTableName('catalogindex/eav');
     //check for prefix
     $alias = 'attr_index_' . $attribute->getId();
     $collection->getSelect()->join(array($alias => $table), $alias . '.entity_id=e.entity_id', array())->where($alias . '.store_id = ?', Mage::app()->getStore()->getId())->where($alias . '.attribute_id = ?', $attribute->getId())->where($alias . '.value IN (?)', $ids);
     switch (Mage::getStoreConfig('design/adjnav/filtering_logic')) {
         case 'AND':
             if (is_array($ids) && ($size = count($ids))) {
                 $adapter = $collection->getConnection();
                 $adapter = $collection->getConnection();
                 $subQuery = new Varien_Db_Select($adapter);
                 $subQuery->from(Mage::getResourceModel('catalogindex/attribute')->getMainTable(), 'entity_id')->where('store_id = ?', Mage::app()->getStore()->getId())->where('attribute_id = ?', $attribute->getId())->where('value IN (?)', $ids)->group(array('entity_id', 'attribute_id', 'store_id'))->having($size . ' = COUNT(value)');
                 $res = $adapter->fetchCol($subQuery);
                 if ($res) {
                     $collection->getSelect()->where($alias . '.entity_id IN (?)', $res);
                 } else {
                     $collection->getSelect()->where($alias . '.entity_id IN (-1)');
                 }
             }
             break;
         case 'OR':
         default:
             break;
     }
     if (count($ids) > 1) {
         $collection->getSelect()->distinct(true);
     }
     return $this;
 }

 protected function applyMultipleValuesFilter($ids)
 {
     $collection = $this->getLayer()->getProductCollection();
     $attribute = $this->getAttributeModel();
     $table = Mage::getSingleton('core/resource')->getTableName('catalogindex/eav');
     //check for prefix
     $helper = Mage::helper('adjnav');
     $alias = 'attr_index_' . $attribute->getId();
     $collection->getSelect()->join(array($alias => $table), $alias . '.entity_id=e.entity_id', array())->where($alias . '.store_id = ?', Mage::app()->getStore()->getId())->where($alias . '.attribute_id = ?', $attribute->getId())->where($alias . '.value IN (?)', $ids);
     if (is_array($ids) && ($size = count($ids))) {
         $adapter = $collection->getConnection();
         $subQuery = new Varien_Db_Select($adapter);
         $subQuery->from(array('e' => Mage::getModel('catalog/product')->getResource()->getTable('catalog/product')), 'entity_id')->join(array('a' => Mage::getModel('catalog/product')->getResource()->getTable('catalog/product_index_eav')), 'a.entity_id = e.entity_id', array());
         $SBBStatus = $helper->getShopByBrandsStatus();
         $forbidConfigurables = $SBBStatus && Mage::helper('aitmanufacturers')->canUseLayeredNavigation(Mage::registry('shopby_attribute'), true);
         if (!$forbidConfigurables) {
             $subQuery->where('e.type_id != ?', Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE);
         }
         $subQuery->where('a.store_id = ?', Mage::app()->getStore()->getId())->where('a.attribute_id = ?', $attribute->getId())->where('a.value IN (?)', $ids)->group(array('a.entity_id', 'a.attribute_id', 'a.store_id'));
         $res = $adapter->fetchCol($subQuery);
         /**
          * @author ksenevich@aitoc.com
          */
         self::_addFilterValues($attribute->getId(), $res, $ids);
     }
     if (count($ids) > 1) {
         $collection->getSelect()->distinct(true);
     }
     return $this;
 }

 /**
  * @param Varien_Db_Select $select
  * @param Mana_Filters_Model_Filter_Attribute $filter
  * @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $collection
  * @return mixed
  */
 public function countItems($select, $filter, $collection)
 {
     $db = $this->_getReadAdapter();
     $schema = $this->seoHelper()->getActiveSchema(Mage::app()->getStore()->getId());
     $fields = array('seo_include_filter_name' => new Zend_Db_Expr("`url`.`final_include_filter_name`"), 'seo_position' => new Zend_Db_Expr("`url`.`position`"), 'seo_id' => new Zend_Db_Expr("`url`.`id`"), 'seo_url_key' => new Zend_Db_Expr("`url`.`final_url_key`"));
     $select->joinLeft(array('url' => $this->getTable('mana_seo/url')), $db->quoteInto("`url`.`option_id` = `eav`.`value` AND `url`.`status` = 'active' AND `url`.`schema_id` = ?", $schema->getId()), null)->columns($fields)->group($fields);
 }

 /**
  * Apply price rule price to price index table
  *
  * @param Varien_Db_Select $select
  * @param array|string $indexTable
  * @param string $entityId
  * @param string $customerGroupId
  * @param string $websiteId
  * @param array $updateFields       the array of fields for compare with rule price and update
  * @param string $websiteDate
  * @return Mage_CatalogRule_Model_Resource_Rule_Product_Price
  */
 public function applyPriceRuleToIndexTable(Varien_Db_Select $select, $indexTable, $entityId, $customerGroupId, $websiteId, $updateFields, $websiteDate)
 {
     if (empty($updateFields)) {
         return $this;
     }
     if (is_array($indexTable)) {
         foreach ($indexTable as $k => $v) {
             if (is_string($k)) {
                 $indexAlias = $k;
             } else {
                 $indexAlias = $v;
             }
             break;
         }
     } else {
         $indexAlias = $indexTable;
     }
     $select->join(array('rp' => $this->getMainTable()), "rp.rule_date = {$websiteDate}", array())->where("rp.product_id = {$entityId} AND rp.website_id = {$websiteId} AND rp.customer_group_id = {$customerGroupId}");
     foreach ($updateFields as $priceField) {
         $priceCond = $this->_getWriteAdapter()->quoteIdentifier(array($indexAlias, $priceField));
         $priceExpr = $this->_getWriteAdapter()->getCheckSql("rp.rule_price < {$priceCond}", 'rp.rule_price', $priceCond);
         $select->columns(array($priceField => $priceExpr));
     }
     $query = $select->crossUpdateFromSelect($indexTable);
     $this->_getWriteAdapter()->query($query);
     return $this;
 }

 /**
  * Join information for last staging logs
  *
  * @param  string $table
  * @param  Varien_Db_Select $select
  * @return Varien_Db_Select $select
  */
 public function getLastStagingLogQuery($table, $select)
 {
     $subSelect = clone $select;
     $subSelect->from($table, array('staging_id', 'log_id', 'action'))->order('log_id DESC');
     $select->from(array('t' => new Zend_Db_Expr('(' . $subSelect . ')')))->group('staging_id');
     return $select;
 }

 public function export(Mage_Core_Model_Store $oStore, $vFeedname, Mage_Core_Model_Config_Element $oConfig)
 {
     Mage::getSingleton('aligent_feeds/log')->log("Beginning {$vFeedname} export for store #" . $oStore->getId() . " - " . $oStore->getName());
     Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
     Mage::getSingleton('aligent_feeds/log')->log("Initialising file writers...");
     $this->_initWriters($oStore, $vFeedname, $oConfig);
     // Prepare the csv file header
     Mage::getSingleton('aligent_feeds/log')->log("Begin preparing header rows...");
     Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
     $this->_prepareHeaders($oConfig);
     // Initialise the formatter
     Mage::getSingleton('aligent_feeds/log')->log("Initialising Feed Formatter...");
     Mage::getSingleton('aligent_feeds/feed_formatter')->init($oStore, $oConfig);
     Mage::getSingleton('aligent_feeds/log')->log("Initialised Feed Formatter.");
     Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
     $oConn = Mage::getModel('core/resource')->getConnection('catalog_read');
     $vCategoryProductTable = Mage::getModel('core/resource_setup', 'core_setup')->getTable('catalog/category_product');
     $vCategoryFlatTable = Mage::getResourceSingleton('catalog/category_flat')->getMainStoreTable($oStore->getId());
     $vProductFlatTable = Mage::getResourceModel('catalog/product_flat_indexer')->getFlatTableName($oStore->getId());
     // Complicated subquery to get the most deeply nested category that this
     // product is assigned to.  Picking the most deeply nested on the assumption
     // that the deepest category is most likely to be the most specific.
     $oSubSelect = new Varien_Db_Select($oConn);
     $oSubSelect->from(array('ccf' => $vCategoryFlatTable), 'entity_id')->joinInner(array('ccp2' => 'catalog_category_product'), 'ccf.entity_id=ccp2.category_id', array())->where('ccp2.product_id=main_table.entity_id')->where('ccf.is_active=1')->order('level', Zend_Db_Select::SQL_DESC)->limit(1);
     $oSelect = new Varien_Db_Select($oConn);
     $oSelect->from(array('main_table' => $vProductFlatTable), array('main_table.*', 'category_id' => new Zend_Db_Expr('(' . $oSubSelect . ')')))->where('visibility IN (?)', array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG, Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH));
     // Allow the feed definition to include a "before_query_filter".  This method
     // will be allowed to modify the query before it's executed.
     if ($oConfig->before_query_filter) {
         Mage::getSingleton('aligent_feeds/log')->log("Calling before query filter...");
         $vClass = (string) $oConfig->before_query_filter->class;
         $vMethod = (string) $oConfig->before_query_filter->method;
         $aParams = (array) $oConfig->before_query_filter->params;
         Mage::getSingleton($vClass)->{$vMethod}($oSelect, $oStore, $aParams);
         Mage::getSingleton('aligent_feeds/log')->log("Before query filter done.");
     }
     Mage::getSingleton('aligent_feeds/log')->log("Exporting products...");
     $oResource = Mage::getModel('core/resource_iterator')->walk($oSelect, array(function ($aArgs) {
         Mage::getSingleton('aligent_feeds/log')->log("Exporting product #" . $aArgs['idx'] . "  SKU: " . $aArgs['row']['sku'], Zend_Log::DEBUG, true);
         if ($aArgs['idx'] % 100 == 0) {
             Mage::getSingleton('aligent_feeds/log')->log("Exporting product #" . $aArgs['idx'] . "...", Zend_Log::INFO);
             Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
         }
         $aRows = Mage::getSingleton('aligent_feeds/feed_formatter')->prepareRow($aArgs['row']);
         if (count($aRows) > 0) {
             foreach ($aRows as $aRow) {
                 foreach ($aArgs['writers'] as $oWriter) {
                     $oWriter->writeDataRow($aRow);
                 }
             }
         }
     }), array('writers' => $this->_oWriters, 'config' => $oConfig, 'store' => $oStore));
     $this->_closeWriters();
     $this->_sendFeed();
     Mage::getSingleton('aligent_feeds/status')->addSuccess("Generated {$vFeedname} data for store #" . $oStore->getId() . " - " . $oStore->getName());
     Mage::getSingleton('aligent_feeds/log')->log("Finished {$vFeedname} data export for store #" . $oStore->getId() . " - " . $oStore->getName());
     Mage::getSingleton('aligent_feeds/log')->logMemoryUsage();
     return $this;
 }

 /**
  * @param Varien_Db_Select $select
  */
 protected function _doWarehouseCompatibility($select)
 {
     if (Mage::getConfig()->getModuleConfig('Innoexts_Warehouse')->is('active', 'true')) {
         $storeId = Mage::app()->getStore()->getId();
         $stockId = Mage::helper('warehouse')->getStockIdByStoreId($storeId) ? Mage::helper('warehouse')->getStockIdByStoreId($storeId) : Mage::helper('warehouse')->getDefaultStockId();
         $select->where('stock_status.stock_id = ?', $stockId);
     }
 }

 /**
  * Join url rewrite to select
  *
  * @param Varien_Db_Select $select
  * @param int $storeId
  * @return Mage_Catalog_Helper_Category_Url_Rewrite
  */
 public function joinTableToSelect(Varien_Db_Select $select, $storeId)
 {
     if ($this->_helper()->OptimizeCategoriesLeftJoin($storeId)) {
         $select->joinLeft(array('url_rewrite' => $this->_resource->getTableName('urlindexer/url_rewrite')), 'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND ' . $this->_connection->quoteInto('url_rewrite.store_id = ? AND ', (int) $storeId) . $this->_connection->prepareSqlCondition('url_rewrite.id_path', array('like' => 'category/%')), array('request_path' => 'url_rewrite.request_path'));
         return $this;
     }
     return parent::joinTableToSelect($select, $storeId);
 }

 /**
  * @param Varien_Object $filter
  * @param array|string|int $value
  * @param Varien_Db_Select $select
  * @return void
  */
 public function prepareSelect($filter, $value, $select)
 {
     $alias = 'category_idx';
     $value = (array) $value;
     foreach ($value as $_value) {
         $where[] = intval($_value);
     }
     $select->join(array($alias => Mage::getSingleton('core/resource')->getTableName('catalog/category_product_index')), $alias . '.category_id IN (' . implode(',', $where) . ') AND ' . $alias . '.product_id = e.entity_id', array());
 }

 public function changedItemsSelectModifier(Varien_Db_Select $select)
 {
     $select->join(array('alp' => $this->getMainTable()), '`lp`.`id` = `alp`.`listing_product_id`', array());
     $select->where('`alp`.`is_variation_product` = 0
          OR
          (
             `alp`.`is_variation_product` = 1
              AND
              `alp`.`is_variation_matched` = 1
          )');
 }

 /**
  * Retrieve all necessary objects mocks which used inside customer storage
  *
  * @param int $tableRowsCount
  * @param array $tableData
  * @param array $aliasesMap
  *
  * @return array
  */
 protected function _getModelDependencies($tableRowsCount = 0, $tableData = array(), $aliasesMap = array())
 {
     $this->_selectMock = $this->getMock('Varien_Db_Select', array(), array(), '', false);
     $this->_selectMock->expects($this->any())->method('from')->will($this->returnSelf());
     $this->_selectMock->expects($this->any())->method('where')->will($this->returnCallback(array($this, 'whereCallback')));
     $adapterMock = $this->getMock('Varien_Db_Adapter_Pdo_Mysql', array('select', 'update', 'fetchAll', 'fetchOne'), array(), '', false);
     $adapterMock->expects($this->any())->method('select')->will($this->returnValue($this->_selectMock));
     $adapterMock->expects($this->any())->method('update')->will($this->returnCallback(array($this, 'updateCallback')));
     $adapterMock->expects($this->any())->method('fetchAll')->will($this->returnValue($tableData));
     $adapterMock->expects($this->any())->method('fetchOne')->will($this->returnValue($tableRowsCount));
     return array('resource_config' => 'not_used', 'connection_config' => 'not_used', 'module_config' => 'not_used', 'base_dir' => 'not_used', 'path_to_map_file' => 'not_used', 'connection' => $adapterMock, 'core_helper' => new Mage_Core_Helper_Data(), 'aliases_map' => $aliasesMap);
 }

 public function newAttributeSetsCollection()
 {
     $r = Mage::getSingleton('core/resource');
     // all product attribute sets
     $allProductSets = Mage::getResourceModel('eav/entity_attribute_set_collection')->setEntityTypeFilter(Mage::getModel('catalog/product')->getResource()->getTypeId());
     // the sets already in the table
     $model = new Varien_Db_Select(Mage::getResourceModel('listrak/product_attribute_set_map')->getReadConnection());
     $model->from(array('current' => $r->getTableName('listrak/product_attribute_set_map')))->where('main_table.attribute_set_id = current.attribute_set_id');
     // new product attribute sets
     $allProductSets->getSelect()->where('NOT EXISTS (' . $model . ')');
     return $allProductSets;
 }

 /**
  * Check structure of sql query
  *
  * @param Varien_Db_Select $select
  * @return array
  */
 public function fetchAllCallback(Varien_Db_Select $select)
 {
     $whereParts = $select->getPart(Varien_Db_Select::WHERE);
     $this->assertCount(2, $whereParts);
     $this->assertContains("rule_name IS NOT NULL", $whereParts[0]);
     $this->assertContains("rule_name <> ''", $whereParts[1]);
     $orderParts = $select->getPart(Varien_Db_Select::ORDER);
     $this->assertCount(1, $orderParts);
     $expectedOrderParts = array('rule_name', 'ASC');
     $this->assertEquals($expectedOrderParts, $orderParts[0]);
     return $this->_rules;
 }

 /** Add filters after all filters have applied for configurable products
  * 
  * @param Varien_Event_Observer $observer
  * @author ksenevich@aitoc.com
  */
 public function onCatalogProductCollectionLoadBefore(Varien_Event_Observer $observer)
 {
     /* @var $versionHelper AdjustWare_Nav_Helper_Version */
     $versionHelper = Mage::helper('adjnav/version');
     /* @var $collection Varien_Data_Collection_Db */
     $collection = $observer->getEvent()->getCollection();
     $adapter = $collection->getConnection();
     $helper = Mage::helper('adjnav');
     $filterAttributes = AdjustWare_Nav_Model_Catalog_Layer_Filter_Attribute::getFilterAttributes();
     $filterProducts = AdjustWare_Nav_Model_Catalog_Layer_Filter_Attribute::getFilterProducts();
     $configurableProducts = array();
     $childByAttribute = array();
     $child2parent = array();
     $productModel = Mage::getModel('catalog/product')->getResource();
     $attributesCount = count($filterAttributes);
     if ($versionHelper->hasConfigurableFix()) {
         foreach ($filterAttributes as $attributeId => $attributeValues) {
             $configurableQuery = new Varien_Db_Select($adapter);
             $configurableQuery->from(array('e' => $productModel->getTable('catalog/product')), 'entity_id')->join(array('l' => $productModel->getTable($versionHelper->getProductRelationTable())), 'l.parent_id = e.entity_id', array('child_id' => $versionHelper->getProductIdChildColumn()))->join(array('a' => Mage::getResourceModel('adjnav/catalog_product_indexer_configurable')->getMainTable()), 'a.entity_id = l.' . $versionHelper->getProductIdChildColumn(), array())->where('e.type_id = ?', Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE)->where('a.store_id = ?', Mage::app()->getStore()->getId())->where('a.attribute_id = ?', $attributeId)->where('a.value IN (?)', $attributeValues)->group(array('e.entity_id', 'l.' . $versionHelper->getProductIdChildColumn(), 'a.store_id'));
             $statement = $adapter->query($configurableQuery);
             while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
                 $child2parent[$row['child_id']][] = $row['entity_id'];
                 $childByAttribute[$row['child_id']][$attributeId] = true;
             }
         }
         foreach ($childByAttribute as $childId => $attributeIds) {
             if (count($attributeIds) == $attributesCount) {
                 $configurableProducts[] = $childId;
                 foreach ($child2parent[$childId] as $parentId) {
                     $configurableProducts[] = $parentId;
                 }
             }
         }
     }
     $configurableProducts = array_unique($configurableProducts);
     /* Commenting this section as it was causing issue */
     /*
             foreach ($filterProducts as $attributeId => $filterProducts)
             {
                 $alias          = 'attr_index_'.$attributeId;
                 $filterProducts = array_merge($filterProducts, $configurableProducts);
     
                 if (empty($filterProducts))
                 {
                     $filterProducts = array(-1);
                 }
     
                 $collection->getSelect()->where($alias.'.entity_id IN (?)', $filterProducts);
             }
     */
     AdjustWare_Nav_Model_Catalog_Layer_Filter_Attribute::cleanFilterAttributes();
 }