· 8 years ago · Jan 26, 2018, 10:46 AM
1DROP TABLE IF EXISTS products_attributes_temp;
2CREATE TABLE products_attributes_temp (
3 `products_attributes_id` INT UNSIGNED NOT NULL,
4 `article_supplier` VARCHAR(255) NOT NULL,
5 `brandswelove_id` INT UNSIGNED NOT NULL,
6 `suppliers_id` INT UNSIGNED NOT NULL,
7 `color_id` INT UNSIGNED NOT NULL,
8 `ls_id` INT UNSIGNED NOT NULL DEFAULT 0,
9 `growth_id` INT UNSIGNED NOT NULL DEFAULT 0,
10 `age_id` INT UNSIGNED NOT NULL DEFAULT 0,
11 `ru_id` INT UNSIGNED NOT NULL DEFAULT 0,
12 `size_id` INT UNSIGNED NOT NULL DEFAULT 0,
13 `gh_id` INT UNSIGNED NOT NULL DEFAULT 0,
14 `volume_id` INT UNSIGNED NOT NULL DEFAULT 0,
15 PRIMARY KEY (`products_attributes_id`),
16 INDEX `my_index` (`article_supplier`, `brandswelove_id`, `suppliers_id`, `color_id`, `ls_id`, `growth_id`, `age_id`, `ru_id`, `size_id`, `gh_id`, `volume_id`)
17)
18 COMMENT = 'Ð’Ñ€ÐµÐ¼ÐµÐ½Ð½Ð°Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ†Ð° Ð´Ð»Ñ Ð·Ð°Ð´Ð°Ñ‡Ð¸ 24576'
19 COLLATE = 'utf8_general_ci'
20 ENGINE = InnoDB;
21
22DROP PROCEDURE IF EXISTS fill_products_attributes_temp;
23DELIMITER $$
24CREATE PROCEDURE fill_products_attributes_temp()
25 BEGIN
26 SET @limitValue = 250000;
27
28 SELECT
29 MAX(pa.products_attributes_id),
30 MIN(pa.products_attributes_id)
31 INTO
32 @maxValue,
33 @startValue
34 FROM products_attributes AS pa;
35
36 REPEAT
37
38 INSERT IGNORE INTO products_attributes_temp
39 SELECT
40 pa.products_attributes_id,
41 pa.article_supplier,
42 p.brandswelove_id,
43 c.suppliers_id,
44 pao_color.products_options_values_id AS color_id,
45
46 pao_ls.products_options_values_id AS ls_id,
47 pao_growth.products_options_values_id AS growth_id,
48 pao_age.products_options_values_id AS age_id,
49 pao_ru.products_options_values_id AS ru_id,
50 pao_size.products_options_values_id AS size_id,
51 pao_gh.products_options_values_id AS gh_id,
52 pao_volume.products_options_values_id AS volume_id
53 FROM `products_attributes` AS `pa`
54 JOIN `products` AS `p` ON p.products_id = pa.products_id
55 JOIN `products_to_categories` AS `ptc` ON ptc.products_id = p.products_id
56 JOIN `categories` AS `c` ON c.categories_id = ptc.categories_id
57
58 JOIN `products_attributes_options` AS `pao_color` ON pao_color.products_attributes_id = pa.products_attributes_id AND pao_color.products_options_id = 3
59
60 LEFT JOIN `products_attributes_options` AS `pao_ls` ON pao_ls.products_attributes_id = pa.products_attributes_id AND pao_ls.products_options_id = 15
61 LEFT JOIN `products_attributes_options` AS `pao_growth` ON pao_growth.products_attributes_id = pa.products_attributes_id AND pao_growth.products_options_id = 11
62 LEFT JOIN `products_attributes_options` AS `pao_age` ON pao_age.products_attributes_id = pa.products_attributes_id AND pao_age.products_options_id = 12
63 LEFT JOIN `products_attributes_options` AS `pao_ru` ON pao_ru.products_attributes_id = pa.products_attributes_id AND pao_ru.products_options_id = 1
64 LEFT JOIN `products_attributes_options` AS `pao_size` ON pao_size.products_attributes_id = pa.products_attributes_id AND pao_size.products_options_id = 28
65 LEFT JOIN `products_attributes_options` AS `pao_gh` ON pao_gh.products_attributes_id = pa.products_attributes_id AND pao_gh.products_options_id = 25
66 LEFT JOIN `products_attributes_options` AS `pao_volume` ON pao_volume.products_attributes_id = pa.products_attributes_id AND pao_volume.products_options_id = 19
67
68 WHERE
69 pa.products_attributes_id >= @startValue
70 AND pa.products_attributes_id < @startValue + @limitValue
71 AND COALESCE(pa.article_supplier, '') != ''
72 AND COALESCE(p.brandswelove_id, 0) != 0;
73
74 SET @startValue = @startValue + @limitValue;
75 UNTIL @startValue > @maxValue
76 END REPEAT;
77 END $$
78DELIMITER ;
79
80CALL fill_products_attributes_temp();
81DROP PROCEDURE IF EXISTS fill_products_attributes_temp;
82
83REPLACE INTO products_attributes_legacy
84 SELECT
85 pat.products_attributes_id,
86 t.min_products_attributes_id
87 FROM products_attributes_temp AS pat
88 JOIN (
89 SELECT
90 MIN(pat.products_attributes_id) AS min_products_attributes_id,
91 pat.article_supplier,
92 pat.brandswelove_id,
93 pat.suppliers_id,
94 pat.color_id,
95 pat.ls_id,
96 pat.growth_id,
97 pat.age_id,
98 pat.ru_id,
99 pat.size_id,
100 pat.gh_id,
101 pat.volume_id
102 FROM products_attributes_temp AS pat
103 GROUP BY pat.article_supplier, pat.brandswelove_id, pat.suppliers_id, pat.color_id, pat.ls_id, pat.growth_id, pat.age_id, pat.ru_id, pat.size_id, pat.gh_id, pat.volume_id
104 ) AS t ON
105 pat.article_supplier = t.article_supplier
106 AND pat.brandswelove_id = t.brandswelove_id
107 AND pat.suppliers_id = t.suppliers_id
108 AND pat.color_id = t.color_id
109
110 AND pat.ls_id = t.ls_id
111 AND pat.growth_id = t.growth_id
112 AND pat.age_id = t.age_id
113 AND pat.ru_id = t.ru_id
114 AND pat.size_id = t.size_id
115 AND pat.gh_id = t.gh_id
116 AND pat.volume_id = t.volume_id;