Consulta mySQL de Woocommerce: lista de todos los pedidos, usuarios y artículos comprados

5 minutos de lectura

avatar de usuario
Millas

Tengo una consulta mySQL completamente funcional que extrae todos los pedidos, usuarios, direcciones y artículos comprados en Woocommerce; sin embargo, solo enumera los productos individualmente y me gustaría agregar la cantidad de cada producto que se muestra.

Actualmente muestra ‘Artículos pedidos’
Zapatos para correr
Zapatos para caminar

Donde debería mostrar ‘Artículos pedidos’
3 zapatillas para correr.
4 zapatos para caminar.

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  MAX( CASE WHEN pm.meta_key = '_billing_email'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Email Address',
  MAX( CASE WHEN pm.meta_key = '_billing_first_name'  AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'First Name',
  MAX( CASE WHEN pm.meta_key = '_billing_last_name'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Last Name',
  MAX( CASE WHEN pm.meta_key = '_billing_address_1'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Address',
  MAX( CASE WHEN pm.meta_key = '_billing_city'        AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'City',
  MAX( CASE WHEN pm.meta_key = '_billing_state'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'State',
  MAX( CASE WHEN pm.meta_key = '_billing_postcode'    AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Post Code',
    CASE p.post_status
      WHEN 'wc-pending'    THEN 'Pending Payment'
      WHEN 'wc-processing' THEN 'Processing'
      WHEN 'wc-on-hold'    THEN 'On Hold'
      WHEN 'wc-completed'  THEN 'Completed'
      WHEN 'wc-cancelled'  THEN 'Cancelled'
      WHEN 'wc-refunded'   THEN 'Refunded'
      WHEN 'wc-failed'     THEN 'Failed'
    ELSE 'Unknown'
    END AS 'Purchase Status',
  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 = '_paid_date'           AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Paid Date',
  ( select group_concat( order_item_name separator '</p>' ) FROM wp_woocommerce_order_items where order_id = p.ID ) AS 'Items Ordered'
FROM  wp_posts AS p
JOIN  wp_postmeta AS pm ON p.ID = pm.post_id
JOIN  wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE post_type="shop_order"
GROUP BY p.ID

Creo que Woocommerce almacena la CANTIDAD en la siguiente tabla/entradas:

wp_woocommerce_order_itemmeta
  order_item_id
    SELECT wp_woocommerce_order_itemmeta.meta_value
    WHERE wp_woocommerce_order_itemmeta.meta_value="_qty" and wp_woocommerce_order_itemmeta.order_item_id =

Y necesito unirme de alguna manera a esta sección:

( select group_concat( order_item_name separator '</p>' ) FROM wp_woocommerce_order_items where order_id = p.ID ) AS 'Items Ordered'

Gracias por adelantado.

ACTUALIZADO CON RESPUESTA DE: Lucek

Gracias Lucek, absolutamente perfecto.

He combinado la consulta completa en caso de que alguien más quiera copiarla.

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  MAX( CASE WHEN pm.meta_key = '_billing_email'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Email Address',
  MAX( CASE WHEN pm.meta_key = '_billing_first_name'  AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'First Name',
  MAX( CASE WHEN pm.meta_key = '_billing_last_name'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Last Name',
  MAX( CASE WHEN pm.meta_key = '_billing_address_1'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Address',
  MAX( CASE WHEN pm.meta_key = '_billing_city'        AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'City',
  MAX( CASE WHEN pm.meta_key = '_billing_state'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'State',
  MAX( CASE WHEN pm.meta_key = '_billing_postcode'    AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Post Code',
    CASE p.post_status
      WHEN 'wc-pending'    THEN 'Pending Payment'
      WHEN 'wc-processing' THEN 'Processing'
      WHEN 'wc-on-hold'    THEN 'On Hold'
      WHEN 'wc-completed'  THEN 'Completed'
      WHEN 'wc-cancelled'  THEN 'Cancelled'
      WHEN 'wc-refunded'   THEN 'Refunded'
      WHEN 'wc-failed'     THEN 'Failed'
    ELSE 'Unknown'
    END AS 'Purchase Status',
  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 = '_paid_date'           AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Paid Date',
  ( SELECT GROUP_CONCAT(CONCAT(m.meta_value, ' x ', i.order_item_name) separator '</br>' )
    FROM wp_woocommerce_order_items i
    JOIN wp_woocommerce_order_itemmeta m ON i.order_item_id = m.order_item_id AND meta_key = '_qty'
    WHERE i.order_id = p.ID AND i.order_item_type="line_item") AS 'Items Ordered',
  MAX( CASE WHEN pm.meta_key = 'post_excerpt'         AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'User Comments'
FROM  wp_posts AS p
JOIN  wp_postmeta AS pm ON p.ID = pm.post_id
JOIN  wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE post_type="shop_order"
GROUP BY p.ID

  • ¡Qué dios manda! Salud

    – usuario2389087

    12 oct 2020 a las 13:52

Debe reemplazar la sección Artículos pedidos con esto:

( SELECT GROUP_CONCAT(CONCAT(m.meta_value, ' x ', i.order_item_name) separator '</p>' )
FROM da_woocommerce_order_items i
JOIN da_woocommerce_order_itemmeta m ON i.order_item_id = m.order_item_id AND meta_key = '_qty'
WHERE i.order_id = p.ID AND i.order_item_type="line_item") AS 'Items Ordered'

Puede cambiar el separador entre el nombre del producto y la cantidad en la función CONCAT, ahora es ‘x’. también agrego i.order_item_type="line_item" Cláusula to where: impide obtener envíos, tarifas y cupones. Si lo necesita todo en su consulta, simplemente elimínelo.

  • Gracias Lucek, absolutamente perfecto.

    – Millas

    1 de noviembre de 2018 a las 1:48

  • Útil. ¿Una modificación menor también podría permitirle crear un archivo .csv para ser importado por Calc, Excel, etc.?

    – ajaaskel

    1 oct 2019 a las 12:53


  • Puede generar csv con PHP, cuando ejecuta esta consulta. ¿Es eso lo que estás buscando?

    – Lucek

    15 oct 2019 a las 6:40

¿Ha sido útil esta solución?