PHP Varien_Db_Select::where方法代码示例

 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;
 }

 /**
  * 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;
 }

 /**
  * Apply filters
  *
  * @return Enterprise_CustomerSegment_Model_Resource_Report_Customer_Collection
  */
 protected function _applyFilters()
 {
     if (!is_null($this->_websites)) {
         $this->_subQuery->where('website_id IN(?)', $this->_websites);
     }
     $this->getSelect()->where('e.entity_id IN(?)', new Zend_Db_Expr($this->_subQuery));
     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);
     }
 }

 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
          )');
 }

 /**
  * Add attribute join condition to select and return Zend_Db_Expr
  * attribute value definition
  *
  * If $condition is not empty apply limitation for select
  *
  * @param Varien_Db_Select $select
  * @param string $attrCode              the attribute code
  * @param string|Zend_Db_Expr $entity   the entity field or expression for condition
  * @param string|Zend_Db_Expr $store    the store field or expression for condition
  * @param Zend_Db_Expr $condition       the limitation condition
  * @param bool $required                if required or has condition used INNER join, else - LEFT
  * @return Zend_Db_Expr                 the attribute value expression
  */
 protected function _addAttributeToSelect($select, $attrCode, $entity, $store, $condition = null, $required = false)
 {
     $attribute = $this->_getAttribute($attrCode);
     $attributeId = $attribute->getAttributeId();
     $attributeTable = $attribute->getBackend()->getTable();
     $joinType = !is_null($condition) || $required ? 'join' : 'joinLeft';
     if ($attribute->isScopeGlobal()) {
         $alias = 'ta_' . $attrCode;
         $select->{$joinType}(array($alias => $attributeTable), "{$alias}.entity_id = {$entity} AND {$alias}.attribute_id = {$attributeId}" . " AND {$alias}.store_id = 0", array());
         $expression = new Zend_Db_Expr("{$alias}.value");
     } else {
         $dAlias = 'tad_' . $attrCode;
         $sAlias = 'tas_' . $attrCode;
         $select->{$joinType}(array($dAlias => $attributeTable), "{$dAlias}.entity_id = {$entity} AND {$dAlias}.attribute_id = {$attributeId}" . " AND {$dAlias}.store_id = 0", array());
         $select->joinLeft(array($sAlias => $attributeTable), "{$sAlias}.entity_id = {$entity} AND {$sAlias}.attribute_id = {$attributeId}" . " AND {$sAlias}.store_id = {$store}", array());
         $expression = new Zend_Db_Expr("IF({$sAlias}.value_id > 0, {$sAlias}.value, {$dAlias}.value)");
     }
     if (!is_null($condition)) {
         $select->where("{$expression}{$condition}");
     }
     return $expression;
 }

 /** Get counts from index by configurable attributes if applicable
  * 
  * @param Mage_Catalog_Model_Resource_Eav_Attribute $attribute
  * @param Varien_Db_Select $select
  */
 protected function _getCountForConfigurable($attribute, Varien_Db_Select $select, Varien_Db_Select $baseSelect)
 {
     /* @var $versionHelper AdjustWare_Nav_Helper_Version */
     $versionHelper = Mage::helper('adjnav/version');
     if (!$versionHelper->hasConfigurableFix()) {
         return false;
     }
     /* @var $configurableSelect Varien_Db_Select */
     $configurableSelect = clone $baseSelect;
     $configurableSelect->reset(Zend_Db_Select::COLUMNS);
     $configurableSelect->reset(Zend_Db_Select::ORDER);
     $configurableSelect->reset(Zend_Db_Select::LIMIT_COUNT);
     $configurableSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
     $configurableFrom = $configurableSelect->getPart(Zend_Db_Select::FROM);
     $attributeJoins = array();
     foreach ($configurableFrom as $alias => $tableInfo) {
         if (0 === strpos($alias, 'attr_index_')) {
             $tableInfo['tableName'] = $this->getTable('adjnav/catalog_product_index_configurable');
             $tableInfo['joinCondition'] = str_replace('e.entity_id', 'l.' . $versionHelper->getProductIdChildColumn(), $tableInfo['joinCondition']);
             $attributeJoins[$alias] = $tableInfo;
             unset($configurableFrom[$alias]);
         }
     }
     if (count($attributeJoins)) {
         $configurableSelect->setPart(Zend_Db_Select::FROM, $configurableFrom);
     }
     $configurableSelect->join(array('l' => $this->getTable($versionHelper->getProductRelationTable())), 'e.entity_id = l.parent_id', array());
     $configurableFrom = $configurableSelect->getPart(Zend_Db_Select::FROM);
     foreach ($attributeJoins as $alias => $tableInfo) {
         $configurableFrom[$alias] = $tableInfo;
     }
     $configurableSelect->setPart(Zend_Db_Select::FROM, $configurableFrom);
     $select->where('e.type_id != ?', Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE);
     $fields = array('count' => 'COUNT(DISTINCT(e.entity_id))', 'index.value', 'type_id' => '("configurable")');
     $configurableSelect->columns($fields)->join(array('index' => $this->getTable('adjnav/catalog_product_index_configurable')), 'index.entity_id = l.' . $versionHelper->getProductIdChildColumn(), array())->where('index.store_id = ?', $this->getStoreId())->where('index.attribute_id = ?', $attribute->getId())->where('e.type_id = ?', Mage_Catalog_Model_Product_Type::TYPE_CONFIGURABLE)->group('index.value');
     return $configurableSelect;
 }

 /**
  * Add access restriction filters to allow load only by granted user.
  *
  * @param Varien_Db_Select $select
  * @param int $accessLevel
  * @param int $userId
  * @return Varien_Db_Select
  */
 protected function _addAccessRestrictionsToSelect($select, $accessLevel, $userId)
 {
     $conditions = array();
     $conditions[] = $this->_getReadAdapter()->quoteInto('user_id = ?', (int) $userId);
     if (!empty($accessLevel)) {
         if (!is_array($accessLevel)) {
             $accessLevel = array($accessLevel);
         }
         $conditions[] = $this->_getReadAdapter()->quoteInto('access_level IN (?)', $accessLevel);
     } else {
         $conditions[] = 'access_level IS NULL';
     }
     $select->where(implode(' OR ', $conditions));
     return $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)
 {
     $select->join(array('ciss' => $this->getMainTable()), "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;
 }

 /**
  * Add redirect_id value in where clause
  *
  * @param Varien_Db_Select $select
  * @return Varien_Db_Select
  */
 protected function _addRedirectIdWhere(Varien_Db_Select $select)
 {
     return $select->where('r.redirect_id = ?', $this->_redirectId);
 }

 /**
  * @param Varien_Event_Observer $observer
  * @return $this
  */
 public function loadAttributeData($observer)
 {
     $attribute = $observer->getAttribute();
     $attribute_id = (int) $attribute->getAttributeId();
     $connection = Mage::getSingleton('core/resource')->getConnection('read');
     $table = Mage::getSingleton('core/resource')->getTableName('ecommerceteam_sln_attribute_data');
     $select = new Varien_Db_Select($connection);
     $select->from($table, array('group_id', 'frontend_type', 'comment', 'options_limit'));
     $select->where('attribute_id = ?', $attribute_id);
     $data = $connection->fetchRow($select);
     if ($data && is_array($data) && !empty($data)) {
         $attribute->addData($data);
     }
     return $this;
 }

 public function dbSelectModifier(Varien_Db_Select $dbSelect)
 {
     $dbSelect->join(array('blp' => Mage::getResourceModel('M2ePro/Buy_Listing_Product')->getMainTable()), '`lp`.`id` = `blp`.`listing_product_id`', array());
     $dbSelect->where('`blp`.`is_variation_product` = ' . self::IS_VARIATION_PRODUCT_NO . ' OR (' . '`blp`.`is_variation_product` = ' . self::IS_VARIATION_PRODUCT_YES . ' AND `blp`.`is_variation_matched` = ' . self::IS_VARIATION_MATCHED_YES . ')');
 }

 /**
  * Prepare query filters
  * 
  * @param Varien_Db_Select $select
  */
 protected function _prepareFilters(Varien_Db_Select $select)
 {
     $adapter = $this->getReadAdapter();
     $select->where('`report`.`campaign_id` = ?', $this->getParam(self::CAMPAIGN));
     $variation = $this->getParam(self::VARIATION);
     if ($variation === false) {
         $select->where('`report`.`variation_id` = -1');
     } else {
         if ($variation === true) {
             $select->where('`report`.`variation_id` >= 0', $variation);
         } else {
             if ($variation) {
                 $select->where('`report`.`variation_id` IN(?)', $variation);
             }
         }
     }
     $range = $this->getParam('date_range');
     if (is_array($range)) {
         if (!empty($range[0])) {
             $select->where('`report`.`date` >= ?', $range[0]);
         }
         if (!empty($range[1])) {
             $select->where('`report`.`date` <= ?', $range[1]);
         }
     }
 }

 /**
  * fastIndexer method and only used in _getProducts
  *
  * @param Varien_Db_Select $select
  * @param int              $storeId
  *
  * @return $this
  */
 private function _excludeDisabledCategories(Varien_Db_Select $select, $storeId)
 {
     if (false === $this->_getHelper()->excludeDisabledCategories($storeId)) {
         return $this;
     }
     $attributeCode = 'is_active';
     if (!isset($this->_categoryAttributes[$attributeCode])) {
         $attribute = $this->getCategoryModel()->getResource()->getAttribute($attributeCode);
         $this->_categoryAttributes[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'is_static' => $attribute->isStatic());
         unset($attribute);
     }
     $tableAlias = 'tjIsActive';
     $tAttrId = (int) $this->_categoryAttributes[$attributeCode]['attribute_id'];
     $disableValue = 0;
     // <@todo remove duplicate code>
     $t1 = 't1' . $tableAlias;
     $t2 = 't2' . $tableAlias;
     $mainTable = $this->getTable('catalog/category_product');
     $valueExpr = $this->_getReadAdapter()->getCheckSql('IFNULL(' . $t2 . '.value_id,0) > 0', $t2 . '.value', $t1 . '.value');
     $select->join(array($t1 => $this->_categoryAttributes[$attributeCode]['table']), $mainTable . '.category_id = ' . $t1 . '.entity_id AND ' . $t1 . '.store_id = 0 AND ' . $t1 . '.attribute_id = ' . $tAttrId, array())->joinLeft(array($t2 => $this->_categoryAttributes[$attributeCode]['table']), $t1 . '.entity_id = ' . $t2 . '.entity_id AND ' . $t1 . '.attribute_id = ' . $t2 . '.attribute_id AND ' . $t2 . '.store_id = ' . $storeId, array());
     $select->where($valueExpr . ' != ' . $disableValue);
     // </@todo remove duplicate code>
     return $this;
 }

 /**
  * @param Varien_DB_select
  * @return Varien_DB_select
  */
 public function addManufacturerFilter(Varien_Db_Select $select)
 {
     if ($this->isModuleEnabled('Aitoc_Aitmanufacturers') && Mage::helper('aitmanufacturers')->canUseLayeredNavigation(Mage::registry('shopby_attribute'), true)) {
         $code = $this->getParam('shopby_attribute');
         if (!empty($code)) {
             $attributeId = Mage::getModel('aitmanufacturers/config')->getAttributeId($code);
             if (!$this->_isAttributeSearchable($attributeId)) {
                 $session = Mage::getSingleton('core/session');
                 $manufactureId = $session->getAitocManufacturersCurrentManufacturerId();
                 $productIds = Mage::getModel('aitmanufacturers/aitmanufacturers')->getProductsByManufacturer($manufactureId, Mage::app()->getStore()->getId(), $attributeId);
                 $select->where('e.entity_id IN (?)', $productIds);
             }
         }
     }
     return $select;
 }