Skip to content

Instantly share code, notes, and snippets.

@frkosk

frkosk/Change categories for products

Last active December 12, 2024 11:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save frkosk/9aee57e7258d59bdcd5aecf3d69d206b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save frkosk/9aee57e7258d59bdcd5aecf3d69d206b to your computer and use it in GitHub Desktop.
Download ZIP
Prestashop 8 - Manipulating categories in SQL database
Raw
Change categories for products
-- Definovanie produktov a kategórií ako reťazce
SET @product_ids = '1,2,3';
SET @category_ids = '10,20,30';
-- Definovanie predvolenej kategórie
SET @default_category_id = 10;
-- Vloženie nových kategórií pre produkty
INSERT INTO ps_category_product (id_category, id_product, position)
SELECT
c.id_category,
p.id_product,
IFNULL(MAX(cp.position) + 1, 0) AS new_position
FROM (
SELECT id_product
FROM ps_product
WHERE FIND_IN_SET(id_product, @product_ids)
) p
CROSS JOIN (
SELECT id_category
FROM ps_category
WHERE FIND_IN_SET(id_category, @category_ids)
) c
LEFT JOIN ps_category_product cp
ON cp.id_category = c.id_category AND cp.id_product = p.id_product
WHERE cp.id_product IS NULL
GROUP BY p.id_product, c.id_category;
-- Odstránenie kategórie s ID 2 pre produkty
DELETE cp
FROM ps_category_product cp
WHERE FIND_IN_SET(cp.id_product, @product_ids)
AND cp.id_category = 2;
-- Zmena predvolenej kategórie pre produkty, ktoré mali odstránenú kategóriu 2 ako predvolenú
UPDATE ps_product
SET id_category_default = @default_category_id
WHERE id_category_default = 2
AND FIND_IN_SET(id_product, @product_ids);
-- Zmena predvolenej kategórie pre produkty v tabuľke ps_product_shop
UPDATE ps_product_shop
SET id_category_default = @default_category_id
WHERE id_category_default = 2
AND FIND_IN_SET(id_product, @product_ids);
Raw
Get the name and ID of categries
-- Nastavenie premennej pre ID produktu
SET @product_id = ;
SELECT cp.id_category, cl.name AS category_name, cp.position
FROM ps_category_product cp
JOIN ps_category_lang cl ON cp.id_category = cl.id_category
WHERE cp.id_product = @product_id
AND cl.id_lang = 1 -- Nahraď 1 ID jazyka, ktorý používaš (napr. slovenčina)
ORDER BY cp.position ASC;
SELECT JSON_ARRAYAGG(cp.id_category ORDER BY cp.position ASC) AS category_ids_json
FROM ps_category_product cp
JOIN ps_category_lang cl ON cp.id_category = cl.id_category
WHERE cp.id_product = @product_id
AND cl.id_lang = 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment