Help with sql query. Output last 5 latest orders with product images

[bnadauld]

Can anyone help with creating a sql query in Prestashop's sql manager?

Im trying to create my own Adobe Indesign delivery slip for my latest orders via a csv + data-merge. Ive got some way there but i'm stuck. Has anyone already got this sql query that they could share please? I'd like all the fields used on a standard delivery slip.

My query so far

SELECT o.reference, c.firstname, c.lastname, a.lastname, a.firstname, a.address1, a.address2, a.postcode, a.city, a.phone, a.phone_mobile, o.id_order
FROM ps_orders o
LEFT JOIN ps_customer c on (c.id_customer = o.id_customer)
LEFT JOIN ps_address a on (a.id_address = o.id_address_delivery)
OUTER JOIN  (SELECT  *  from ps_order_detail where id_order = o.id_order limit 1) od1 on (o.id_order = o.id_order)

order by id_order desc limit 5

Some code i borrowed

select
p.`id_product`
,pl.`name`
,concat('https://tinsflowershop.com/',cl.`link_rewrite`,'/',pl.`link_rewrite`,'-',p.`id_product`,'.html') as "ProductURL"
,case
    when length(im.`id_image`)=6 then
     concat('https://tinsflowershop.com/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=5 then
     concat('https://tinsflowershop.com/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat('https://tinsflowershop.com/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat('https://tinsflowershop.com/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat('https://tinsflowershop.com/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat('https://tinsflowershop.com/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  
     
    else ''
    end as "ImgURL_1"
FROM `ps_product` p
join `ps_product_lang` pl on pl.`id_product`= p.`id_product`
join `ps_category_lang` cl on cl.`id_category`= p.`id_category_default`
join `ps_image` im on im.`id_product`= p.`id_product`
where pl.`id_lang`=1 and cl.`id_lang`=1 and im.`cover`=1 and p.`active`=1

Thanks

[bnadauld]

Nice to see the Prestashop support is as good as ever...

[bnadauld]

I'll answer my own question. Note that this wont work if used in prestashop sql manager (thats as BS as the PS support) only use in myphp admin or such like.

SET SQL_BIG_SELECTS=1;
 SELECT 
       o.reference          AS order_reference,
       c.firstname          AS customer_first_name,
       c.lastname,
       a.lastname,
       a.firstname,
       a.address1,
       a.address2,
       a.postcode,
       a.city,
       a.phone,
       a.phone_mobile,
       o.id_order,
       od1.product_id       AS Product1ID,
       prod1.name           AS Product1,
       prod1.url_image      AS Product1Image,
       prod1.ProductURL     AS Product1URL,
       od1.product_quantity AS Product1Quantity,
       od2.product_id       AS Product2ID,
       prod2.name           AS Product2,
       prod2.url_image      AS Product2Image,
       prod2.ProductURL     AS Product2URL,
       od2.product_quantity AS Product2Quantity,
       od3.product_id       AS Product3ID,
       prod3.name           AS Product3,
       prod3.url_image      AS Product3Image,
       prod3.ProductURL     AS Product3URL,
       od3.product_quantity AS Product3Quantity,
       od4.product_id       AS Product4ID,
       prod4.name           AS Product4,
       prod4.url_image      AS Product4Image,
       prod4.ProductURL     AS Product4URL,
       od4.product_quantity AS Product4Quantity,
       od5.product_id       AS Product5ID,
       prod5.name           AS Product5,
       prod5.url_image      AS Product5Image,
       prod5.ProductURL     AS Product5URL,
       od5.product_quantity AS Product5Quantity,
       od6.product_id       AS Product6ID,
       prod6.name           AS Product6,
       prod6.url_image      AS Product6Image,
       prod6.ProductURL     AS Product6URL,
       od6.product_quantity AS Product6Quantity,
       o.payment,
       o.invoice_date
       FROM   ps_orders o
       LEFT JOIN ps_customer c
              ON ( c.id_customer = o.id_customer )
       LEFT JOIN ps_address a
              ON ( a.id_address = o.id_address_delivery )
       LEFT JOIN ps_order_detail od1
              ON od1.id_order_detail = (SELECT d.id_order_detail
                                        FROM   ps_order_detail AS d
                                        WHERE  o.id_order = d.id_order
                                        ORDER  BY d.id_order_detail
                                        LIMIT  1)
       LEFT JOIN ps_order_detail od2
              ON od2.id_order_detail = (SELECT d.id_order_detail
                                        FROM   ps_order_detail AS d
                                        WHERE  o.id_order = d.id_order
                                        ORDER  BY d.id_order_detail
                                        LIMIT  1, 1)
       LEFT JOIN ps_order_detail od3
              ON od3.id_order_detail = (SELECT d.id_order_detail
                                        FROM   ps_order_detail AS d
                                        WHERE  o.id_order = d.id_order
                                        ORDER  BY d.id_order_detail
                                        LIMIT  2, 1)
       LEFT JOIN ps_order_detail od4
              ON od4.id_order_detail = (SELECT d.id_order_detail
                                        FROM   ps_order_detail AS d
                                        WHERE  o.id_order = d.id_order
                                        ORDER  BY d.id_order_detail
                                        LIMIT  3, 1)
       LEFT JOIN ps_order_detail od5
              ON od5.id_order_detail = (SELECT d.id_order_detail
                                        FROM   ps_order_detail AS d
                                        WHERE  o.id_order = d.id_order
                                        ORDER  BY d.id_order_detail
                                        LIMIT  4, 1)
       LEFT JOIN ps_order_detail od6
              ON od6.id_order_detail = (SELECT d.id_order_detail
                                        FROM   ps_order_detail AS d
                                        WHERE  o.id_order = d.id_order
                                        ORDER  BY d.id_order_detail
                                        LIMIT  5, 1)

       LEFT JOIN (SELECT p.id_product         AS 'ID',
                         pl.name              AS 'Name',
        Concat('{ImagePath}', pl.`link_rewrite`, '.jpg') AS "ProductURL",
       Concat('https://your_url.com/img/p/',
       Mid(im.id_image, 1, 1), '/',
              IF
       (
              Length(im.id_image) > 1, Concat(Mid(im.id_image, 2, 1), '/'), ''), IF
(
Length(im.id_image) > 2, Concat(Mid(im.id_image, 3, 1), '/'), ''), IF
(Length(im.id_image) > 3,
Concat(Mid(im.id_image, 4, 1), '/'), ''),
IF (Length(im.id_image) > 4, Concat(Mid(im.id_image, 5, 1), '/'), ''),
im.id_image, '.jpg') AS url_image
    FROM   ps_product p
           INNER JOIN ps_product_lang pl
                   ON p.id_product = pl.id_product
           LEFT JOIN ps_image im
                  ON p.id_product = im.id_product


    WHERE  1 = 1
           AND p.active = 1) prod1
ON od1.product_id = prod1.id

LEFT JOIN (SELECT p.id_product         AS 'ID',
           pl.name              AS 'Name',
           Concat('{ImagePath}', pl.`link_rewrite`, '.jpg') AS "ProductURL",
           Concat('https://your_url.com/img/p/',
           Mid(im.id_image, 1, 1), '/',
IF (
Length(im.id_image) > 1, Concat(Mid(im.id_image, 2, 1), '/'), ''), IF
(
Length(im.id_image) > 2, Concat(Mid(im.id_image, 3, 1), '/'), ''), IF
(Length(im.id_image) > 3,
Concat(Mid(im.id_image, 4, 1), '/'), ''),
IF (Length(im.id_image) > 4, Concat(Mid(im.id_image, 5, 1), '/'), ''),
im.id_image, '.jpg') AS url_image
    FROM   ps_product p
           INNER JOIN ps_product_lang pl
                   ON p.id_product = pl.id_product
           LEFT JOIN ps_image im
                  ON p.id_product = im.id_product
    WHERE  1 = 1
           AND p.active = 1) prod2
ON od2.product_id = prod2.id
LEFT JOIN (SELECT p.id_product         AS 'ID',
           pl.name              AS 'Name',
           Concat('{ImagePath}', pl.`link_rewrite`, '.jpg') AS "ProductURL",
           Concat('https://your_url.com/img/p/',
           Mid(im.id_image, 1, 1), '/',
IF (
Length(im.id_image) > 1, Concat(Mid(im.id_image, 2, 1), '/'), ''), IF
(
Length(im.id_image) > 2, Concat(Mid(im.id_image, 3, 1), '/'), ''), IF
(Length(im.id_image) > 3,
Concat(Mid(im.id_image, 4, 1), '/'), ''),
IF (Length(im.id_image) > 4, Concat(Mid(im.id_image, 5, 1), '/'), ''),
im.id_image, '.jpg') AS url_image
    FROM   ps_product p
           INNER JOIN ps_product_lang pl
                   ON p.id_product = pl.id_product
           LEFT JOIN ps_image im
                  ON p.id_product = im.id_product
    WHERE  1 = 1
           AND p.active = 1) prod3
ON od3.product_id = prod3.id
LEFT JOIN (SELECT p.id_product         AS 'ID',
           pl.name              AS 'Name',
           Concat('{ImagePath}', pl.`link_rewrite`, '.jpg') AS "ProductURL",
           Concat('https://your_url.com/img/p/',
           Mid(im.id_image, 1, 1), '/',
IF (
Length(im.id_image) > 1, Concat(Mid(im.id_image, 2, 1), '/'), ''), IF
(
Length(im.id_image) > 2, Concat(Mid(im.id_image, 3, 1), '/'), ''), IF
(Length(im.id_image) > 3,
Concat(Mid(im.id_image, 4, 1), '/'), ''),
IF (Length(im.id_image) > 4, Concat(Mid(im.id_image, 5, 1), '/'), ''),
im.id_image, '.jpg') AS url_image
    FROM   ps_product p
           INNER JOIN ps_product_lang pl
                   ON p.id_product = pl.id_product
           LEFT JOIN ps_image im
                  ON p.id_product = im.id_product
    WHERE  1 = 1
           AND p.active = 1) prod4
ON od4.product_id = prod4.id
LEFT JOIN (SELECT p.id_product         AS 'ID',
           pl.name              AS 'Name',
           Concat('{ImagePath}', pl.`link_rewrite`, '.jpg') AS "ProductURL",
           Concat('https://your_url.com/img/p/',
           Mid(im.id_image, 1, 1), '/',
IF (
Length(im.id_image) > 1, Concat(Mid(im.id_image, 2, 1), '/'), ''), IF
(
Length(im.id_image) > 2, Concat(Mid(im.id_image, 3, 1), '/'), ''), IF
(Length(im.id_image) > 3,
Concat(Mid(im.id_image, 4, 1), '/'), ''),
IF (Length(im.id_image) > 4, Concat(Mid(im.id_image, 5, 1), '/'), ''),
im.id_image, '.jpg') AS url_image
    FROM   ps_product p
           INNER JOIN ps_product_lang pl
                   ON p.id_product = pl.id_product
           LEFT JOIN ps_image im
                  ON p.id_product = im.id_product
    WHERE  1 = 1
           AND p.active = 1) prod5
ON od5.product_id = prod5.id
LEFT JOIN (SELECT p.id_product         AS 'ID',
           pl.name              AS 'Name',
          Concat('{ImagePath}', pl.`link_rewrite`, '.jpg') AS "ProductURL",
           Concat('https://your_url.com/img/p/',
           Mid(im.id_image, 1, 1), '/',
IF (
Length(im.id_image) > 1, Concat(Mid(im.id_image, 2, 1), '/'), ''), IF
(
Length(im.id_image) > 2, Concat(Mid(im.id_image, 3, 1), '/'), ''), IF
(Length(im.id_image) > 3,
Concat(Mid(im.id_image, 4, 1), '/'), ''),
IF (Length(im.id_image) > 4, Concat(Mid(im.id_image, 5, 1), '/'), ''),
im.id_image, '.jpg') AS url_image
    FROM   ps_product p
           INNER JOIN ps_product_lang pl
                   ON p.id_product = pl.id_product
           LEFT JOIN ps_image im
                  ON p.id_product = im.id_product
    WHERE  1 = 1
           AND p.active = 1) prod6
ON od6.product_id = prod6.id
GROUP  BY o.id_order
ORDER  BY o.id_order DESC
LIMIT  5