Edit History

In case others stumble upon this post in the future, I have expanded upon what Eutanasio put together.

This SQL Query for Prestashop 1.7.8.5 will export a complete list of all orders with extensive data including Order ID, Reference, Invoice, Customer Phone, Email, Name, Address, ID, Registration Date, Payment, Transaction ID, Order Date, Shipping Number, Delivery Date, and Message.

In other words, this is a comprehensive SQL Query to export all of your orders with significant information needed for record keeping.

SELECT
    o.id_order AS order_id,
    o.reference AS order_reference,
    o.invoice_number AS invoice_number,
    c.id_customer AS customer_id,
    c.date_add AS registration_date,
    c.firstname AS customer_firstname,
    c.lastname AS customer_lastname,
    a.phone,
    c.email AS email,
    a.address1,
    a.address2,
    a.postcode,
    a.city,
    cl.name AS country,
    s.name AS state,
    GROUP_CONCAT(CONCAT(od.product_reference, ' (Qty: ', od.product_quantity, ')') SEPARATOR '; ') AS product_reference,
    GROUP_CONCAT(CONCAT(od.product_name, ' (Ref: ', od.product_reference, ', Qty: ', od.product_quantity, ', Price: ', od.product_price, ')') SEPARATOR '; ') AS product_details,
    o.total_shipping_tax_incl AS shipping_cost,
    o.total_wrapping_tax_incl AS wrapping_cost,
    payment.payment_details,
    op.transaction_id AS transaction_id,
    o.total_paid_tax_excl,
    o.total_paid_tax_incl,
    (o.total_paid_tax_incl - o.total_products_wt - o.total_shipping_tax_incl - o.total_wrapping_tax_incl) AS order_discount,
    o.date_add AS order_date,
    o.shipping_number AS shipping_number,
    o.delivery_date AS delivery_date,
    m.message AS message
FROM
    ps_orders o
JOIN
    ps_customer c ON o.id_customer = c.id_customer
JOIN
    ps_address a ON o.id_address_invoice = a.id_address
JOIN
    ps_country_lang cl ON a.id_country = cl.id_country AND o.id_lang = cl.id_lang
LEFT JOIN
    ps_state s ON a.id_state = s.id_state
JOIN
    ps_order_detail od ON o.id_order = od.id_order
LEFT JOIN
    (
        SELECT
            order_reference,
            GROUP_CONCAT(CONCAT(payment_method, ': ', amount) SEPARATOR '; ') AS payment_details
        FROM
            ps_order_payment
        GROUP BY
            order_reference
    ) AS payment ON o.reference = payment.order_reference
LEFT JOIN
    ps_order_payment op ON o.id_order = op.id_order_payment
LEFT JOIN
    ps_message m ON o.id_order = m.id_order
GROUP BY
    o.id_order
ORDER BY
    o.id_order;