/** * @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; }
/** * 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; }
/** * Join url rewrite to select * * @param Varien_Db_Select $select * @param int $storeId * @return Enterprise_Catalog_Helper_Category_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_category' => $this->_resource->getTableName('enterprise_catalog/category')), 'url_rewrite_category.category_id = main_table.entity_id' . ' AND ' . $this->_connection->quoteInto('url_rewrite_category.store_id = ?', (int) $storeId), array(''))->joinLeft(array('url_rewrite' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'url_rewrite_category.url_rewrite_id = url_rewrite.url_rewrite_id AND url_rewrite.is_system = 1', array(''))->joinLeft(array('default_urc' => $this->_resource->getTableName('enterprise_catalog/category')), 'default_urc.category_id = url_rewrite_category.category_id AND default_urc.store_id = 0', array(''))->joinLeft(array('default_ur' => $this->_resource->getTableName('enterprise_urlrewrite/url_rewrite')), 'default_ur.url_rewrite_id = default_urc.url_rewrite_id AND default_ur.is_system = 1', array('request_path' => $requestPath)); return $this; }