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