Автор Тема: sql запрос  (Прочитано 44 раз)

14 Июль 2018, 16:24:45
  • Пользователь
  • **
  • Сообщений: 83
  • Репутация: +0/-0
  • Сообщество PrestaShop
    • Просмотр профиля
И снова sql менеджер.Изначально стоит задача "вытащить" все позиции товара со всеми возможными значениями в формат csv . В данном примере тестирую на отдельно взятом товаре.
Есть вот такой запрос:SELECT
p.id_product AS `ID`,
p.active AS `Active (0/1)`,
pl.`name` AS `Name`,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS `Categories (x,y,z...)`,
p.price AS `Price tax excluded or Price tax included`,
p.id_tax_rules_group AS `Tax rules ID`,
p.wholesale_price AS `Wholesale price`,
p.on_sale AS `On sale (0/1)`,
IF(pr.reduction_type='amount',pr.reduction,'') AS `Discount amount`,
IF(pr.reduction_type='percentage',pr.reduction,'') AS `Discount percent`,
pr.`from` AS `Discount from (yyyy-mm-dd)`,
pr.`to` AS `Discount to (yyyy-mm-dd)`,
p.reference AS `Reference #`,
pps.product_supplier_reference AS `Supplier reference #`,
ps.`name` AS `Supplier`,
pm.`name` AS `Manufacturer`,
p.ean13 AS `EAN13`,
p.upc AS `UPC`,
p.ecotax AS `Ecotax`,
p.width AS `Width`,
p.height AS `Height`,
p.depth AS `Depth`,
p.weight AS `Weight`,
pq.quantity AS `Quantity`,
p.minimal_quantity AS `Minimal quantity`,
p.visibility AS `Visibility`,
p.additional_shipping_cost AS `Additional shipping cost`,
p.unity AS `Unit for the unit price`,
p.unit_price_ratio AS `Unit price`,
pl.description_short AS `Short description`,
pl.description AS `Description`,
IF(t.`name` IS NOT NULL, GROUP_CONCAT(DISTINCT(t.`name`) SEPARATOR ','), '') AS `Tags (x,y,z...)`,
pl.meta_title AS `Meta title`,
pl.meta_keywords AS `Meta keywords`,
pl.meta_description AS `Meta description`,
pl.link_rewrite AS `URL rewritten`,
pl.available_now AS `Text when in stock`,
pl.available_later AS `Text when backorder allowed`,
p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
'' AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,
IF(pi.`id_product` IS NOT NULL, GROUP_CONCAT(DISTINCT(concat( 'http://my-URL.com/img/p/',mid(pi.id_image,1,1),'/', if (length(pi.id_image)>1, concat(mid(pi.id_image,2,1),'/'),''),if (length(pi.id_image)>2,concat(mid(pi.id_image,3,1),'/'),''),if (length(pi.id_image)>3,concat(mid(pi.id_image,4,1),'/'),''),if (length(pi.id_image)>4,concat(mid(pi.id_image,5,1),'/'),''), pi.id_image, '.jpg' )) SEPARATOR ','), '') AS url_image,
0 AS `Delete existing images (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT((fl.`name`), ':', (fvl.`value`), ':' , (f.position), ':' , (fv.custom))) SEPARATOR ',') AS `Feature (Name:Value:Position:Customized)`,
p.online_only AS `Available online only (0 = No, 1 = Yes)`,
p.condition AS `Condition`,
0 AS `Customizable (0 = No, 1 = Yes)`,
0 AS `Uploadable files (0 = No, 1 = Yes)`,
0 AS `Text fields (0 = No, 1 = Yes)`,
'' AS `Action when out of stock`,
p.id_shop_default AS `ID / Name of shop`,
p.advanced_stock_management AS `Advanced Stock Management`,
'' AS `Depends on stock`,
'' AS `Warehouse`
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_specific_price pr ON (p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON ( pt.id_tag = t.id_tag )
LEFT JOIN ps_image pi ON ( p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON (p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON (p.id_supplier = ps.id_supplier)
LEFT JOIN ps_product_supplier pps ON (p.id_supplier = pps.id_supplier)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
LEFT JOIN ps_configuration conf ON conf.`name` = 'ps_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
WHERE p.id_product = 4587 AND pl.id_lang = 6
AND cl.id_lang = 6
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product

Все срабатывает, но! что то я не так указываю в коде про изображения.
Не формируются ссылки на изображенияКак правильно прописать запрос, чтобы ссылки на все изображения товара формировались в csv?
16 Июль 2018, 12:11:22
Ответ #1
  • Ветеран
  • *****
  • Сообщений: 1242
  • Репутация: +15/-1
  • Сообщество PrestaShop
    • Просмотр профиля
concat( 'http:// или https://', ifnull( conf.value, 'адрес сайта' ) ,
'/img/p/',mid(p.id_product,1,1),'/',
if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),
if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),
if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),
if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''),
p.id_product, '.jpg' ) AS url_image
16 Июль 2018, 13:52:50
Ответ #2
  • Пользователь
  • **
  • Сообщений: 83
  • Репутация: +0/-0
  • Сообщество PrestaShop
    • Просмотр профиля
что то пошло не так 
подскажите, где ошибка?выдает ошибку: FROM не существует.

SELECT
p.id_product AS `ID`,
p.active AS `Active (0/1)`,
pl.`name` AS `Name`,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS `Categories (x,y,z...)`,
p.price AS `Price tax excluded or Price tax included`,
p.id_tax_rules_group AS `Tax rules ID`,
p.wholesale_price AS `Wholesale price`,
p.on_sale AS `On sale (0/1)`,
IF(pr.reduction_type='amount',pr.reduction,'') AS `Discount amount`,
IF(pr.reduction_type='percentage',pr.reduction,'') AS `Discount percent`,
pr.`from` AS `Discount from (yyyy-mm-dd)`,
pr.`to` AS `Discount to (yyyy-mm-dd)`,
p.reference AS `Reference #`,
pps.product_supplier_reference AS `Supplier reference #`,
ps.`name` AS `Supplier`,
pm.`name` AS `Manufacturer`,
p.ean13 AS `EAN13`,
p.upc AS `UPC`,
p.ecotax AS `Ecotax`,
p.width AS `Width`,
p.height AS `Height`,
p.depth AS `Depth`,
p.weight AS `Weight`,
pq.quantity AS `Quantity`,
p.minimal_quantity AS `Minimal quantity`,
p.visibility AS `Visibility`,
p.additional_shipping_cost AS `Additional shipping cost`,
p.unity AS `Unit for the unit price`,
p.unit_price_ratio AS `Unit price`,
pl.description_short AS `Short description`,
pl.description AS `Description`,
IF(t.`name` IS NOT NULL, GROUP_CONCAT(DISTINCT(t.`name`) SEPARATOR ','), '') AS `Tags (x,y,z...)`,
pl.meta_title AS `Meta title`,
pl.meta_keywords AS `Meta keywords`,
pl.meta_description AS `Meta description`,
pl.link_rewrite AS `URL rewritten`,
pl.available_now AS `Text when in stock`,
pl.available_later AS `Text when backorder allowed`,
p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
'' AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,
IF(pi.`id_product` IS NOT NULL, GROUP_CONCAT(DISTINCT(concat( 'http:// или https://', ifnull( conf.value, 'umkarapuz.ru' ) ,
'/img/p/',mid(p.id_product,1,1),'/',
if (length(p.id_product)>1,concat(mid(p.id_product,2,1),'/'),''),
if (length(p.id_product)>2,concat(mid(p.id_product,3,1),'/'),''),
if (length(p.id_product)>3,concat(mid(p.id_product,4,1),'/'),''),
if (length(p.id_product)>4,concat(mid(p.id_product,5,1),'/'),''),
p.id_product, '.jpg' ) AS url_image,
0 AS `Delete existing images (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT((fl.`name`), ':', (fvl.`value`), ':' , (f.position), ':' , (fv.custom))) SEPARATOR ',') AS `Feature (Name:Value:Position:Customized)`,
p.online_only AS `Available online only (0 = No, 1 = Yes)`,
p.condition AS `Condition`,
0 AS `Customizable (0 = No, 1 = Yes)`,
0 AS `Uploadable files (0 = No, 1 = Yes)`,
0 AS `Text fields (0 = No, 1 = Yes)`,
'' AS `Action when out of stock`,
p.id_shop_default AS `ID / Name of shop`,
p.advanced_stock_management AS `Advanced Stock Management`,
'' AS `Depends on stock`,
'' AS `Warehouse`
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_specific_price pr ON (p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON ( pt.id_tag = t.id_tag )
LEFT JOIN ps_image pi ON ( p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON (p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON (p.id_supplier = ps.id_supplier)
LEFT JOIN ps_product_supplier pps ON (p.id_supplier = pps.id_supplier)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
LEFT JOIN ps_configuration conf ON conf.`name` = 'ps_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
WHERE p.id_product = 4587 AND pl.id_lang = 6
AND cl.id_lang = 6
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product
16 Июль 2018, 17:30:42
Ответ #3
  • Пользователь
  • **
  • Сообщений: 83
  • Репутация: +0/-0
  • Сообщество PrestaShop
    • Просмотр профиля
Видимо как обычно, что то не дописал и стандартно нарукожопил.Предложенный запрос не сработал, но вставил в старый запрос адрес сайта и изображения стали добавляться в csvВсе равно, Mistral, спасибо!
И еще вопрос, если у одного товара несколько изображений, в csv ссылки добавляются через запятую, существующий импорт "не съедает" это и просто не импортирует несколько изображений к одному товару. Видимо каждая ссылка на изображение должна находиться в отдельном столбце, заголовок которого должен совпадать с другими столбцами изображений. Подскажите как это сделать?
SELECT
p.id_product AS `ID`,
p.active AS `Active (0/1)`,
pl.`name` AS `Name`,
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS `Categories (x,y,z...)`,
p.price AS `Price tax excluded or Price tax included`,
p.id_tax_rules_group AS `Tax rules ID`,
p.wholesale_price AS `Wholesale price`,
p.on_sale AS `On sale (0/1)`,
IF(pr.reduction_type='amount',pr.reduction,'') AS `Discount amount`,
IF(pr.reduction_type='percentage',pr.reduction,'') AS `Discount percent`,
pr.`from` AS `Discount from (yyyy-mm-dd)`,
pr.`to` AS `Discount to (yyyy-mm-dd)`,
p.reference AS `Reference #`,
pps.product_supplier_reference AS `Supplier reference #`,
ps.`name` AS `Supplier`,
pm.`name` AS `Manufacturer`,
p.ean13 AS `EAN13`,
p.upc AS `UPC`,
p.ecotax AS `Ecotax`,
p.width AS `Width`,
p.height AS `Height`,
p.depth AS `Depth`,
p.weight AS `Weight`,
pq.quantity AS `Quantity`,
p.minimal_quantity AS `Minimal quantity`,
p.visibility AS `Visibility`,
p.additional_shipping_cost AS `Additional shipping cost`,
p.unity AS `Unit for the unit price`,
p.unit_price_ratio AS `Unit price`,
pl.description_short AS `Short description`,
pl.description AS `Description`,
IF(t.`name` IS NOT NULL, GROUP_CONCAT(DISTINCT(t.`name`) SEPARATOR ','), '') AS `Tags (x,y,z...)`,
pl.meta_title AS `Meta title`,
pl.meta_keywords AS `Meta keywords`,
pl.meta_description AS `Meta description`,
pl.link_rewrite AS `URL rewritten`,
pl.available_now AS `Text when in stock`,
pl.available_later AS `Text when backorder allowed`,
p.available_for_order AS `Available for order (0 = No, 1 = Yes)`,
'' AS `Product available date`,
p.date_add `Product creation date`,
p.show_price AS `Show price (0 = No, 1 = Yes)`,
IF(pi.`id_product` IS NOT NULL, GROUP_CONCAT(DISTINCT(concat( 'http://umkarapuz.ru/img/p/',mid(pi.id_image,1,1),'/', if (length(pi.id_image)>1, concat(mid(pi.id_image,2,1),'/'),''),if (length(pi.id_image)>2,concat(mid(pi.id_image,3,1),'/'),''),if (length(pi.id_image)>3,concat(mid(pi.id_image,4,1),'/'),''),if (length(pi.id_image)>4,concat(mid(pi.id_image,5,1),'/'),''), pi.id_image, '.jpg' )) SEPARATOR ','), '') AS url_image,
0 AS `Delete existing images (0 = No, 1 = Yes)`,
GROUP_CONCAT(DISTINCT(CONCAT((fl.`name`), ':', (fvl.`value`), ':' , (f.position), ':' , (fv.custom))) SEPARATOR ',') AS `Feature (Name:Value:Position:Customized)`,
p.online_only AS `Available online only (0 = No, 1 = Yes)`,
p.condition AS `Condition`,
0 AS `Customizable (0 = No, 1 = Yes)`,
0 AS `Uploadable files (0 = No, 1 = Yes)`,
0 AS `Text fields (0 = No, 1 = Yes)`,
'' AS `Action when out of stock`,
p.id_shop_default AS `ID / Name of shop`,
p.advanced_stock_management AS `Advanced Stock Management`,
'' AS `Depends on stock`,
'' AS `Warehouse`
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_specific_price pr ON (p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON ( pt.id_tag = t.id_tag )
LEFT JOIN ps_image pi ON ( p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON (p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON (p.id_supplier = ps.id_supplier)
LEFT JOIN ps_product_supplier pps ON (p.id_supplier = pps.id_supplier)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
LEFT JOIN ps_configuration conf ON conf.`name` = 'ps_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
WHERE p.id_product = 4569 AND pl.id_lang = 6
AND cl.id_lang = 6
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product