public static function addSqlAssociation($table, $alias, $inner_join = true, $on = null, $force_not_default = false) { $sql = parent::addSqlAssociation($table, $alias, $inner_join, $on, $force_not_default); if (!Module::isInstalled('agilemultipleshop')) { return $sql; } $eaccess = AgileSellerManager::get_entity_access($table); if ($eaccess['owner_table_type'] == AgileSellerManager::OWNER_TABLE_UNKNOWN) { return $sql; } $xr_table = $eaccess['owner_xr_table']; $include_shared = ''; if (!$eaccess['is_exclusive']) { $include_shared = ',0'; } if (Shop::$id_shop_owner > 0) { $join = $inner_join ? 'INNER JOIN ' : 'LEFT JOIN '; if ($table == 'feature') { $join = 'LEFT JOIN '; } if ($eaccess['owner_table_type'] == AgileSellerManager::OWNER_TABLE_DEFINED) { $sql .= $join . _DB_PREFIX_ . $table . '_owner ' . $table . '_owner ON (' . $alias . '.id_' . $table . '=' . $table . '_owner.id_' . $table . ' AND IFNULL(' . $table . '_owner.id_owner,0) IN (' . Shop::$id_shop_owner . $include_shared . '))'; } else { $sql .= $join . _DB_PREFIX_ . 'object_owner ' . $table . '_object_owner ON (' . $table . '_object_owner.entity=\'' . $table . '\' AND ' . $alias . '.id_' . $table . '= ' . $table . '_object_owner.id_object AND IFNULL(' . $table . '_object_owner.id_owner,0) IN (' . Shop::$id_shop_owner . $include_shared . '))'; } } return $sql; }
public function sortProductIdList(array $productList, $filterParams) { if (count($productList) < 2) { return false; } if (!isset($filterParams['filter_category'])) { throw new \Exception('Cannot sort when filterParams does not contains \'filter_category\'.', 5010); } foreach ($filterParams as $k => $v) { if ($v == '' || strpos($k, 'filter_') !== 0) { continue; } if ($k == 'filter_category') { continue; } throw new \Exception('Cannot sort when filterParams contains other filter than \'filter_category\'.', 5010); } $categoryId = $filterParams['filter_category']; /* Sorting items on one page only, with ONE SQL UPDATE query, * then fixing bugs (duplicates and 0 values) on next pages with more queries, if needed. * * Most complicated case example: * We have to sort items from offset 5, limit 5, on total object count: 14 * The previous AND the next pages MUST NOT be impacted but fixed if needed. * legend: #<id>|P<position> * * Before sort: * #1|P2 #2|P4 #3|P5 #7|P8 #6|P9 #5|P10 #8|P11 #10|P13 #12|P14 #11|P15 #9|P16 #12|P18 #14|P19 #22|P24 * (there is holes in positions) * * Sort request: * #5|P?? #10|P?? #12|P?? #8|P?? #11|P?? * * After sort: * (previous page unchanged) (page to sort: sort and no duplicates) (the next pages MUST be shifted to avoid duplicates if any) * * Request input: * [#5]P10 [#10]P13 [#12]P14 [#8]P11 [#11]P15 */ $maxPosition = max(array_values($productList)); $sortedPositions = array_values($productList); sort($sortedPositions); // new positions to update // avoid '0', starts with '1', so shift right (+1) if ($sortedPositions[1] === 0) { foreach ($sortedPositions as $k => $v) { $sortedPositions[$k] = $v + 1; } } // combine old positions with new position in an array $combinedOldNewPositions = array_combine(array_values($productList), $sortedPositions); ksort($combinedOldNewPositions); // (keys: old positions starting at '1', values: new positions) $positionsMatcher = array_replace(array_pad(array(), $maxPosition, 0), $combinedOldNewPositions); // pad holes with 0 array_shift($positionsMatcher); // shift because [0] is not used in MySQL FIELD() $fields = implode(',', $positionsMatcher); // update current pages. $updatePositions = 'UPDATE `' . _DB_PREFIX_ . 'category_product` cp INNER JOIN `' . _DB_PREFIX_ . 'product` p ON (cp.`id_product` = p.`id_product`) ' . \ShopCore::addSqlAssociation('product', 'p') . ' SET cp.`position` = ELT(cp.`position`, ' . $fields . '), p.`date_upd` = "' . date('Y-m-d H:i:s') . '", product_shop.`date_upd` = "' . date('Y-m-d H:i:s') . '" WHERE cp.`id_category` = ' . $categoryId . ' AND cp.`id_product` IN (' . implode(',', array_keys($productList)) . ')'; \Db::getInstance()->query($updatePositions); // Fixes duplicates on all pages \Db::getInstance()->query('SET @i := 0'); $selectPositions = 'UPDATE`' . _DB_PREFIX_ . 'category_product` cp SET cp.`position` = (SELECT @i := @i + 1) WHERE cp.`id_category` = ' . $categoryId . ' ORDER BY cp.`id_product` NOT IN (' . implode(',', array_keys($productList)) . '), cp.`position` ASC'; \Db::getInstance()->query($selectPositions); return true; }
/** * Get combination images ids * * @param int $idAttribute * * @return array */ public function getImages($idAttribute) { return \DbCore::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT a.`id_image` as id FROM `' . _DB_PREFIX_ . 'product_attribute_image` a ' . \ShopCore::addSqlAssociation('product_attribute', 'a') . ' WHERE a.`id_product_attribute` = ' . (int) $idAttribute . ' '); }