How can I export an excel of my orders with the product title column? [SOLVED]

[monicaamb]

 

Right now I have this query where I have added the column to know the SKU also:

Sorry for the picture but PrestaShop forum does not allow me to put the query in text.

How can I get the product title column?

Edited January 12, 2023 by monicaamb (see edit history)

[Prestachamps]

Hi,
You need to add to the query another join to the "w5kn_product_lang" table ON id_product from where you can get the product names. 
I hope that I could help.
Have a nice day, Leo.

[monicaamb]

On 1/9/2023 at 3:11 PM, Prestachamps said:

Hi,
You need to add to the query another join to the "w5kn_product_lang" table ON id_product from where you can get the product names. 
I hope that I could help.
Have a nice day, Leo.

Thank you very much for your help. I am trying to put that but sorry I don't know how to do add that.

Could you help me?

This is what I have for the moment:

Edited January 12, 2023 by monicaamb (see edit history)

[ps8modules]

Hi,
I would like to help you, but it is still not clear to me what you need.
There can be multiple products in the order, so there would be multiple lines in the CSV with the same id and order reference. The order detail is in the order_detal table and there is also the name + attributes of the purchased product.

[monicaamb]

10 minutes ago, prestashopfree.com said:

Hi,
I would like to help you, but it is still not clear to me what you need.
There can be multiple products in the order, so there would be multiple lines in the CSV with the same id and order reference. The order detail is in the order_detal table and there is also the name + attributes of the purchased product.

Thank you for the reply.

What I would like is to export somehow the orders with the product or product the client has bought. 

I realised I can do that by making a query in the DB but the columns of SKU and TITLE OF THE PRODUCT didn't appear. So I already put the column of the SKU although I don't khow how is going to show if the client buy more than one product in an order. On the other hand I am looking for the query to obtain the title of the product.

Edited January 12, 2023 by monicaamb (see edit history)

[ps8modules]

SELECT pl.name as product_name, ......

LEFT JOIN w5kn_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = so.id_lang)

 

Edited January 12, 2023 by prestashopfree.com (see edit history)

[monicaamb]

7 hours ago, prestashopfree.com said:

SELECT pl.name as product_name, ......

LEFT JOIN w5kn_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = so.id_lang)

 

Muchas gracias. 

 

I have added what you tell me there, is the position correct?

On the other hand, I don't understand where I have to put the SELECT pl.name as...

Edited January 12, 2023 by monicaamb (see edit history)

[ps8modules]

In your SELECT at the beginning of the SQL query, you insert pl.name as product_name.

You placed the LEFT JOIN correctly.

How to insert SQL into the forum?
You click on <>

and choose SQL at the bottom.

When several dots are used in the text, it means that there is a continuation after these dots (in your case, your SQL).

Edited January 12, 2023 by prestashopfree.com (see edit history)

[monicaamb]

SELECT CONCAT(LEFT(cu.`firstname`, 1), '. ', cu.`lastname`) AS `customer`, o.id_order, o.reference, o.total_paid_tax_incl, os.paid, osl.name AS osname, o.id_currency, cur.iso_code, o.current_state, o.id_customer, cu.`id_customer` IS NULL as `deleted_customer`, os.color, o.payment, s.name AS shop_name, o.date_add, cu.company, cl.name AS country_name, o.invoice_number, o.delivery_number, (SELECT IF(count(so.id_order) > 0, 0, 1) FROM w5kn_orders so WHERE (so.id_customer = o.id_customer) AND (so.id_order < o.id_order) LIMIT 1) AS new, p.reference as sku

FROM w5kn_orders o

LEFT JOIN w5kn_customer cu ON o.id_customer = cu.id_customer

LEFT JOIN w5kn_currency cur ON o.id_currency = cur.id_currency

INNER JOIN w5kn_address a ON o.id_address_delivery = a.id_address

LEFT JOIN w5kn_order_state os ON o.current_state = os.id_order_state

LEFT JOIN w5kn_shop s ON o.id_shop = s.id_shop

INNER JOIN w5kn_country c ON a.id_country = c.id_country

INNER JOIN w5kn_country_lang cl ON c.id_country = cl.id_country AND cl.id_lang = 2

LEFT JOIN w5kn_order_state_lang osl ON os.id_order_state = osl.id_order_state AND osl.id_lang = 2

INNER JOIN w5kn_order_detail od ON o.id_order = od.id_order

INNER JOIN w5kn_product p ON od.product_id = p.id_product

LEFT JOIN w5kn_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = so.id_lang)

WHERE o.`id_shop` IN ('1')

ORDER BY o.id_order DESC

LIMIT 50

 

[ps8modules]

SELECT 
    CONCAT(LEFT(cu.firstname, 1), '. ', cu.lastname) AS customer, 
    o.id_order, 
    o.reference, 
    o.total_paid_tax_incl, 
    os.paid, 
    osl.name AS osname, 
    o.id_currency, 
    cur.iso_code, 
    o.current_state, 
    o.id_customer, 
    cu.deleted AS deleted_customer,
    os.color, 
    o.payment, 
    s.name AS shop_name, 
    o.date_add, 
    cu.company, 
    cl.name AS country_name, 
    o.invoice_number, 
    o.delivery_number, 
    (SELECT 
        IF(count(so.id_order) > 0, 0, 1) 
        FROM w5kn_orders so 
        WHERE (so.id_customer = o.id_customer) AND (so.id_order < o.id_order) LIMIT 1) 
    AS new, 
    p.reference as sku,
    pl.name AS product_name

FROM w5kn_orders o

LEFT JOIN w5kn_customer cu ON (o.id_customer = cu.id_customer)

LEFT JOIN w5kn_currency cur ON (o.id_currency = cur.id_currency)

INNER JOIN w5kn_address a ON (o.id_address_delivery = a.id_address)

LEFT JOIN w5kn_order_state os ON (o.current_state = os.id_order_state)

LEFT JOIN w5kn_shop s ON (o.id_shop = s.id_shop)

INNER JOIN w5kn_country c ON (a.id_country = c.id_country)

INNER JOIN w5kn_country_lang cl ON (c.id_country = cl.id_country AND cl.id_lang = o.id_lang)

LEFT JOIN w5kn_order_state_lang osl ON (os.id_order_state = osl.id_order_state AND osl.id_lang = o.id_lang)

INNER JOIN w5kn_order_detail od ON (o.id_order = od.id_order)

INNER JOIN w5kn_product p ON (od.product_id = p.id_product)

LEFT JOIN w5kn_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = o.id_lang)

WHERE o.id_shop IN ('1')

ORDER BY o.id_order DESC

LIMIT 50;

 

[monicaamb]

3 hours ago, prestashopfree.com said:

SELECT 
    CONCAT(LEFT(cu.firstname, 1), '. ', cu.lastname) AS customer, 
    o.id_order, 
    o.reference, 
    o.total_paid_tax_incl, 
    os.paid, 
    osl.name AS osname, 
    o.id_currency, 
    cur.iso_code, 
    o.current_state, 
    o.id_customer, 
    cu.deleted AS deleted_customer,
    os.color, 
    o.payment, 
    s.name AS shop_name, 
    o.date_add, 
    cu.company, 
    cl.name AS country_name, 
    o.invoice_number, 
    o.delivery_number, 
    (SELECT 
        IF(count(so.id_order) > 0, 0, 1) 
        FROM w5kn_orders so 
        WHERE (so.id_customer = o.id_customer) AND (so.id_order < o.id_order) LIMIT 1) 
    AS new, 
    p.reference as sku,
    pl.name AS product_name

FROM w5kn_orders o

LEFT JOIN w5kn_customer cu ON (o.id_customer = cu.id_customer)

LEFT JOIN w5kn_currency cur ON (o.id_currency = cur.id_currency)

INNER JOIN w5kn_address a ON (o.id_address_delivery = a.id_address)

LEFT JOIN w5kn_order_state os ON (o.current_state = os.id_order_state)

LEFT JOIN w5kn_shop s ON (o.id_shop = s.id_shop)

INNER JOIN w5kn_country c ON (a.id_country = c.id_country)

INNER JOIN w5kn_country_lang cl ON (c.id_country = cl.id_country AND cl.id_lang = o.id_lang)

LEFT JOIN w5kn_order_state_lang osl ON (os.id_order_state = osl.id_order_state AND osl.id_lang = o.id_lang)

INNER JOIN w5kn_order_detail od ON (o.id_order = od.id_order)

INNER JOIN w5kn_product p ON (od.product_id = p.id_product)

LEFT JOIN w5kn_product_lang pl ON (pl.id_product = p.id_product AND pl.id_lang = o.id_lang)

WHERE o.id_shop IN ('1')

ORDER BY o.id_order DESC

LIMIT 50;

 

Perfect.

Thank you very much for share your knowledge