Product URL and feature via SQL query

[jobybär]

Hello everyone,
I have long been searching in google and the Presta forum for a SQL query of the Product URL for a CSV export.

I'm not fit in SQL queries and spellings.
Maybe someone can help me.

In the back office the product URL is deffiniert from me.
{category:/}{rewrite}{-:ean13}-{id}.html
https://landmuecke.eu/mystery-magic/elfe-mit-drachen-und-stock-mit-glaskugel-4052286741104-10.html
So I just need a concatenation like in code.
But do not get it going. Also with other spelling clinch or for example (pl.'id_product')

Even the features assigned to the product I do not get read.
There are too many links of tables necessary.
I just want to read out the feature value only of  the id_feature = 9

Select

GROUP_CONCAT((conf.name),'-',(cl.link_rewrite),'-',(p.ean13),'-',(p.id_product),'.html',) AS `Product_URL`,

IF(fl.id_future=9, fvl.value) As 'Farbe'

FROM ps_product p 
       
LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
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_future f ON ...............
LEFT JOIN ps_future_value_lang fvl ON ........

WHERE p.active=1
AND   pl.id_lang = 1
AND   pl.id_shop = 1
GROUP BY p.id_product

 

Edited December 28, 2017 by jobybär (see edit history)

[musicmaster]

I am puzzled what you want to do. But there is one thing I know for sure: this won't work if you don't use the ps_feature_product table.

[jobybär]

Sorry for my english to understand. I'm german.

The code is only a part of the complete SQL query I need.
My information

IF(fl.id_future=9, fvl.value) As 'Farbe'

should only show the scheme which I need.

I have written that some tables are needed for the feature query line.
I do not get the necessary connection of the tables to each other.

Needed are certainly the tables
ps_feature_lang
ps_feature_product
ps_feature_value
ps_feature_value_lang

I do not get the link of each table to represent the following.

I need two columns in my CSV file.
1.) Colum AS COLOR (Farbe)
  The id_feature of the feature.name (color) = 9

2.) Colum AS Material (Material)
  The id_feature of the feature.name (color) = 10

The result of the CSV file should look like this.

id_prodact | Farbe | Material
1001             |yelow  |Wood
1002            |gold    |Resin
1003            |red      |Glas
1004            |silver   |Resin

As I said, that's just part of the required SQL query.
Here's the whole scope if it matters.
All lines commented out with (#) are still missing in the implementation.

Select
       p.reference As 'Artikelnummer_SKU',
       p.ean13 As 'EAN', 
       pl.name As 'Produktname', 
       p.ladenzeile_kat As 'Hauptkategorie', 	-- No Prestashop standard fields - only in my database
       p.ladenzeile_ukat1 As 'Unterkategorie',  -- No Prestashop standard fields - only in my database
       p.ladenzeile_ukat2 As 'Unterkategorie2', -- No Prestashop standard fields - only in my database
      
#  Feature Color      id_feature =9    ps_feture_value_lang   As 'Farbe',
#  Feature Material   id_feature =10   ps_feture_value_lang  As 'Material',

#  Hersteller       ps_manufacturer.name  As 'Marke',



#  Product URL like https://landmuecke.eu/tiere/storch-sitzend-mit-baby-10-cm-hoch-4058766200147-3646.html  AS 'Deeplink URL',

#  pl.description_shor und  pl.description summed up in a column without any HTML tags     As 'Produktbeschreibung',
       pl.description_short As 'Kurz_Beschreibung', 
       pl.description As 'Produktbeschreibung',
 
       Round((p.price *1.19),2) AS 'Preis',

# optional Old price if the price lnk. Tax is available with discount    (Preis_ink._tax / Ps_specific_price.reduktion)   As  'Alter Preis',

       cu.iso_code      As 'Währung',
       pq.quantity As 'Menge',
       pl.available_now As 'Verfügbarkeit',
       col.value  AS 'Lieferzeit',

#  Shipping costs id_carrier = 2 ; id_range_price = 2 ; id_zone = 9  AS 'Versandkosten',
       
       p.condition As 'Zustand',
 
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi.id_image,
        -- put the image extension
        '.jpg') as "Bild URL",
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi2.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi2.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi2.id_image) >= 4, CONCAT(SUBSTRING(pi2.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 3, CONCAT(SUBSTRING(pi2.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi2.id_image) >= 2, CONCAT(SUBSTRING(pi2.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi2.id_image) >= 1, CONCAT(SUBSTRING(pi2.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi2.id_image,
        -- put the image extension
        '.jpg') as "AUX Bild URL 1",  
   
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi3.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi3.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi3.id_image) >= 4, CONCAT(SUBSTRING(pi3.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi3.id_image) >= 3, CONCAT(SUBSTRING(pi3.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi3.id_image) >= 2, CONCAT(SUBSTRING(pi3.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi3.id_image) >= 1, CONCAT(SUBSTRING(pi3.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi3.id_image,
        -- put the image extension
        '.jpg') "AUX Bild URL 2",   
   
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi4.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi4.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi4.id_image) >= 4, CONCAT(SUBSTRING(pi4.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi4.id_image) >= 3, CONCAT(SUBSTRING(pi4.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi4.id_image) >= 2, CONCAT(SUBSTRING(pi4.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi4.id_image) >= 1, CONCAT(SUBSTRING(pi4.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi4.id_image,
        -- put the image extension
        '.jpg') as "AUX Bild URL 3",   
   
CONCAT('https://',
        -- get the shop domain
        IFNULL(conf.value, 'undefined_domain'),
        -- the path to the pictures folder
        '/img/p/',
        -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
        -- assuming we have smaller image id than 100'000 ;)
        IF(CHAR_LENGTH(pi5.id_image) >= 5, 
            -- if we have 5 digits for the image id
            CONCAT(
                -- take the first digit
                SUBSTRING(pi5.id_image, -5, 1),
                -- add a slash
                '/'),
            ''),
        -- repeat for the next digits
        IF(CHAR_LENGTH(pi5.id_image) >= 4, CONCAT(SUBSTRING(pi5.id_image, -4, 1), '/'), ''),
        IF(CHAR_LENGTH(pi5.id_image) >= 3, CONCAT(SUBSTRING(pi5.id_image, -3, 1), '/'), ''),
        if(CHAR_LENGTH(pi5.id_image) >= 2, CONCAT(SUBSTRING(pi5.id_image, -2, 1), '/'), ''),
        IF(CHAR_LENGTH(pi5.id_image) >= 1, CONCAT(SUBSTRING(pi5.id_image, -1, 1), '/'), ''),
        -- add the image id
        pi5.id_image,
        -- put the image extension
        '.jpg') as "AUX Bild URL 4"
       
FROM ps_product p 
       
LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product and pi.cover = 1)
LEFT JOIN ps_image pi2 ON(p.id_product = pi2.id_product and pi2.position = 2)
LEFT JOIN ps_image pi3 ON(p.id_product = pi3.id_product and pi3.position = 3)
LEFT JOIN ps_image pi4 ON(p.id_product = pi4.id_product and pi4.position = 4)
LEFT JOIN ps_image pi5 ON(p.id_product = pi5.id_product and pi5.position = 5)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
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_specific_price sp ON(p.id_product = pl.id_product)
INNER JOIN ps_currency cu
INNER JOIN ps_configuration_lang col ON (col.id_configuration = 511)
   
WHERE p.active=1
AND   pl.id_lang = 1
AND   pl.id_shop = 1
GROUP BY p.id_product;

 

[musicmaster]

I don't have that whole code for getting feature values at hand. But I would use embedded queries for this.

You should get something like:

SELECT ....,f9.value,f10.value FROM 
LEFT JOIN ...
LEFT JOIN ...
LEFT JOIN (SELECT fvl.value,fp.id_product FROM ... LEFT JOIN ... LEFT JOIN ... WHERE fv.id_feature = 9) f9 ON fp.id_product=p.id_product
LEFT JOIN (SELECT fvl.value,fp.id_product FROM ... LEFT JOIN ... LEFT JOIN ... WHERE fv.id_feature = 10) f10 ON fp.id_product=p.id_product