Сортировка по цене с учётом модификаций
2wind
19 декабря 2013 г.
Добрый вечер.
Вопрос касается работы с querybuilder в ТДС "Интернет-магазин".
В стандартной ТДС "Интернет-магазин" есть такой код:
if ($price_from || $price_to || $sorting == 1 || $sorting == 2)
{
// Получаем список валют магазина
$aShop_Currencies = Core_Entity::factory('Shop_Currency')->findAll();
$query_currency_switch = 'price';
foreach ($aShop_Currencies as $oShop_Currency)
{
// Получаем коэффициент пересчета для каждой валюты
$currency_coefficient = Shop_Controller::instance()->getCurrencyCoefficientInShopCurrency(
$oShop_Currency, $oShop->Shop_Currency
);
$query_currency_switch = "IF (`shop_items`.`shop_currency_id` = '{$oShop_Currency->id}', IF (shop_discounts.percent, price * (100 - shop_discounts.percent) * {$currency_coefficient} / 100, shop_items.price * {$currency_coefficient}), {$query_currency_switch})";
}
$Shop_Controller_Show->shopItems()
->queryBuilder()
->select(array(Core_QueryBuilder::expression($query_currency_switch), 'absolute_price'))
->leftJoin('shop_item_discounts', 'shop_items.id', '=', 'shop_item_discounts.id')
->leftJoin('shop_discounts', 'shop_item_discounts.id', '=', 'shop_discounts.id', array(
array('AND (' => array('shop_discounts.end_datetime', '>=', $current_date)),
array('OR' => array('shop_discounts.end_datetime', '=', '0000-00-00 00:00:00')),
array('AND' => array('shop_discounts.start_datetime', '<=', $current_date)),
array(')' => NULL),
));
$Shop_Controller_Show->shopItems()->queryBuilder()
->clearOrderBy()
->orderBy('absolute_price', $sorting == 1 ? 'ASC' : 'DESC');
}
который позволяет сортировать товары по цене. В результате выполнения этого кода получается такой запрос в БД:
SELECT SQL_CALC_FOUND_ROWS `shop_items`.*, IF (`shop_items`.`shop_currency_id` = '3', IF (shop_discounts.percent, price * (100 - shop_discounts.percent) * 23.639664 / 100, shop_items.price * 23.639664), IF (`shop_items`.`shop_currency_id` = '2', IF (shop_discounts.percent, price * (100 - shop_discounts.percent) * 36.889479 / 100, shop_items.price * 36.889479), IF (`shop_items`.`shop_currency_id` = '1', IF (shop_discounts.percent, price * (100 - shop_discounts.percent) * 1 / 100, shop_items.price * 1), price))) AS `absolute_price` FROM `shop_items` LEFT OUTER JOIN `shop_item_discounts` ON `shop_items`.`id` = `shop_item_discounts`.`shop_item_id` LEFT OUTER JOIN `shop_discounts` ON `shop_item_discounts`.`shop_discount_id` = `shop_discounts`.`id` AND ( `shop_discounts`.`end_datetime` >= '2013-12-19 19:35:57' OR `shop_discounts`.`end_datetime` = '0000-00-00 00:00:00' AND `shop_discounts`.`start_datetime` <= '2013-12-19 19:35:57' ) WHERE `shop_items`.`shop_id` = '1' AND ( `shop_items`.`start_datetime` < '2013-12-19 19:35:57' OR `shop_items`.`start_datetime` = '0000-00-00 00:00:00' ) AND ( `shop_items`.`end_datetime` > '2013-12-19 19:35:57' OR `shop_items`.`end_datetime` = '0000-00-00 00:00:00' ) AND `shop_items`.`siteuser_group_id` IN (0, -1) AND `shop_items`.`modification_id` = 0 AND `shop_items`.`active` = 1 AND `shop_items`.`shop_group_id` = 586 AND `shop_items`.`deleted` = 0 ORDER BY `absolute_price` ASC LIMIT 6 OFFSET 0
Как правильно изменить код, чтобы запрос в БД выглядел таким образом:
SELECT SQL_CALC_FOUND_ROWS `shop_items`.*, IF (`shop_items`.`shop_currency_id` = '3', IF (shop_discounts.percent, mprice * (100 - shop_discounts.percent) * 23.639664 / 100, shop_items.mprice * 23.639664), IF (`shop_items`.`shop_currency_id` = '2', IF (shop_discounts.percent, mprice * (100 - shop_discounts.percent) * 36.889479 / 100, shop_items.mprice * 36.889479), IF (`shop_items`.`shop_currency_id` = '1', IF (shop_discounts.percent, mprice * (100 - shop_discounts.percent) * 1 / 100, shop_items.mprice * 1), mprice))) AS `absolute_price` FROM (SELECT `shop_items`.*, IF(`shop_items_sub`.`id`, MIN(`shop_items_sub`.`price`), `shop_items`.`price`) AS `mprice` FROM `shop_items` LEFT JOIN `shop_items` `shop_items_sub` ON `shop_items_sub`.`modification_id`=`shop_items`.`id` WHERE `shop_items`.`shop_id` = 1 AND `shop_items`.`modification_id` > 0 AND ( `shop_items`.`start_datetime` = '2013-12-19 17:12:24' OR `shop_items`.`start_datetime` = '0000-00-00 00:00:00' ) AND ( `shop_items`.`end_datetime` = '2013-12-19 17:12:24' OR `shop_items`.`end_datetime` = '0000-00-00 00:00:00' ) AND `shop_items`.`siteuser_group_id` IN (0, -1) AND `shop_items`.`deleted` = 0 AND `shop_items`.`active` = 1 GROUP BY `shop_items`.`id`) `shop_items` LEFT OUTER JOIN `shop_item_discounts` ON `shop_items`.`id` = `shop_item_discounts`.`id` LEFT OUTER JOIN `shop_discounts` ON `shop_item_discounts`.`id` = `shop_discounts`.`id` AND ( `shop_discounts`.`end_datetime` >= '2013-12-19 17:12:24' OR `shop_discounts`.`end_datetime` = '0000-00-00 00:00:00' AND `shop_discounts`.`start_datetime` <= '2013-12-19 17:12:24' ) WHERE 1 GROUP BY `shop_items`.`id` HAVING `absolute_price` >= 465 AND `absolute_price` <= 5153
Приведите, пожалуйста, рабочий код, чтобы понять, как создавать подобные запросы на querybuilder.
Вопрос касается работы с querybuilder в ТДС "Интернет-магазин".
В стандартной ТДС "Интернет-магазин" есть такой код:
if ($price_from || $price_to || $sorting == 1 || $sorting == 2)
{
// Получаем список валют магазина
$aShop_Currencies = Core_Entity::factory('Shop_Currency')->findAll();
$query_currency_switch = 'price';
foreach ($aShop_Currencies as $oShop_Currency)
{
// Получаем коэффициент пересчета для каждой валюты
$currency_coefficient = Shop_Controller::instance()->getCurrencyCoefficientInShopCurrency(
$oShop_Currency, $oShop->Shop_Currency
);
$query_currency_switch = "IF (`shop_items`.`shop_currency_id` = '{$oShop_Currency->id}', IF (shop_discounts.percent, price * (100 - shop_discounts.percent) * {$currency_coefficient} / 100, shop_items.price * {$currency_coefficient}), {$query_currency_switch})";
}
$Shop_Controller_Show->shopItems()
->queryBuilder()
->select(array(Core_QueryBuilder::expression($query_currency_switch), 'absolute_price'))
->leftJoin('shop_item_discounts', 'shop_items.id', '=', 'shop_item_discounts.id')
->leftJoin('shop_discounts', 'shop_item_discounts.id', '=', 'shop_discounts.id', array(
array('AND (' => array('shop_discounts.end_datetime', '>=', $current_date)),
array('OR' => array('shop_discounts.end_datetime', '=', '0000-00-00 00:00:00')),
array('AND' => array('shop_discounts.start_datetime', '<=', $current_date)),
array(')' => NULL),
));
$Shop_Controller_Show->shopItems()->queryBuilder()
->clearOrderBy()
->orderBy('absolute_price', $sorting == 1 ? 'ASC' : 'DESC');
}
который позволяет сортировать товары по цене. В результате выполнения этого кода получается такой запрос в БД:
SELECT SQL_CALC_FOUND_ROWS `shop_items`.*, IF (`shop_items`.`shop_currency_id` = '3', IF (shop_discounts.percent, price * (100 - shop_discounts.percent) * 23.639664 / 100, shop_items.price * 23.639664), IF (`shop_items`.`shop_currency_id` = '2', IF (shop_discounts.percent, price * (100 - shop_discounts.percent) * 36.889479 / 100, shop_items.price * 36.889479), IF (`shop_items`.`shop_currency_id` = '1', IF (shop_discounts.percent, price * (100 - shop_discounts.percent) * 1 / 100, shop_items.price * 1), price))) AS `absolute_price` FROM `shop_items` LEFT OUTER JOIN `shop_item_discounts` ON `shop_items`.`id` = `shop_item_discounts`.`shop_item_id` LEFT OUTER JOIN `shop_discounts` ON `shop_item_discounts`.`shop_discount_id` = `shop_discounts`.`id` AND ( `shop_discounts`.`end_datetime` >= '2013-12-19 19:35:57' OR `shop_discounts`.`end_datetime` = '0000-00-00 00:00:00' AND `shop_discounts`.`start_datetime` <= '2013-12-19 19:35:57' ) WHERE `shop_items`.`shop_id` = '1' AND ( `shop_items`.`start_datetime` < '2013-12-19 19:35:57' OR `shop_items`.`start_datetime` = '0000-00-00 00:00:00' ) AND ( `shop_items`.`end_datetime` > '2013-12-19 19:35:57' OR `shop_items`.`end_datetime` = '0000-00-00 00:00:00' ) AND `shop_items`.`siteuser_group_id` IN (0, -1) AND `shop_items`.`modification_id` = 0 AND `shop_items`.`active` = 1 AND `shop_items`.`shop_group_id` = 586 AND `shop_items`.`deleted` = 0 ORDER BY `absolute_price` ASC LIMIT 6 OFFSET 0
Как правильно изменить код, чтобы запрос в БД выглядел таким образом:
SELECT SQL_CALC_FOUND_ROWS `shop_items`.*, IF (`shop_items`.`shop_currency_id` = '3', IF (shop_discounts.percent, mprice * (100 - shop_discounts.percent) * 23.639664 / 100, shop_items.mprice * 23.639664), IF (`shop_items`.`shop_currency_id` = '2', IF (shop_discounts.percent, mprice * (100 - shop_discounts.percent) * 36.889479 / 100, shop_items.mprice * 36.889479), IF (`shop_items`.`shop_currency_id` = '1', IF (shop_discounts.percent, mprice * (100 - shop_discounts.percent) * 1 / 100, shop_items.mprice * 1), mprice))) AS `absolute_price` FROM (SELECT `shop_items`.*, IF(`shop_items_sub`.`id`, MIN(`shop_items_sub`.`price`), `shop_items`.`price`) AS `mprice` FROM `shop_items` LEFT JOIN `shop_items` `shop_items_sub` ON `shop_items_sub`.`modification_id`=`shop_items`.`id` WHERE `shop_items`.`shop_id` = 1 AND `shop_items`.`modification_id` > 0 AND ( `shop_items`.`start_datetime` = '2013-12-19 17:12:24' OR `shop_items`.`start_datetime` = '0000-00-00 00:00:00' ) AND ( `shop_items`.`end_datetime` = '2013-12-19 17:12:24' OR `shop_items`.`end_datetime` = '0000-00-00 00:00:00' ) AND `shop_items`.`siteuser_group_id` IN (0, -1) AND `shop_items`.`deleted` = 0 AND `shop_items`.`active` = 1 GROUP BY `shop_items`.`id`) `shop_items` LEFT OUTER JOIN `shop_item_discounts` ON `shop_items`.`id` = `shop_item_discounts`.`id` LEFT OUTER JOIN `shop_discounts` ON `shop_item_discounts`.`id` = `shop_discounts`.`id` AND ( `shop_discounts`.`end_datetime` >= '2013-12-19 17:12:24' OR `shop_discounts`.`end_datetime` = '0000-00-00 00:00:00' AND `shop_discounts`.`start_datetime` <= '2013-12-19 17:12:24' ) WHERE 1 GROUP BY `shop_items`.`id` HAVING `absolute_price` >= 465 AND `absolute_price` <= 5153
Приведите, пожалуйста, рабочий код, чтобы понять, как создавать подобные запросы на querybuilder.
2wind
24 декабря 2013 г.
Решили. Если кому-то понадобится:
if ($price_from || $price_to || $sorting == 1 || $sorting == 2)
{
// Получаем список валют магазина
$aShop_Currencies = Core_Entity::factory('Shop_Currency')->findAll();
$default_currency_id = $oShop->Shop_Currency->id; // здесь у нас ID основной валюты магазина
$default_currency_coefficient = $oShop->Shop_Currency->exchange_rate; // здесь у нас курс основной валюты магазина
/* делаем так потому, что у основных товаров с модификациями валюта может быть не указана */
$query_currency_switch = "IF (shop_items.shop_currency_id = {$default_currency_id} OR shop_items.shop_currency_id = 0, IF (shop_discounts.percent, IF(`shop_items`.`price` = '0.00', (SELECT MIN(`shop_items_sub`.`price`) FROM `shop_items` AS `shop_items_sub` WHERE `shop_items_sub`.`modification_id`=`shop_items`.`id` AND `shop_items_sub`.`deleted` = 0 AND `shop_items_sub`.`active` = 1), `shop_items`.`price`) * (100 - shop_discounts.percent) * {$default_currency_coefficient} / 100, IF(`shop_items`.`price` = '0.00', (SELECT MIN(`shop_items_sub`.`price`) FROM `shop_items` AS `shop_items_sub` WHERE `shop_items_sub`.`modification_id`=`shop_items`.`id` AND `shop_items_sub`.`deleted` = 0 AND `shop_items_sub`.`active` = 1), `shop_items`.`price`) * {$default_currency_coefficient}), `shop_items`.`price`)";
foreach ($aShop_Currencies as $oShop_Currency)
{
if ($oShop_Currency->id != $oShop->Shop_Currency->id) {
// Получаем коэффициент пересчета для каждой валюты, кроме основной валюты магазина
$currency_coefficient = Shop_Controller::instance()->getCurrencyCoefficientInShopCurrency(
$oShop_Currency, $oShop->Shop_Currency
);
$query_currency_switch = "IF (shop_items.shop_currency_id = {$oShop_Currency->id}, IF (shop_discounts.percent, IF(`shop_items`.`price` = '0.00', (SELECT MIN(`shop_items_sub`.`price`) FROM `shop_items` AS `shop_items_sub` WHERE `shop_items_sub`.`modification_id`=`shop_items`.`id` AND `shop_items_sub`.`deleted` = 0 AND `shop_items_sub`.`active` = 1), `shop_items`.`price`) * (100 - shop_discounts.percent) * {$currency_coefficient} / 100, IF(`shop_items`.`price` = '0.00', (SELECT MIN(`shop_items_sub`.`price`) FROM `shop_items` AS `shop_items_sub` WHERE `shop_items_sub`.`modification_id`=`shop_items`.`id` AND `shop_items_sub`.`deleted` = 0 AND `shop_items_sub`.`active` = 1), `shop_items`.`price`) * {$currency_coefficient}), {$query_currency_switch})";
}
}
$current_date = date('Y-m-d H:i:s');
$Shop_Controller_Show->shopItems()
->queryBuilder()
->select(array(Core_QueryBuilder::expression($query_currency_switch), 'absolute_price'))
->leftJoin('shop_item_discounts', 'shop_items.id', '=', 'shop_item_discounts.shop_item_id')
->leftJoin('shop_discounts', 'shop_item_discounts.shop_discount_id', '=', 'shop_discounts.id', array(
array('AND (' => array('shop_discounts.end_datetime', '>=', $current_date)),
array('OR' => array('shop_discounts.end_datetime', '=', '0000-00-00 00:00:00')),
array('AND' => array('shop_discounts.start_datetime', '<=', $current_date)),
array(')' => NULL),
));
$Shop_Controller_Show->shopItems()->queryBuilder()
->clearOrderBy()
->orderBy('absolute_price', $sorting == 1 ? 'ASC' : 'DESC');
}
if ($price_from || $price_to || $sorting == 1 || $sorting == 2)
{
// Получаем список валют магазина
$aShop_Currencies = Core_Entity::factory('Shop_Currency')->findAll();
$default_currency_id = $oShop->Shop_Currency->id; // здесь у нас ID основной валюты магазина
$default_currency_coefficient = $oShop->Shop_Currency->exchange_rate; // здесь у нас курс основной валюты магазина
/* делаем так потому, что у основных товаров с модификациями валюта может быть не указана */
$query_currency_switch = "IF (shop_items.shop_currency_id = {$default_currency_id} OR shop_items.shop_currency_id = 0, IF (shop_discounts.percent, IF(`shop_items`.`price` = '0.00', (SELECT MIN(`shop_items_sub`.`price`) FROM `shop_items` AS `shop_items_sub` WHERE `shop_items_sub`.`modification_id`=`shop_items`.`id` AND `shop_items_sub`.`deleted` = 0 AND `shop_items_sub`.`active` = 1), `shop_items`.`price`) * (100 - shop_discounts.percent) * {$default_currency_coefficient} / 100, IF(`shop_items`.`price` = '0.00', (SELECT MIN(`shop_items_sub`.`price`) FROM `shop_items` AS `shop_items_sub` WHERE `shop_items_sub`.`modification_id`=`shop_items`.`id` AND `shop_items_sub`.`deleted` = 0 AND `shop_items_sub`.`active` = 1), `shop_items`.`price`) * {$default_currency_coefficient}), `shop_items`.`price`)";
foreach ($aShop_Currencies as $oShop_Currency)
{
if ($oShop_Currency->id != $oShop->Shop_Currency->id) {
// Получаем коэффициент пересчета для каждой валюты, кроме основной валюты магазина
$currency_coefficient = Shop_Controller::instance()->getCurrencyCoefficientInShopCurrency(
$oShop_Currency, $oShop->Shop_Currency
);
$query_currency_switch = "IF (shop_items.shop_currency_id = {$oShop_Currency->id}, IF (shop_discounts.percent, IF(`shop_items`.`price` = '0.00', (SELECT MIN(`shop_items_sub`.`price`) FROM `shop_items` AS `shop_items_sub` WHERE `shop_items_sub`.`modification_id`=`shop_items`.`id` AND `shop_items_sub`.`deleted` = 0 AND `shop_items_sub`.`active` = 1), `shop_items`.`price`) * (100 - shop_discounts.percent) * {$currency_coefficient} / 100, IF(`shop_items`.`price` = '0.00', (SELECT MIN(`shop_items_sub`.`price`) FROM `shop_items` AS `shop_items_sub` WHERE `shop_items_sub`.`modification_id`=`shop_items`.`id` AND `shop_items_sub`.`deleted` = 0 AND `shop_items_sub`.`active` = 1), `shop_items`.`price`) * {$currency_coefficient}), {$query_currency_switch})";
}
}
$current_date = date('Y-m-d H:i:s');
$Shop_Controller_Show->shopItems()
->queryBuilder()
->select(array(Core_QueryBuilder::expression($query_currency_switch), 'absolute_price'))
->leftJoin('shop_item_discounts', 'shop_items.id', '=', 'shop_item_discounts.shop_item_id')
->leftJoin('shop_discounts', 'shop_item_discounts.shop_discount_id', '=', 'shop_discounts.id', array(
array('AND (' => array('shop_discounts.end_datetime', '>=', $current_date)),
array('OR' => array('shop_discounts.end_datetime', '=', '0000-00-00 00:00:00')),
array('AND' => array('shop_discounts.start_datetime', '<=', $current_date)),
array(')' => NULL),
));
$Shop_Controller_Show->shopItems()->queryBuilder()
->clearOrderBy()
->orderBy('absolute_price', $sorting == 1 ? 'ASC' : 'DESC');
}
Авторизация
