php - PrestaShop 交叉销售产品按销售排序 [PHP MOD]
问题描述
我正在尝试修改 PrestaShop 模块 Crosselling,我需要添加 2 个 mod:
- 从列表中隐藏缺货项目。
- 按产品与页面产品共享订单的时间对产品列表进行排序。
我找到了产品列表功能:
if (count($orders) > 0) {
$list = '';
foreach ($orders as $order) {
$list .= (int)$order['id_order'].',';
}
$list = rtrim($list, ',');
$list_product_ids = join(',', $products_id);
if (Group::isFeatureActive()) {
$sql_groups_join = '
LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = product_shop.id_category_default
AND cp.id_product = product_shop.id_product)
LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cp.`id_category` = cg.`id_category`)';
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups_where = 'AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '='.(int)Group::getCurrent()->id);
}
$order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT DISTINCT od.product_id, pl.name, pl.description_short, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price,
cl.link_rewrite category, p.ean13, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
FROM '._DB_PREFIX_.'order_detail od
LEFT JOIN '._DB_PREFIX_.'product p ON (p.id_product = od.product_id)
'.Shop::addSqlAssociation('product', 'p').
(Combination::isFeatureActive() ? 'LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
ON (p.`id_product` = pa.`id_product`)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
'.Product::sqlStock('p', 'product_attribute_shop', false, $this->context->shop) : Product::sqlStock('p', 'product', false,
$this->context->shop)).'
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = od.product_id'.Shop::addSqlRestrictionOnLang('pl').')
LEFT JOIN '._DB_PREFIX_.'category_lang cl ON (cl.id_category = product_shop.id_category_default'
.Shop::addSqlRestrictionOnLang('cl').')
LEFT JOIN '._DB_PREFIX_.'image i ON (i.id_product = od.product_id)
'.(Group::isFeatureActive() ? $sql_groups_join : '').'
WHERE od.id_order IN ('.$list.')
AND pl.id_lang = '.(int)$this->context->language->id.'
AND cl.id_lang = '.(int)$this->context->language->id.'
AND od.product_id NOT IN ('.$list_product_ids.')
AND i.cover = 1
AND stock.quantity > 1
AND product_shop.active = 1
'.(Group::isFeatureActive() ? $sql_groups_where : '').'
ORDER BY RAND()
LIMIT '.(int)Configuration::get('CROSSSELLING_NBR'));
$tax_calc = Product::getTaxCalculationMethod();
foreach ($order_products as &$order_product) {
$order_product['id_product'] = (int)$order_product['product_id'];
$order_product['image'] = $this->context->link->getImageLink($order_product['link_rewrite'],
(int)$order_product['product_id'].'-'.(int)$order_product['id_image'], ImageType::getFormatedName('home'));
$order_product['link'] = $this->context->link->getProductLink((int)$order_product['product_id'], $order_product['link_rewrite'],
$order_product['category'], $order_product['ean13']);
if (Configuration::get('CROSSSELLING_DISPLAY_PRICE') && ($tax_calc == 0 || $tax_calc == 2)) {
$order_product['displayed_price'] = Product::getPriceStatic((int)$order_product['product_id'], true, null);
} elseif (Configuration::get('CROSSSELLING_DISPLAY_PRICE') && $tax_calc == 1) {
$order_product['displayed_price'] = Product::getPriceStatic((int)$order_product['product_id'], false, null);
}
$order_product['allow_oosp'] = Product::isAvailableWhenOutOfStock((int)$order_product['out_of_stock']);
if (!isset($final_products_list[$order_product['product_id'].'-'.$order_product['id_image']])) {
$final_products_list[$order_product['product_id'].'-'.$order_product['id_image']] = $order_product;
}
}
}
return $final_products_list;
}
因此,为了隐藏我添加的缺货商品:“AND stock.quantity > 1”,看起来效果很好,但我不确定如何修改“ORDER BY RAND()”以按价值订购产品(计数($订单)
也许我对函数工作有误解,我的PHP技能有限。
感谢您的帮助
第一次尝试:
$order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT od.product_id, pl.name, pl.description_short, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price,
cl.link_rewrite category, p.ean13, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
FROM '._DB_PREFIX_.'order_detail od
LEFT JOIN '._DB_PREFIX_.'product p ON (p.id_product = od.product_id)
'.Shop::addSqlAssociation('product', 'p').
(Combination::isFeatureActive() ? 'LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
ON (p.`id_product` = pa.`id_product`)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
'.Product::sqlStock('p', 'product_attribute_shop', false, $this->context->shop) : Product::sqlStock('p', 'product', false,
$this->context->shop)).'
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = od.product_id'.Shop::addSqlRestrictionOnLang('pl').')
LEFT JOIN '._DB_PREFIX_.'category_lang cl ON (cl.id_category = product_shop.id_category_default'
.Shop::addSqlRestrictionOnLang('cl').')
LEFT JOIN '._DB_PREFIX_.'image i ON (i.id_product = od.product_id)
'.(Group::isFeatureActive() ? $sql_groups_join : '').'
WHERE od.id_order IN ('.$list.')
GROUP BY od.product_id
AND pl.id_lang = '.(int)$this->context->language->id.'
AND cl.id_lang = '.(int)$this->context->language->id.'
AND od.product_id NOT IN ('.$list_product_ids.')
AND i.cover = 1
AND product_shop.active = 1
AND stock.quantity > 0
'.(Group::isFeatureActive() ? $sql_groups_where : '').'
ORDER BY sum(od.quantity) DESC
LIMIT '.(int)Configuration::get('CROSSSELLING_NBR'));
解决方案
原始查询已加入ps_order_detail表,因此您可以更轻松地按“订购的产品数量”进行订购。
您需要从 select 中删除 DISTINCT,按od.product_id 分组并按sum (od.quantity) desc 排序- 不过,这会使查询变得更重。
推荐阅读
- javascript - Ant Design:单击折叠标题时如何防止扩展,但允许按钮?
- django - 如何在 Django 中使用表单向导更新帖子
- postgresql - 如何将 PostgreSQL 9.6 中的数据库备份恢复到 PostgreSQL 12.2?
- c# - 如何访问 Outlook vsto 中的“收件人”自动填充电子邮件地址?缓存的联系人
- sql - T-SQL 存储过程错误:关键字“END”附近的语法不正确。严重性 15 PROCEDURE_1
- conditional-formatting - LibreOffice Calc(日期具有过去的编码格式)
- java - Solr Suggester 不适用于复制字段
- qt - QML FileDialog - 对要选择的文件设置数量限制
- javascript - connection.playStream 不是函数
- git-svn - 我可以用 subgit 映射 svn 外部吗?