PHP Varien_Db_Select::from方法代码示例

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

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

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

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

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

 /**
  * (non-PHPdoc)
  * @see Zend_Db_Select::from()
  */
 public function from($name, $cols = null, $schema = null)
 {
     if (is_array($name)) {
         foreach ($name as $correlationName => $tableName) {
             $this->_tableAlias = $correlationName;
             break;
         }
     } else {
         $name = array($this->_tableAlias => $name);
     }
     return parent::from($name, $cols, $schema);
 }

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

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

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

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

 /**
  * @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 getStorageSize()
 {
     if ($this->_storageSize === null) {
         $adapter = $this->_getResource()->getReadConnection();
         $config = $adapter->getConfig();
         $select = new Varien_Db_Select($adapter);
         $select->from('information_schema.TABLES', '')->columns('(data_length + index_length)')->where('table_schema = ?', $config['dbname'])->where('table_name = ?', $this->_getResource()->getTable('core/cache'));
         $this->_storageSize = $adapter->fetchOne($select);
     }
     return $this->_storageSize / 1024 / 1024;
 }