PHP Varien_Db_Adapter_Interface::select方法代码示例

 /**
  * Gets attribute options from database
  *
  * @param \Mage_Eav_Model_Entity_Attribute $attribute
  *
  * @return array
  */
 protected function getOptions($attribute)
 {
     $select = $this->readConnection->select()->from(array('o' => \Mage::getSingleton('core/resource')->getTableName('eav_attribute_option')))->join(array('ov' => \Mage::getSingleton('core/resource')->getTableName('eav_attribute_option_value')), 'o.option_id = ov.option_id')->where('o.attribute_id = ?', $attribute->getId())->where('ov.store_id = 0')->order('ov.option_id');
     $query = $select->query();
     $values = array();
     foreach ($query->fetchAll() as $row) {
         $values[] = $row['value'];
     }
     return array('values' => $values);
 }

 /**
  * @param $data
  */
 protected function _processTemplates(&$data)
 {
     $config = $this->_adapter->getConfig();
     $select = $this->_adapter->select();
     $select->from('information_schema.tables', 'AUTO_INCREMENT')->where('table_schema = ?', $config['dbname'])->where('table_name = ?', $this->_adapter->getTableName('customer_entity'));
     $nextId = $this->_adapter->fetchOne($select);
     foreach ($data['account'] as &$field) {
         $field = str_replace('{id}', $nextId, $field);
     }
     foreach ($data['address'] as &$address) {
         foreach ($address as &$field) {
             $field = str_replace('{id}', $nextId, $field);
         }
     }
 }

 /**
  * @dataProvider insertDataProvider
  */
 public function testInsertForce($data)
 {
     $this->assertEquals(1, $this->_connection->insertForce($this->_tableName, $data));
     $select = $this->_connection->select()->from($this->_tableName);
     $result = $this->_connection->fetchRow($select);
     $this->assertEquals($data, $result);
 }

 /**
  * Copy data from temporary to main redirects table
  *
  * @return void
  */
 protected function _copyRedirectsToMainTable()
 {
     $select = $this->_connection->select();
     $select->from(array('t' => self::TMP_TABLE_NAME), array('*'));
     $query = $select->insertFromSelect($this->_resource->getTableName('enterprise_urlrewrite/redirect'), array('identifier', 'target_path', 'store_id', 'category_id', 'product_id'), Varien_Db_Adapter_Interface::INSERT_ON_DUPLICATE);
     $this->_connection->query($query);
 }

 /**
  * Prepare data for group website relation
  */
 protected function _prepareGroupWebsite($timestamp)
 {
     $this->_connection->delete($this->_resource->getTable('catalogrule/rule_group_website'), array());
     $select = $this->_connection->select()->distinct(true)->from($this->_resource->getTable('catalogrule/rule_product'), array('rule_id', 'customer_group_id', 'website_id'))->where(new Zend_Db_Expr("{$timestamp} >= from_time"))->where($this->_connection->getCheckSql(new Zend_Db_Expr('to_time = 0'), new Zend_Db_Expr(1), new Zend_Db_Expr("{$timestamp} <= to_time")));
     $query = $select->insertFromSelect($this->_resource->getTable('catalogrule/rule_group_website'));
     $this->_connection->query($query);
 }

 /**
  * Get trigger action body for event
  *
  * @param int $entityEventId
  * @return string
  */
 public function getTriggerBody($entityEventId)
 {
     $select = $this->_connection->select()->reset()->from(array(), array('status' => new Zend_Db_Expr('?')))->joinInner(array('me' => $this->_resource->getTableName('enterprise_mview/metadata_event')), new Zend_Db_Expr('mm.metadata_id = me.metadata_id'), array())->where('mview_event_id = ?', $entityEventId);
     $update = $this->_connection->updateFromSelect($select, array('mm' => $this->_resource->getTableName('enterprise_mview/metadata')));
     $update = $this->_connection->quoteInto($update, Enterprise_Mview_Model_Metadata::STATUS_INVALID, null, 1);
     return $update . ';';
 }

 /**
  * Retrieve attribute codes using for flat
  *
  * @return array
  */
 public function getAttributeCodes()
 {
     if ($this->_attributeCodes === null) {
         $this->_attributeCodes = array();
         $systemAttributes = array();
         $attributeNodes = Mage::getConfig()->getNode(self::XML_NODE_ATTRIBUTE_NODES)->children();
         foreach ($attributeNodes as $node) {
             $attributes = Mage::getConfig()->getNode((string) $node)->asArray();
             $attributes = array_keys($attributes);
             $systemAttributes = array_unique(array_merge($attributes, $systemAttributes));
         }
         $bind = array('backend_type' => Mage_Eav_Model_Entity_Attribute_Abstract::TYPE_STATIC, 'entity_type_id' => $this->getEntityTypeId());
         $select = $this->_connection->select()->from(array('main_table' => $this->getTable('eav/attribute')))->join(array('additional_table' => $this->getTable('catalog/eav_attribute')), 'additional_table.attribute_id = main_table.attribute_id')->where('main_table.entity_type_id = :entity_type_id');
         $whereCondition = array('main_table.backend_type = :backend_type', $this->_connection->quoteInto('additional_table.is_used_for_promo_rules = ?', 1), $this->_connection->quoteInto('additional_table.used_in_product_listing = ?', 1), $this->_connection->quoteInto('additional_table.used_for_sort_by = ?', 1), $this->_connection->quoteInto('main_table.attribute_code IN(?)', $systemAttributes));
         if ($this->getFlatHelper()->isAddFilterableAttributes()) {
             $whereCondition[] = $this->_connection->quoteInto('additional_table.is_filterable > ?', 0);
         }
         $select->where(implode(' OR ', $whereCondition));
         $attributesData = $this->_connection->fetchAll($select, $bind);
         Mage::getSingleton('eav/config')->importAttributesData($this->getEntityType(), $attributesData);
         foreach ($attributesData as $data) {
             $this->_attributeCodes[$data['attribute_id']] = $data['attribute_code'];
         }
         unset($attributesData);
     }
     return $this->_attributeCodes;
 }

 /**
  * Get current DB version
  *
  * @return int
  */
 protected function _getCurrentVersionId()
 {
     if (empty($this->_currentVersionId)) {
         // zend select query permanently requires FROM statement, so executing raw query
         $this->_currentVersionId = $this->_connection->query($this->_connection->select()->from($this->_metadata->getChangelogName(), array())->columns(array('max' => 'MAX(version_id)')))->fetchColumn();
     }
     return $this->_currentVersionId;
 }

 /**
  * @param Mage_Customer_Model_Customer $customer
  */
 protected function _moveAttributesData($customer)
 {
     $attributes = array('po_limit' => $this->_poLimitId, 'po_credit' => $this->_poCreditId);
     foreach ($attributes as $attributeCode => $attributeId) {
         try {
             $select = $this->_db->select()->from($customer->getResource()->getTable('customer_entity_int'), array('value'))->where('attribute_id = ?', $attributeId)->where('entity_id = ?', $customer->getId());
             $value = $this->_db->fetchOne($select);
             if ((int) $value > 0) {
                 $this->_db->insert($customer->getResource()->getTable('customer_entity_decimal'), array('entity_type_id' => $customer->getEntityTypeId(), 'attribute_id' => $attributeId, 'entity_id' => $customer->getId(), 'value' => (int) $value));
             }
         } catch (Exception $e) {
         }
         try {
             $this->_db->delete($customer->getResource()->getTable('customer_entity_int'), 'entity_type_id = ' . $customer->getEntityTypeId() . ' AND attribute_id = ' . $attributeId . ' AND ' . 'entity_id = ' . $customer->getId());
         } catch (Exception $e) {
         }
     }
 }

 /**
  * Returns last version_id from changelog table
  *
  * @return int
  */
 protected function _selectLastVersionId()
 {
     $changelogName = $this->_metadata->getChangelogName();
     if (empty($changelogName)) {
         return 0;
     }
     $select = $this->_connection->select()->from(array('changelog' => $changelogName), array('version_id'))->order('version_id DESC')->limit(1);
     return (int) $this->_connection->fetchOne($select);
 }

 /**
  * Add products to changelog by conditions
  *
  * @param int $storeId
  * @param string $attrCode
  * @param Zend_Db_Expr $attrConditionValue
  */
 protected function _refreshSpecialPriceByStore($storeId, $attrCode, $attrConditionValue)
 {
     $attribute = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $attrCode);
     $attributeId = $attribute->getAttributeId();
     $select = $this->_connection->select()->from($this->_getTable(array('catalog/product', 'datetime')), array('entity_id'))->where('attribute_id = ?', $attributeId)->where('store_id = ?', $storeId)->where('value = ?', $attrConditionValue);
     $client = $this->_getClient(Mage::helper('enterprise_index')->getIndexerConfigValue('catalog_product_price', 'index_table'));
     $query = $select->insertFromSelect($client->getMetadata()->changelog_name, array('entity_id'), false);
     $this->_connection->query($query);
 }

 /**
  * Assert that session data writes to DB in base64 encoding
  */
 public function testWriteEncoded()
 {
     $data = serialize($this->_sessionData[self::SESSION_NEW]);
     $this->_model->write(self::SESSION_ID, $data);
     $select = $this->_connection->select()->from($this->_sessionTable)->where(self::COLUMN_SESSION_ID . ' = :' . self::COLUMN_SESSION_ID);
     $bind = array(self::COLUMN_SESSION_ID => self::SESSION_ID);
     $session = $this->_connection->fetchRow($select, $bind);
     $this->assertEquals(self::SESSION_ID, $session[self::COLUMN_SESSION_ID]);
     $this->assertTrue(ctype_digit((string) $session[self::COLUMN_SESSION_EXPIRES]), 'Value of session expire field must have integer type');
     $this->assertEquals($data, base64_decode($session[self::COLUMN_SESSION_DATA]));
 }

 /**
  * Removes redirect rows which have no corresponding records in redirect table from index.
  *
  * @return Enterprise_UrlRewrite_Model_Index_Action_Url_Rewrite_Redirect_Refresh_Orphan
  * @throws Enterprise_Index_Model_Action_Exception
  */
 public function execute()
 {
     try {
         $select = $this->_connection->select()->from(array('ur' => $this->_getTable('enterprise_urlrewrite/url_rewrite')), '*')->joinInner(array('rr' => $this->_getTable('enterprise_urlrewrite/redirect_rewrite')), 'ur.url_rewrite_id = rr.url_rewrite_id')->joinLeft(array('redirect' => $this->_getTable('enterprise_urlrewrite/redirect')), 'redirect.redirect_id = rr.redirect_id')->where('ur.entity_type = ?', Enterprise_UrlRewrite_Model_Redirect::URL_REWRITE_ENTITY_TYPE)->where('redirect.redirect_id IS NULL');
         $this->_connection->query($this->_connection->deleteFromSelect($select, 'ur'));
     } catch (Exception $e) {
         $this->_metadata->setInvalidStatus()->save();
         throw new Enterprise_Index_Model_Action_Exception($e->getMessage(), $e->getCode(), $e);
     }
     return $this;
 }

 /**
  * Return last version ID
  *
  * @return string
  */
 protected function _getLastVersionId()
 {
     $changelogName = $this->_metadata->getChangelogName();
     if (empty($changelogName)) {
         return 0;
     }
     if (!$this->_lastVersionId) {
         $select = $this->_connection->select()->from($changelogName, array('version_id'))->order('version_id DESC')->limit(1);
         $this->_lastVersionId = (int) $this->_connection->fetchOne($select);
     }
     return $this->_lastVersionId;
 }

 /**
  * Get select for all products
  *
  * @param $store
  * @return Varien_Db_Select
  */
 protected function _getAllProducts(Mage_Core_Model_Store $store)
 {
     if (!isset($this->_allProductsSelect[$store->getId()])) {
         /** @var $eavConfig Mage_Eav_Model_Config */
         $eavConfig = $this->_factory->getSingleton('eav/config');
         $statusAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'status')->getId();
         $visibilityAttributeId = $eavConfig->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'visibility')->getId();
         $select = $this->_connection->select()->from(array('cp' => $this->_getTable('catalog/product')), array())->joinInner(array('cpw' => $this->_getTable('catalog/product_website')), 'cpw.product_id = cp.entity_id', array())->joinInner(array('cpsd' => $this->_getTable(array('catalog/product', 'int'))), 'cpsd.entity_id = cp.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = ' . $statusAttributeId, array())->joinLeft(array('cpss' => $this->_getTable(array('catalog/product', 'int'))), 'cpss.entity_id = cp.entity_id AND cpss.attribute_id = cpsd.attribute_id' . ' AND cpss.store_id = ' . $store->getId(), array())->joinInner(array('cpvd' => $this->_getTable(array('catalog/product', 'int'))), 'cpvd.entity_id = cp.entity_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, array())->joinLeft(array('cpvs' => $this->_getTable(array('catalog/product', 'int'))), 'cpvs.entity_id = cp.entity_id AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' . $store->getId(), array())->joinLeft(array('ccp' => $this->_getTable('catalog/category_product')), 'ccp.product_id = cp.entity_id', array())->where('cpw.website_id = ?', $store->getWebsiteId())->where($this->_connection->getIfNullSql('cpss.value', 'cpsd.value') . ' = ?', Mage_Catalog_Model_Product_Status::STATUS_ENABLED)->where($this->_connection->getIfNullSql('cpvs.value', 'cpvd.value') . ' IN (?)', array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_SEARCH))->group('cp.entity_id')->columns(array('category_id' => new Zend_Db_Expr($store->getRootCategoryId()), 'product_id' => 'cp.entity_id', 'position' => new Zend_Db_Expr($this->_connection->getCheckSql('ccp.product_id IS NOT NULL', 'ccp.position', '0')), 'is_parent' => new Zend_Db_Expr($this->_connection->getCheckSql('ccp.product_id IS NOT NULL', '0', '1')), 'store_id' => new Zend_Db_Expr($store->getId()), 'visibility' => new Zend_Db_Expr($this->_connection->getIfNullSql('cpvs.value', 'cpvd.value'))));
         $this->_allProductsSelect[$store->getId()] = $select;
     }
     return $this->_allProductsSelect[$store->getId()];
 }