PHP Varien_Db_Select::joinLeft方法代码示例

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

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

 /**
  * Add attribute join condition to select and return Zend_Db_Expr
  * attribute value definition
  *
  * If $condition is not empty apply limitation for select
  *
  * @param Varien_Db_Select $select
  * @param string $attrCode              the attribute code
  * @param string|Zend_Db_Expr $entity   the entity field or expression for condition
  * @param string|Zend_Db_Expr $store    the store field or expression for condition
  * @param Zend_Db_Expr $condition       the limitation condition
  * @param bool $required                if required or has condition used INNER join, else - LEFT
  * @return Zend_Db_Expr                 the attribute value expression
  */
 protected function _addAttributeToSelect($select, $attrCode, $entity, $store, $condition = null, $required = false)
 {
     $attribute = $this->_getAttribute($attrCode);
     $attributeId = $attribute->getAttributeId();
     $attributeTable = $attribute->getBackend()->getTable();
     $joinType = !is_null($condition) || $required ? 'join' : 'joinLeft';
     if ($attribute->isScopeGlobal()) {
         $alias = 'ta_' . $attrCode;
         $select->{$joinType}(array($alias => $attributeTable), "{$alias}.entity_id = {$entity} AND {$alias}.attribute_id = {$attributeId}" . " AND {$alias}.store_id = 0", array());
         $expression = new Zend_Db_Expr("{$alias}.value");
     } else {
         $dAlias = 'tad_' . $attrCode;
         $sAlias = 'tas_' . $attrCode;
         $select->{$joinType}(array($dAlias => $attributeTable), "{$dAlias}.entity_id = {$entity} AND {$dAlias}.attribute_id = {$attributeId}" . " AND {$dAlias}.store_id = 0", array());
         $select->joinLeft(array($sAlias => $attributeTable), "{$sAlias}.entity_id = {$entity} AND {$sAlias}.attribute_id = {$attributeId}" . " AND {$sAlias}.store_id = {$store}", array());
         $expression = new Zend_Db_Expr("IF({$sAlias}.value_id > 0, {$sAlias}.value, {$dAlias}.value)");
     }
     if (!is_null($condition)) {
         $select->where("{$expression}{$condition}");
     }
     return $expression;
 }

 /**
  * @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 stock status to prepare index select
  *
  * @param Varien_Db_Select $select
  * @param Mage_Core_Model_Website $website
  * @return Mage_CatalogInventory_Model_Resource_Stock_Status
  */
 public function addStockStatusToSelect(Varien_Db_Select $select, Mage_Core_Model_Website $website)
 {
     $websiteId = $website->getId();
     $select->joinLeft(array('stock_status' => $this->getMainTable()), 'e.entity_id = stock_status.product_id AND stock_status.website_id=' . $websiteId, array('salable' => 'stock_status.stock_status'));
     return $this;
 }

 /**
  * Necessary for EE > 1.12
  *
  * @param Varien_Db_Select $select
  * @param $storeId
  * @return $this
  */
 protected function _joinTableToSelect(Varien_Db_Select $select, $storeId)
 {
     $requestPath = $this->_getWriteAdapter()->getIfNullSql('url_rewrite.request_path', 'default_ur.request_path');
     $urlSuffix = Mage::helper('catalog/product')->getProductUrlSuffix($storeId);
     $urlSuffix = $urlSuffix ? '.' . $urlSuffix : '';
     $select->joinLeft(array('url_rewrite_product' => $this->getTable('enterprise_catalog/product')), 'url_rewrite_product.product_id = main_table.entity_id ' . 'AND url_rewrite_product.store_id = ' . (int) $storeId, array('url_rewrite_product.product_id' => 'url_rewrite_product.product_id'))->joinLeft(array('url_rewrite' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'url_rewrite_product.url_rewrite_id = url_rewrite.url_rewrite_id AND url_rewrite.is_system = 1', array(''))->joinLeft(array('default_urp' => $this->getTable('enterprise_catalog/product')), 'default_urp.product_id = main_table.entity_id AND default_urp.store_id = 0', array(''))->joinLeft(array('default_ur' => $this->getTable('enterprise_urlrewrite/url_rewrite')), 'default_ur.url_rewrite_id = default_urp.url_rewrite_id', array('url' => 'concat( ' . $requestPath . ',"' . $urlSuffix . '")'));
     return $this;
 }

 /**
  * Add attribute data to select
  *
  * @param Varien_Db_Select $select
  * @param string $attributeCode
  * @param Mage_Core_Model_Website $website
  * @return Mage_Bundle_Model_Mysql4_Price_Index
  */
 protected function _addAttributeDataToSelect(Varien_Db_Select $select, $attributeCode, Mage_Core_Model_Website $website)
 {
     $attribute = $this->_getAttribute($attributeCode);
     $store = $website->getDefaultStore();
     if ($attribute->isScopeGlobal()) {
         $table = 't_' . $attribute->getAttributeCode();
         $select->joinLeft(array($table => $attribute->getBackend()->getTable()), 'e.entity_id=' . $table . '.entity_id' . ' AND ' . $table . '.attribute_id=' . $attribute->getAttributeId() . ' AND ' . $table . '.store_id=0', array($attribute->getAttributeCode() => $table . '.value'));
     } else {
         $tableName = $attribute->getBackend()->getTable();
         $tableGlobal = 't1_' . $attribute->getAttributeCode();
         $tableStore = 't2_' . $attribute->getAttributeCode();
         $select->joinLeft(array($tableGlobal => $tableName), 'e.entity_id=' . $tableGlobal . '.entity_id' . ' AND ' . $tableGlobal . '.attribute_id=' . $attribute->getAttributeId() . ' AND ' . $tableGlobal . '.store_id=0', array($attribute->getAttributeCode() => 'IF(' . $tableStore . '.value_id > 0, ' . $tableStore . '.value, ' . $tableGlobal . '.value)'))->joinLeft(array($tableStore => $tableName), $tableGlobal . '.entity_id = ' . $tableStore . '.entity_id' . ' AND ' . $tableGlobal . '.attribute_id = ' . $tableStore . '.attribute_id' . ' AND ' . $tableStore . '.store_id=' . $store->getId(), array());
     }
     return $this;
 }

 /**
  * Join tax class
  * @param Varien_Db_Select $select
  * @param int $storeId
  * @param string $priceTable
  * @return Mage_Tax_Helper_Data
  */
 public function joinTaxClass($select, $storeId, $priceTable = 'main_table')
 {
     $taxClassAttribute = Mage::getModel('eav/entity_attribute')->loadByCode(Mage_Catalog_Model_Product::ENTITY, 'tax_class_id');
     $joinConditionD = implode(' AND ', array("tax_class_d.entity_id = {$priceTable}.entity_id", $select->getAdapter()->quoteInto('tax_class_d.attribute_id = ?', (int) $taxClassAttribute->getId()), 'tax_class_d.store_id = 0'));
     $joinConditionC = implode(' AND ', array("tax_class_c.entity_id = {$priceTable}.entity_id", $select->getAdapter()->quoteInto('tax_class_c.attribute_id = ?', (int) $taxClassAttribute->getId()), $select->getAdapter()->quoteInto('tax_class_c.store_id = ?', (int) $storeId)));
     $select->joinLeft(array('tax_class_d' => $taxClassAttribute->getBackend()->getTable()), $joinConditionD, array())->joinLeft(array('tax_class_c' => $taxClassAttribute->getBackend()->getTable()), $joinConditionC, array());
     return $this;
 }

 /**
  * Prepare url rewrite left join statement for given select instance and store_id parameter.
  *
  * @param Varien_Db_Select $select
  * @param int $storeId
  * @return Mage_Catalog_Helper_Product_Url_Rewrite_Interface
  */
 public function joinTableToSelect(Varien_Db_Select $select, $storeId)
 {
     $select->joinLeft(array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')), 'url_rewrite.product_id = main_table.entity_id AND url_rewrite.is_system = 1 AND ' . $this->_connection->quoteInto('url_rewrite.category_id IS NULL AND url_rewrite.store_id = ? AND ', (int) $storeId) . $this->_connection->prepareSqlCondition('url_rewrite.id_path', array('like' => 'product/%')), array('request_path' => 'url_rewrite.request_path'));
     return $this;
 }

 /**
  * @param Varien_Db_Select $select
  * @param string $tableAlias
  * @param string $tableName
  * @param string $joinCondition
  * @param int $count
  * @return $this
  */
 public function joinLeft($select, $tableAlias, $tableName, $joinCondition, $count)
 {
     for ($i = 0; $i < $count; $i++) {
         $select->joinLeft(array(str_replace('X', $i, $tableAlias) => $tableName), str_replace('X`', "{$i}`", $joinCondition), null);
     }
     return $this;
 }

 /**
  * Prepare url rewrite left join statement for given select instance and store_id parameter.
  *
  * @param Varien_Db_Select $select
  * @param int $storeId
  * @return Enterprise_Catalog_Helper_Product_UrlRewrite
  */
 public function joinTableToSelect(Varien_Db_Select $select, $storeId)
 {
     $requestPath = $this->_connection->getIfNullSql('url_rewrite.request_path', 'default_ur.request_path');
     $select->joinLeft(array('url_rewrite_product' => $this->_resource->getTableName('enterprise_catalog/product')), 'url_rewrite_product.product_id = main_table.entity_id ' . 'AND url_rewrite_product.store_id = ' . (int) $storeId, array('url_rewrite_product.product_id' => 'url_rewrite_product.product_id'))->joinLeft(array('url_rewrite' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'url_rewrite_product.url_rewrite_id = url_rewrite.url_rewrite_id AND url_rewrite.is_system = 1', array(''))->joinLeft(array('default_urp' => $this->_resource->getTableName('enterprise_catalog/product')), 'default_urp.product_id = main_table.entity_id AND default_urp.store_id = 0', array(''))->joinLeft(array('default_ur' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'default_ur.url_rewrite_id = default_urp.url_rewrite_id', array('request_path' => $requestPath));
     return $this;
 }

 /**
  * Join Tracker table for the given tracker id
  * 
  * return table alias
  * 
  * @param Varien_Db_Select $select
  * @param integer $trackerId
  * @param string $table
  * @return string
  */
 protected function _joinTracker(Varien_Db_Select $select, $trackerId, $table)
 {
     $alias = 'tracker_' . $trackerId;
     // already joined
     if (array_key_exists($alias, $select->getPart(Varien_Db_Select::FROM))) {
         return $alias;
     }
     $description = $this->getReadAdapter()->describeTable($this->getTable($table));
     $joins = array('campaign_id', 'variation_id', 'dimension_id', 'value_id', 'date');
     foreach ($joins as $join) {
         if (isset($description[$join])) {
             $cond[] = "`report`.`{$join}` = `{$alias}`.`{$join}`";
         }
     }
     $cond[] = "`{$alias}`.`tracker_id` = {$trackerId}";
     // seperate join condition is a bit faster
     //$cond[] = "((`report`.`variation_id` = `$alias`.`variation_id`) OR (`report`.`variation_id` IS NULL && `$alias`.`variation_id` IS NULL))";
     /* if($this->getParam(self::VARIATION)) {
            $cond[] = "`report`.`variation_id` = `$alias`.`variation_id`";
        }
        else {
            $cond[] = "`$alias`.`variation_id` IS NULL";
        }*/
     $cond = implode(' AND ', $cond);
     $select->joinLeft(array($alias => $this->getTable($table)), $cond, null);
     return $alias;
 }

 /**
  * FastIndexer
  *
  * @param Varien_Db_Select $select
  * @param string           $attributeCode
  * @param int              $storeId
  */
 private function _addCategoryAttributeToSelect(Varien_Db_Select $select, $attributeCode, $storeId)
 {
     $adapter = $this->_getReadAdapter();
     /** @var $adapter SchumacherFM_FastIndexer_Model_Db_Adapter_Pdo_Mysql */
     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 = 'cat' . $attributeCode;
     $tAttrId = (int) $this->_categoryAttributes[$attributeCode]['attribute_id'];
     $tTypeId = (int) $this->_categoryAttributes[$attributeCode]['entity_type_id'];
     if (1 === (int) $this->_categoryAttributes[$attributeCode]['is_global'] || $storeId == 0) {
         $joinColumns = array($tableAlias . '.entity_type_id = ' . $tTypeId, $tableAlias . '.store_id = 0', $tableAlias . '.attribute_id = ' . $tAttrId, 'main_table.entity_id = ' . $tableAlias . '.entity_id');
         $select->joinLeft(array($tableAlias => $this->_categoryAttributes[$attributeCode]['table']), implode(' AND ', $joinColumns), array())->columns(array($attributeCode => $tableAlias . '.value'));
     } else {
         $t1 = 't1' . $tableAlias;
         $t2 = 't2' . $tableAlias;
         $valueExpr = $adapter->getCheckSql('IFNULL(' . $t2 . '.value_id,0) > 0', $t2 . '.value', $t1 . '.value');
         $select->joinLeft(array($t1 => $this->_categoryAttributes[$attributeCode]['table']), 'main_table.entity_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())->columns(array($attributeCode => $valueExpr));
     }
 }

 /**
  * Add attribute data to select
  *
  * @param Varien_Db_Select $select
  * @param string $attributeCode
  * @param Mage_Core_Model_Website $website
  * @return Mage_Bundle_Model_Resource_Price_Index
  */
 protected function _addAttributeDataToSelect(Varien_Db_Select $select, $attributeCode, Mage_Core_Model_Website $website)
 {
     $attribute = $this->_getAttribute($attributeCode);
     $store = $website->getDefaultStore();
     if ($attribute->isScopeGlobal()) {
         $table = 't_' . $attribute->getAttributeCode();
         $select->joinLeft(array($table => $attribute->getBackend()->getTable()), "e.entity_id={$table}.entity_id" . " AND {$table}.attribute_id={$attribute->getAttributeId()}" . " AND {$table}.store_id=0", array($attribute->getAttributeCode() => $table . '.value'));
     } else {
         $tableName = $attribute->getBackend()->getTable();
         $tableGlobal = 't1_' . $attribute->getAttributeCode();
         $tableStore = 't2_' . $attribute->getAttributeCode();
         $attributeCond = $this->getReadConnection()->getCheckSql($tableStore . '.value_id > 0', $tableStore . '.value', $tableGlobal . '.value');
         $select->joinLeft(array($tableGlobal => $tableName), "e.entity_id = {$tableGlobal}.entity_id" . " AND {$tableGlobal}.attribute_id = {$attribute->getAttributeId()}" . " AND {$tableGlobal}.store_id = 0", array($attribute->getAttributeCode() => $attributeCond))->joinLeft(array($tableStore => $tableName), "{$tableGlobal}.entity_id = {$tableStore}.entity_id" . " AND {$tableGlobal}.attribute_id = {$tableStore}.attribute_id" . " AND {$tableStore}.store_id = " . $store->getId(), array());
     }
     return $this;
 }