Closed tim-breitenstein-it closed 2 years ago
colinmollenhour
commented
4 years ago Your fix looks valid to me, grouping by website_id should prevent duplicate rules from different websites, but shouldn't there be a filter clause on website_id as well?
tim-breitenstein-it
commented
4 years ago @colinmollenhour Do you mean?
/**
* Prepare index select
*
* @param Mage_Core_Model_Website $website
* @param int|Zend_Db_Expr $time
* @return Varien_Db_Select
*/
protected function _prepareIndexSelect(Mage_Core_Model_Website $website, $time)
{
$nA = $this->_connection->quote('N/A');
$this->_connection->query('SET @price := 0');
$this->_connection->query('SET @group_id := NULL');
$this->_connection->query('SET @action_stop := NULL');
$indexSelect = $this->_connection->select()
->from(array('cppt' => $this->_getTemporaryTable()), array())
->order(array('cppt.grouped_id', 'cppt.sort_order', 'cppt.rule_product_id'))
->columns(
array(
'customer_group_id' => 'cppt.customer_group_id',
'product_id' => 'cppt.product_id',
'rule_price' => $this->_calculatePrice(),
'latest_start_date' => 'cppt.from_date',
'earliest_end_date' => 'cppt.to_date',
new Zend_Db_Expr(
$this->_connection->getCaseSql(
'',
array(
$this->_connection->getIfNullSql(
new Zend_Db_Expr('@group_id'),
$nA
) . ' != cppt.grouped_id' => new Zend_Db_Expr('@action_stop := cppt.action_stop'),
$this->_connection->getIfNullSql(
new Zend_Db_Expr('@group_id'),
$nA
) . ' = cppt.grouped_id' => '@action_stop := '
. $this->_connection->getIfNullSql(
new Zend_Db_Expr('@action_stop'),
new Zend_Db_Expr(0)
) . ' + cppt.action_stop',
)
)
),
new Zend_Db_Expr('@group_id := cppt.grouped_id'),
'from_time' => 'cppt.from_time',
'to_time' => 'cppt.to_time'
)
);
$select = $this->_connection->select()
->from($indexSelect, array())
->joinInner(
array(
'dates' => $this->_connection->select()->union(
array(
new Zend_Db_Expr(
'SELECT ' . $this->_connection->getDateAddSql(
$this->_connection->fromUnixtime($time),
-1,
Varien_Db_Adapter_Interface::INTERVAL_DAY
) . ' AS rule_date'
),
new Zend_Db_Expr('SELECT ' . $this->_connection->fromUnixtime($time) . ' AS rule_date'),
new Zend_Db_Expr(
'SELECT ' . $this->_connection->getDateAddSql(
$this->_connection->fromUnixtime($time),
1,
Varien_Db_Adapter_Interface::INTERVAL_DAY
) . ' AS rule_date'
),
)
)
),
'1=1',
array()
)
->columns(
array(
'rule_product_price_id' => new Zend_Db_Expr('NULL'),
'rule_date' => 'dates.rule_date',
'customer_group_id' => 'customer_group_id',
'product_id' => 'product_id',
'rule_price' => 'MIN(rule_price)',
'website_id' => new Zend_Db_Expr($website->getId()),
'latest_start_date' => 'latest_start_date',
'earliest_end_date' => 'earliest_end_date',
)
)
->where(new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " >= from_time"))
->where(
$this->_connection->getCheckSql(
new Zend_Db_Expr('to_time = 0'),
new Zend_Db_Expr(1),
new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " <= to_time")
)
)
->where(new Zend_Db_Expr($website->getId() . " = website_id"))
->group(array('customer_group_id', 'product_id', 'dates.rule_date', 'website_id'));
return $select;
}or something like that
/**
* Prepare index select
*
* @param Mage_Core_Model_Website $website
* @param int|Zend_Db_Expr $time
* @return Varien_Db_Select
*/
protected function _prepareIndexSelect(Mage_Core_Model_Website $website, $time)
{
$nA = $this->_connection->quote('N/A');
$this->_connection->query('SET @price := 0');
$this->_connection->query('SET @group_id := NULL');
$this->_connection->query('SET @action_stop := NULL');
$indexSelect = $this->_connection->select()
->from(array('cppt' => $this->_getTemporaryTable()), array())
->order(array('cppt.grouped_id', 'cppt.sort_order', 'cppt.rule_product_id'))
->columns(
array(
'customer_group_id' => 'cppt.customer_group_id',
'product_id' => 'cppt.product_id',
'rule_price' => $this->_calculatePrice(),
'latest_start_date' => 'cppt.from_date',
'earliest_end_date' => 'cppt.to_date',
new Zend_Db_Expr(
$this->_connection->getCaseSql(
'',
array(
$this->_connection->getIfNullSql(
new Zend_Db_Expr('@group_id'),
$nA
) . ' != cppt.grouped_id' => new Zend_Db_Expr('@action_stop := cppt.action_stop'),
$this->_connection->getIfNullSql(
new Zend_Db_Expr('@group_id'),
$nA
) . ' = cppt.grouped_id' => '@action_stop := '
. $this->_connection->getIfNullSql(
new Zend_Db_Expr('@action_stop'),
new Zend_Db_Expr(0)
) . ' + cppt.action_stop',
)
)
),
new Zend_Db_Expr('@group_id := cppt.grouped_id'),
'from_time' => 'cppt.from_time',
'to_time' => 'cppt.to_time'
)
);
$select = $this->_connection->select()
->from($indexSelect, array())
->joinInner(
array(
'dates' => $this->_connection->select()->union(
array(
new Zend_Db_Expr(
'SELECT ' . $this->_connection->getDateAddSql(
$this->_connection->fromUnixtime($time),
-1,
Varien_Db_Adapter_Interface::INTERVAL_DAY
) . ' AS rule_date'
),
new Zend_Db_Expr('SELECT ' . $this->_connection->fromUnixtime($time) . ' AS rule_date'),
new Zend_Db_Expr(
'SELECT ' . $this->_connection->getDateAddSql(
$this->_connection->fromUnixtime($time),
1,
Varien_Db_Adapter_Interface::INTERVAL_DAY
) . ' AS rule_date'
),
)
)
),
'1=1',
array()
)
->columns(
array(
'rule_product_price_id' => new Zend_Db_Expr('NULL'),
'rule_date' => 'dates.rule_date',
'customer_group_id' => 'customer_group_id',
'product_id' => 'product_id',
'rule_price' => 'MIN(rule_price)',
'website_id' => new Zend_Db_Expr($website->getId()),
'latest_start_date' => 'latest_start_date',
'earliest_end_date' => 'earliest_end_date',
)
)
->where(new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " >= from_time"))
->where(
$this->_connection->getCheckSql(
new Zend_Db_Expr('to_time = 0'),
new Zend_Db_Expr(1),
new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " <= to_time")
)
)
->addAttributeToFilter('website_id', $website->getId())
->group(array('customer_group_id', 'product_id', 'dates.rule_date', 'website_id'));
return $select;
}Yes, either of those, although my personal preference is:
->where('website_id = ?', $website->getId())Yes, either of those, although my personal preference is:
->where('website_id = ?', $website->getId())
This won´t work:
[Zend_Db_Statement_Exception]
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'website_id' in 'where clause', query was: What finally works for me:
/**
* Fill Index Data
*
* @param Mage_Core_Model_Website $website
* @param int $time
*/
protected function _fillIndexData(Mage_Core_Model_Website $website, $time)
{
$this->_connection->query(
$this->_connection->insertFromSelect(
$this->_prepareIndexSelect($website, $time),
$this->_resource->getTable('catalogrule/rule_product_price'),
[],
Varien_Db_Adapter_Interface::INSERT_IGNORE
)
);
}
Preconditions (*)
Steps to reproduce (*)
catalogrule_apply_allExpected result (*)
Actual result (*)
Possible Fix ?
app/code/core/Mage/CatalogRule/Model/Action/Index/Refresh.phpFrom:
->group(array('customer_group_id', 'product_id', 'dates.rule_date'));To:->group(array('customer_group_id', 'product_id', 'dates.rule_date', 'website_id'));