Фильтр товаров со скидками с включенным быстрым фильтром

14 декабря 2022 г.

Добрый день
Пытаюсь вывести товары со скидками в ТДС магазина с включенным быстрым фильтром
При добавлении такого кода

   $Shop_Controller_Show->shopItems()
      ->queryBuilder()
      ->select('shop_items.*')
      ->join(array('shop_item_discounts', 'item_discounts'), 'item_discounts.shop_item_id', '=', 'shop_items.id')
      ->join(array('shop_discounts', 'discounts'), 'discounts.id', '=', 'item_discounts.shop_discount_id')
      ->where('discounts.active', '=', 1)
      ->where('discounts.start_datetime', '<', Core_Date::timestamp2sql(time()))
      ->where('discounts.end_datetime', '>', Core_Date::timestamp2sql(time()))
      ->where('discounts.deleted', '=', 0)
      ->where('shop_items.modification_id', '=', 0)
      ->clearOrderBy()
      ->orderBy('shop_items.datetime', 'ASC'); // price, name, datetime

отображается такая ошибка

Exception: Query error 42S22: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'shop_items.id' in 'on clause'. Query: SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS `shop_items`.* FROM `shop_filter1` INNER JOIN `shop_item_discounts` AS `item_discounts` ON `item_discounts`.`shop_item_id` = `shop_items`.`id` INNER JOIN `shop_discounts` AS `discounts` ON `discounts`.`id` = `item_discounts`.`shop_discount_id` INNER JOIN `shop_items` ON `shop_items`.`id` = `shop_filter1`.`shop_item_id` LEFT OUTER JOIN `shop_warehouse_items` ON `shop_warehouse_items`.`shop_item_id` = `shop_items`.`id` LEFT OUTER JOIN `shop_groups` AS `sg` ON `sg`.`id` = `shop_items`.`shop_group_id` WHERE `shop_items`.`shop_id` = '1' AND ( `shop_items`.`start_datetime` < '2022-12-14 11:42:14' OR `shop_items`.`start_datetime` = '0000-00-00 00:00:00' ) AND ( `shop_items`.`end_datetime` > '2022-12-14 11:42:14' OR `shop_items`.`end_datetime` = '0000-00-00 00:00:00' ) AND `shop_items`.`siteuser_group_id` IN (0, -1) AND `discounts`.`active` = 1 AND `discounts`.`start_datetime` < '2022-12-14 11:42:14' AND `discounts`.`end_datetime` > '2022-12-14 11:42:14' AND `discounts`.`deleted` = 0 AND `shop_items`.`modification_id` = 0 AND `shop_filter1`.`primary` = 1 AND `shop_items`.`active` = 1 AND `shop_items`.`shortcut_id` = 0 AND ( `sg`.`active` = 1 AND `sg`.`deleted` = 0 AND `sg`.`siteuser_group_id` IN (0, -1) OR `sg`.`id` IS NULL ) AND `shop_items`.`deleted` = 0 GROUP BY `shop_items`.`id` ORDER BY SIGN(SUM(`shop_warehouse_items`.`count`)) DESC, `price_absolute` ASC LIMIT 24 OFFSET 0

Как правильно теперь выводить товары со скидкой?

HostDev.pw - модули для HostCMS, Telegram: @hostdev

Модератор 14 декабря 2022 г.

Re: Фильтр товаров со скидками с включенным быстрым фильтром

https://www.hostcms.ru/documentation/modules/shop/frontend/show-good-available-in-the-stock/

$fieldName = count($Shop_Controller_Show->getFilterProperties()) || count($Shop_Controller_Show->getFilterPrices()) || $Shop_Controller_Show->producer
    ? $Shop_Controller_Show->getFilterTableName() . '.shop_item_id'
    : 'shop_items.id';

$Shop_Controller_Show->shopItems()
   ->queryBuilder()
   ->join(array('shop_item_discounts', 'item_discounts'), 'item_discounts.shop_item_id', '=', $fieldName)
   ->join(array('shop_discounts', 'discounts'), 'discounts.id', '=', 'item_discounts.shop_discount_id')
   ->where('discounts.active', '=', 1)
   ->where('discounts.start_datetime', '<', Core_Date::timestamp2sql(time()))
   ->where('discounts.end_datetime', '>', Core_Date::timestamp2sql(time()))
   ->where('discounts.deleted', '=', 0)
   ->where('shop_items.modification_id', '=', 0)
   ->clearOrderBy()
   ->orderBy('shop_items.datetime', 'ASC'); // price, name, datetime

Авторизация