Jak vytvořit výpis všech produktů (Presta 1.6)

[Scientific]

Ahoj všem, prosím si od zkušenějších z Vás radu. Jak mohu vytvářet kompletní výpisy z databáze všech produktů?

Prosím nemáte pro mě někdo funkční SQL dotaz, který vrátí seznam všech produktů?

Děkuji moc všem za pomoc.

Našel jsem nějaké, ale ani jeden nefunguje (vrací prázdný výsledek) id_lang jsem upravil:

SELECT
    p.id_product,
    pa.reference,
    pl.name,
    @id_image := ifnull(pai.id_image, pi.id_image) as id_image,
    concat('http://',
            ifnull(shop_domain.value, 'domain'),
            '/img/p/',
            if(CHAR_LENGTH(@id_image) >= 5,
                concat(SUBSTRING(@id_image from - 5 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 4,
                concat(SUBSTRING(@id_image from - 4 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 3,
                concat(SUBSTRING(@id_image from - 3 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 2,
                concat(SUBSTRING(@id_image from - 2 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 1,
                concat(SUBSTRING(@id_image from - 1 FOR 1), '/'),
                ''),
            @id_image,
            '.jpg') as image_url,
    GROUP_CONCAT(DISTINCT (pal.name)
        SEPARATOR ', ') as combination,
    ROUND(p.price, 2) as price,
    p.active,
    pq.quantity
FROM
    ps_product p
        LEFT JOIN
    ps_product_attribute pa ON (p.id_product = pa.id_product)
        LEFT JOIN
    ps_stock_available pq ON (p.id_product = pq.id_product
        AND pa.id_product_attribute = pq.id_product_attribute)
        LEFT JOIN
    ps_product_lang pl ON (p.id_product = pl.id_product)
        LEFT JOIN
    ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
        LEFT JOIN
    ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
        LEFT JOIN
    ps_product_attribute_image pai ON (pa.id_product_attribute = pai.id_product_attribute)
        LEFT JOIN
    ps_image pi ON p.id_product = pi.id_product
        LEFT JOIN
    ps_configuration shop_domain ON shop_domain.name = 'PS_SHOP_DOMAIN'
WHERE
    pl.id_lang = (
        SELECT
            id_lang
        FROM
            ps_lang
        ORDER BY id_lang ASC
        LIMIT 1
    )
        AND
    pal.id_lang = pl.id_lang
GROUP BY pa.reference
ORDER BY p.id_product , pac.id_attribute;

Ani tento:

SELECT
    p.id_product,
    pa.reference,
    pl.name,
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
    p.price,
    pq.quantity
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
WHERE pl.id_lang = 1
AND pal.id_lang = 1
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

 

[Scientific]

Našel jsem dotaz, který mi funguje, ale výsledek nezobrazuje některé důležité informace jako třeba cenu, vat, description and images of products. Prosím, jak požadované sloupce do výpisu přidat? Děkuji moc.
 

SELECT
            p.id_product,
            pa.id_product_attribute as id_product_attribute,
            pa.reference,
            CONCAT(p.supplier_reference, " - ", pa.supplier_reference) as supplier_reference,
            pa.upc,
            pa.ean13,
            p.price,
            pl.name,
            GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
            pq.quantity, pa.weight, p.width, p.depth, p.height
        FROM ps_product p
        LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
        LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
        LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
        LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
        LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
        LEFT JOIN ps_product_attribute_image pai on(pa.id_product_attribute = pai.id_product_attribute)
        WHERE pl.id_lang = 6
        AND pal.id_lang = 6
        GROUP BY pa.reference

        union

        SELECT
            p.id_product,
            "" as id_product_attribute,
            p.reference,
            p.supplier_reference,
            p.upc,
            p.ean13,
            p.price,
            pl.name,
            "" as combination,
            p.quantity, p.weight, p.width, p.depth, p.height
        FROM ps_product p
        LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
        LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
        WHERE pl.id_lang = 6
        GROUP BY p.reference

        ORDER BY id_product, id_product_attribute