Consulta de selección SQL para recuperar elementos de pedido y metadatos de WooCommerce

7 minutos de lectura

Estoy trabajando en la creación de algunas vistas SQL para mi base de datos de WordPress para ver más fácilmente los pedidos en línea generados a través del complemento WooCommerce. Estoy familiarizado con la estructura de WooCommerce y dónde se almacenan los datos, pero como soy un novato en SQL, me estoy quedando atascado en el último obstáculo.

Pude crear la primera vista de todos los detalles del pedido usando esta referencia:

http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-incluidos-metadatos/

Ahora necesito crear una vista similar de las líneas de pedido para cada uno de estos pedidos. Hay 2 tablas que almacenan los detalles del artículo del pedido:

wp_woocommerce_order_items wp_woocommerce_order_itemmeta

Pude crear la lista básica de registros de la tabla wp_woocommerce_order_items de la siguiente manera:

SELECT order_item_id,order_item_name,order_item_type,order_id
FROM ocm_woocommerce_order_items 
ORDER BY order_id

Esto devuelve una buena lista de registros como este:

order_item_id   order_item_name order_item_type order_id
 2  Widgets line_item   9
 3  Widgets line_item   10
 4  Widgets line_item   11
 5  Woo Logo    line_item   473
 6  Woo Logo    line_item   473

Ahora me gustaría agregar columnas adicionales para cada elemento de línea de la tabla wp_woocommerce_order_itemmeta. Esta tabla contiene varios registros para cada elemento de línea como este:

 meta_id    order_item_id   meta_key    meta_value
 136    16  _qty    4
 137    16  _tax_class  
 138    16  _product_id 87
 139    16  _variation_id   0
 140    16  _line_subtotal  36
 141    16  _line_total 36
 142    16  _line_subtotal_tax  3.6
 143    16  _line_tax   3.6

Me gustaría convertir cada uno de estos en una nueva columna para que mi salida consista en:

 order_item_id, order_item_name, order_item_type, order_id, qty, tax_class, product_id, variation_id, line_subtotal, line_total, line_subtotal_tax, line_tax

No estoy seguro de qué sintaxis SQL usar aquí: he intentado usar una sintaxis similar al enlace anterior, pero hasta ahora no he podido hacer que funcione y no estoy seguro de si esa es la sintaxis correcta.

Me las arreglé para resolver esto por prueba y error. Aquí está la consulta que funcionó al final:

select
p.order_id,
p.order_item_id,
p.order_item_name,
p.order_item_type,
max( CASE WHEN pm.meta_key = '_product_id' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as productID,
max( CASE WHEN pm.meta_key = '_qty' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as Qty,
max( CASE WHEN pm.meta_key = '_variation_id' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as variationID,
max( CASE WHEN pm.meta_key = '_line_total' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as lineTotal,
max( CASE WHEN pm.meta_key = '_line_subtotal_tax' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as subTotalTax,
max( CASE WHEN pm.meta_key = '_line_tax' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as Tax,
max( CASE WHEN pm.meta_key = '_tax_class' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as taxClass,
max( CASE WHEN pm.meta_key = '_line_subtotal' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as subtotal
from
ocm_woocommerce_order_items as p,
ocm_woocommerce_order_itemmeta as pm
 where order_item_type="line_item" and
 p.order_item_id = pm.order_item_id
 group by
p.order_item_id

  • Gracias. cómo conseguir customer_id campo con campos superiores?

    – Mostafá Norzade

    20 de mayo de 2019 a las 14:16


  • Deberá consultar el Pedido en sí, que incluirá el ID_cliente para cada pedido, por lo que consultará el wp_posts mesa directamente para esto

    – usuario982124

    21 mayo 2019 a las 23:49

  • cantidad que viene vacía :/

    – Diego Ponciano

    11 de febrero de 2020 a las 23:24

  • En cada max la p.order_item_id = pm.order_item_id condición parece innecesaria porque esta condición ya está en p y pm en el where cláusula.

    – jcalfee314

    30 de julio a las 17:19


Puede utilizar esta consulta para obtener todos los pedidos completados, incluidos todos los metadatos.

SELECT
        p.ID as order_id,
        p.post_date,
        max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
        max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
        max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
        max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
        max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
        max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
        max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
        max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
        max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
        max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
        max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
        max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
        max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
        max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
        max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
        max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
        max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
        max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
        ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
    FROM
        wp_posts p 
        join wp_postmeta pm on p.ID = pm.post_id
        join wp_woocommerce_order_items oi on p.ID = oi.order_id
    WHERE
        post_type="shop_order" and
        post_date BETWEEN '2021-07-01' AND '2021-08-01' and
        post_status="wc-completed"
    group by
        p.ID

  • Gracias por compartir: ¡he estado jugando con esto durante algún tiempo para obtener productos para los pedidos que se están procesando actualmente!

    – cmptrwizard

    7 sep 2021 a las 18:27

@mostafa Norzade No tengo comentarios previos, así que publicar como respuesta

la meta_clave para user_id es ‘_customer_user’, así que simplemente agregue lo siguiente: max( CASE WHEN pm.meta_key = ‘_customer_user ‘ and p.ID = pm.post_id THEN pm.meta_value END ) como customer_user,

  • no funciona… ¿puedes reescribir la consulta completa?

    – Mariuta Mihai Dan

    12 de febrero de 2021 a las 22:44

¿Ha sido útil esta solución?

Esta web utiliza cookies propias y de terceros para su correcto funcionamiento y para fines analíticos y para mostrarte publicidad relacionada con sus preferencias en base a un perfil elaborado a partir de tus hábitos de navegación. Al hacer clic en el botón Aceptar, acepta el uso de estas tecnologías y el procesamiento de tus datos para estos propósitos. Configurar y más información
Privacidad