/** * 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(); }
/** * Batched insert of specified select * * @param Varien_Db_Select $select * @param string $table * @param array $fields * @param bool $mode * @param int $step * @return int */ public function insertBatchFromSelect(Varien_Db_Select $select, $table, array $fields = array(), $mode = false, $step = 10000) { $limitOffset = 0; $totalAffectedRows = 0; do { $select->limit($step, $limitOffset); $result = $this->query($this->insertFromSelect($select, $table, $fields, $mode)); $affectedRows = $result->rowCount(); $totalAffectedRows += $affectedRows; $limitOffset += $step; } while ($affectedRows > 0); return $totalAffectedRows; }
/** * Clean logs * * @return Mage_Log_Model_Cron */ public function streamClean() { $adapter = Mage::getSingleton('core/resource')->getConnection('write'); $activityTableName = Mage::getSingleton('core/resource')->getTableName('activitystream/activity'); $select = new Varien_Db_Select($adapter); $select->from($activityTableName)->reset(Zend_Db_Select::COLUMNS)->columns(array('id'))->order('id DESC')->limit(100); try { $liveActivityList = $adapter->fetchCol($select); $adapter->delete($activityTableName, array('id NOT IN (?)' => $liveActivityList)); } catch (Exception $e) { Mage::logException($e); } return $this; }
/** * Remove price records from where query * * @param Varien_Db_Select $select * @param string $priceExpression * @return Varien_Db_Select */ public function _removePriceFromSelect($select, $priceExpression) { $oldWhere = $select->getPart(Varien_Db_Select::WHERE); $newWhere = array(); foreach ($oldWhere as $cond) { if (false === strpos($cond, $priceExpression)) { $newWhere[] = $cond; } } if ($newWhere && substr($newWhere[0], 0, 3) == 'AND') { $newWhere[0] = substr($newWhere[0], 3); } $select->setPart(Varien_Db_Select::WHERE, $newWhere); return $select; }
/** * Class constructor * * @param Zend_Db_Adapter_Abstract $adapter */ public function __construct(Zend_Db_Adapter_Abstract $adapter) { parent::__construct($adapter); if (!in_array(self::STRAIGHT_JOIN_ON, self::$_joinTypes)) { self::$_joinTypes[] = self::STRAIGHT_JOIN_ON; self::$_partsInit = array(self::STRAIGHT_JOIN => false) + self::$_partsInit; } }
/** * @param Varien_Db_Select $select * @param string $attributeCode * @return $this */ public function joinAttribute($select, $attributeCode) { /* @var $core Mana_Core_Helper_Data */ $core = Mage::helper(strtolower('Mana_Core')); /* @var $attribute Mage_Catalog_Model_Resource_Eav_Attribute */ $attribute = $core->collectionFind($this->getAttributes(), 'attribute_code', $attributeCode); /* @var $db Varien_Db_Adapter_Pdo_Mysql */ $db = $select->getAdapter(); $alias = 'meav_' . $attributeCode; $storeAlias = 's' . $alias; $from = $select->getPart(Varien_Db_Select::FROM); if (!isset($from[$alias])) { $select->joinLeft(array($alias => $attribute->getBackendTable()), implode(' AND ', array("`{$alias}`.`entity_id` = `e`.`entity_id`", $db->quoteInto("`{$alias}`.`attribute_id` = ?", $attribute->getId()), "`{$alias}`.`store_id` = 0")), null); $select->joinLeft(array($storeAlias => $attribute->getBackendTable()), implode(' AND ', array("`{$storeAlias}`.`entity_id` = `e`.`entity_id`", $db->quoteInto("`{$storeAlias}`.`attribute_id` = ?", $attribute->getId()), $db->quoteInto("`{$storeAlias}`.`store_id` = ?", Mage::app()->getStore()->getId()))), null); } 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; }
/** * Retrieves a collection of all new attribute sets * * @return Mage_Eav_Model_Resource_Entity_Attribute_Set_Collection */ public function newAttributeSetsCollection() { /* @var Mage_Core_Model_Resource $resource */ $resource = Mage::getSingleton('core/resource'); /* @var Mage_Catalog_Model_Resource_Product $productResource */ $productResource = Mage::getModel('catalog/product')->getResource(); /* @var Mage_Eav_Model_Resource_Entity_Attribute_Set_Collection $sets */ $sets = Mage::getResourceModel('eav/entity_attribute_set_collection'); $sets->setEntityTypeFilter($productResource->getTypeId()); /* @var Listrak_Remarketing_Model_Mysql4_Product_Attribute_Set_Map $setResource */ $setResource = Mage::getResourceModel('listrak/product_attribute_set_map'); // the sets already in the table $model = new Varien_Db_Select($setResource->getReadConnection()); $model->from(array('current' => $resource->getTableName('listrak/product_attribute_set_map')), array("*"))->where('main_table.attribute_set_id = current.attribute_set_id'); // new product attribute sets $sets->getSelect()->where('NOT EXISTS (' . $model . ')'); return $sets; }
public function testWhere() { $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'5'")); $select->from('test')->where('field = ?', 5); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (field = '5')", $select->assemble()); $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "''")); $select->from('test')->where('field = ?'); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (field = '')", $select->assemble()); $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'%?%'")); $select->from('test')->where('field LIKE ?', '%value?%'); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (field LIKE '%?%')", $select->assemble()); $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(0)); $select->from('test')->where("field LIKE '%value?%'", null, Varien_Db_Select::TYPE_CONDITION); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (field LIKE '%value?%')", $select->assemble()); $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'1', '2', '4', '8'")); $select->from('test')->where("id IN (?)", array(1, 2, 4, 8)); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (id IN ('1', '2', '4', '8'))", $select->assemble()); }
/** * Enter description here ... * @param Varien_Db_Select $select * @param string $column */ public function selectColumn($select, $column) { list($expr, $alias) = explode(' AS ', $column); foreach ($select->getPart(Zend_Db_Select::COLUMNS) as $columnInfo) { if ($columnInfo[2] == $alias) { return $this; } } $select->columns($column); return $this; }
/** 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; }
/** * @param array|string $attributes * @param Varien_Db_Select $select * @param string $mainCorrelation * @param string $mainIdColumn * @return $this */ public function includeProductAttribute($attributes, $select, $mainCorrelation = 'main_table', $mainIdColumn = null, $joinTableAlias = null, $resourceName = null) { $attributes = $this->_loadAttributes($attributes); $mainIdColumn = $mainIdColumn ?: 'entity_id'; $joinTableAlias = $joinTableAlias ?: 'product_%s_table'; foreach ($attributes as $attributeCode) { /** @var Mage_Eav_Model_Entity_Attribute $attribute */ if (is_string($attributeCode)) { $attribute = $this->_getCachedAttribute($attributeCode); } else { $attribute = $attributeCode; } $attributeCode = $attribute->getAttributeCode(); if ($attribute->getId()) { if ($attribute->getBackendType() == 'static') { $select->columns($attribute->getAttributeCode()); } else { $joinTable = sprintf($joinTableAlias, $attributeCode); $select->joinLeft(array($joinTable => $attribute->getBackendTable()), sprintf('`%s`.%s = `%s`.entity_id AND `%s`.attribute_id = %s', $mainCorrelation, $mainIdColumn, $joinTable, $joinTable, $attribute->getAttributeId()), array($attributeCode => new Zend_Db_Expr(sprintf('`%s`.value', $joinTable)))); } } } return $this; }
/** * 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; }
/** * Adds Columns prepared for union * * @param Varien_Db_Select $select * @param string $table * @param string $type * @return Varien_Db_Select */ protected function _addLoadAttributesSelectFields($select, $table, $type) { $select->columns(Mage::getResourceHelper('catalog')->attributeSelectFields('attr_table', $type)); return $select; }
/** * Prepare additional price expression sql part * * @param Varien_Db_Select $select * @return Mage_Catalog_Model_Resource_Product_Collection */ protected function _preparePriceExpressionParameters($select) { // prepare response object for event $response = new Varien_Object(); $response->setAdditionalCalculations(array()); $tableAliases = array_keys($select->getPart(Zend_Db_Select::FROM)); if (in_array(self::INDEX_TABLE_ALIAS, $tableAliases)) { $table = self::INDEX_TABLE_ALIAS; } else { $table = reset($tableAliases); } // prepare event arguments $eventArgs = array('select' => $select, 'table' => $table, 'store_id' => $this->getStoreId(), 'response_object' => $response); Mage::dispatchEvent('catalog_prepare_price_select', $eventArgs); $additional = join('', $response->getAdditionalCalculations()); $this->_priceExpression = $table . '.min_price'; $this->_additionalPriceExpression = $additional; $this->_catalogPreparePriceSelect = clone $select; return $this; }
/** * Join event table to select object * * @param Varien_Db_Select $select * @return Varien_Db_Select */ protected function _joinEventData($select) { $joinCondition = sprintf('e.%1$s = %2$s.%1$s', $this->getIdFieldName(), $this->getMainTable()); $select->joinLeft(array('e' => $this->_eventTable), $joinCondition, '*'); return $select; }