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