/** * 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 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; }
/** * @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 )'); }
/** * Add attribute filter to select * * @param Varien_Db_Select $select * @param string $attributeCode * @param string $table the main table name or alias * @param string $field entity_id field name * @param int $store * @param int|string|array $value the filter value * @return Mage_CatalogIndex_Model_Resource_Data_Abstract */ protected function _addAttributeFilter(Varien_Db_Select $select, $attributeCode, $table, $field, $store, $value) { $adapter = $this->_getReadAdapter(); $attribute = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $attributeCode); /* @var $attribute Mage_Catalog_Model_Resource_Eav_Attribute */ $attributeTable = $attribute->getBackend()->getTable(); if ($attribute->getBackendType() == 'static') { $tableAlias = sprintf('t_%s', $attribute->getAttributeCode()); $joinCond = join(' AND ', array(sprintf('`%s`.`%s`=`%s`.`entity_id`', $table, $field, $tableAlias))); $select->join(array($tableAlias => $attributeTable), $joinCond, array())->where(sprintf('%s.%s IN(?)', $tableAlias, $attribute->getAttributeCode()), $value); } elseif ($attribute->isScopeGlobal()) { $tableAlias = sprintf('t_%s', $attribute->getAttributeCode()); $joinCond = join(' AND ', array(sprintf('`%s`.`%s`=`%s`.`entity_id`', $table, $field, $tableAlias), $adapter->quoteInto(sprintf('`%s`.`attribute_id`=?', $tableAlias), $attribute->getAttributeId()), $adapter->quoteInto(sprintf('`%s`.`store_id`=?', $tableAlias), 0))); $select->join(array($tableAlias => $attributeTable), $joinCond, array())->where(sprintf('%s.value IN(?)', $tableAlias), $value); } else { $tableGlobal = sprintf('t_global_%s', $attribute->getAttributeCode()); $tableStore = sprintf('t_store_%s', $attribute->getAttributeCode()); $joinCondGlobal = join(' AND ', array(sprintf('`%s`.`%s`=`%s`.`entity_id`', $table, $field, $tableGlobal), $adapter->quoteInto(sprintf('`%s`.`attribute_id`=?', $tableGlobal), $attribute->getAttributeId()), $adapter->quoteInto(sprintf('`%s`.`store_id`=?', $tableGlobal), 0))); $joinCondStore = join(' AND ', array(sprintf('`%s`.`entity_id`=`%s`.`entity_id`', $tableGlobal, $tableStore), sprintf('`%s`.`attribute_id`=`%s`.`attribute_id`', $tableGlobal, $tableStore), $adapter->quoteInto(sprintf('`%s`.`store_id`=?', $tableStore), $store))); $whereCond = sprintf('IF(`%s`.`value_id`>0, `%s`.`value`, `%s`.`value`) IN(?)', $tableStore, $tableStore, $tableGlobal); $select->join(array($tableGlobal => $attributeTable), $joinCondGlobal, array())->joinLeft(array($tableStore => $attributeTable), $joinCondStore, array())->where($whereCond, $value); } 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) { $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 join for catalog/product_website table * * Joined table has alias pw * * @param Varien_Db_Select $select the select object * @param string|Zend_Db_Expr $website the limitation of website_id * @param string|Zend_Db_Expr $product the limitation of product_id * @return Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Abstract */ protected function _addProductWebsiteJoinToSelect($select, $website, $product) { $select->join(array('pw' => $this->getTable('catalog/product_website')), "pw.product_id = {$product} AND pw.website_id = {$website}", array()); 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 . ')'); }
/** * @param Varien_Db_Select $select * @param $conditionString * @return mixed|null */ protected function _replaceProductTableAlias($select, $conditionString, $oldMainFromClause) { if (is_null($conditionString)) { return null; } $adapter = $this->_getReadAdapter(); if (strpos($conditionString, 'e.') === false) { return $conditionString; } $fromPart = $select->getPart(Zend_Db_Select::FROM); if (!isset($fromPart['main_table'])) { $select->join(array('main_table' => $oldMainFromClause['tableName']), "`e`.`entity_id` = `main_table`.`entity_id`", null); } $oldAlias = array('e' . '.', $adapter->quoteIdentifier('e') . '.'); $newAlias = array('main_table' . '.', $adapter->quoteIdentifier('main_table') . '.'); return $this->_replaceAliasExpr($oldAlias, $newAlias, $conditionString); }
/** * 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; }