PHP Varien_Db_Select::join方法代码示例

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