Export extra product option with SQL requestion

  • Resolved Aastone

    (@aastone)


    2 years ago

    Hi, i’m trying to make a sql request to get the extra product options for each orders but the request is returning NULL for each value

    select
    p.ID as order_id,

    max( CASE WHEN pm.meta_key = 'transportation' and p.ID = pm.post_id THEN pm.meta_value END ) as accompagnamento,
    max( CASE WHEN pm.meta_key = 'trasporto_locale' and p.ID = pm.post_id THEN pm.meta_value END ) as oyster_card,
    max( CASE WHEN pm.meta_key = 'trasporto_locale_2' and p.ID = pm.post_id THEN pm.meta_value END ) as oyster_card,
    max( CASE WHEN pm.meta_key = 'activities' and p.ID = pm.post_id THEN pm.meta_value END ) as attivita_extra,


    ( 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_status = 'wc-processing'
group by
    p.ID

    I tried to put the name of each options as meta_key but it’s not working and i can see that most of my clients took options or each orders, can you help me here?

Viewing 1 replies (of 1 total)
  • Plugin Author ThemeHigh

    (@themehigh)

    You can retrieve order item meta (wepo product options) using below code.

    $order_id = 758; // Replace with the ID of the order you want to retrieve the order item meta from.
    
// Get the order items for the order.
$order = wc_get_order( $order_id ); 
$order_items = $order->get_items();

foreach($order_items as $item_id => $item){
    wc_get_order_item_meta($item_id, 'test', true); //Relace test with field name

}

    If you want to retrieve the order item meta (wepo product options) using SQL query then the order item meta is available in wp_woocommerce_order_itemmeta table.?

    SELECT meta_value
FROM wp_woocommerce_order_itemmeta
WHERE order_item_id = ‘item_id’
  AND meta_key = ‘field_name’

    ‘Item_id’ ?is the ID of the order item for which you want to retrieve the data, and ‘field_name’ ?is the name of the metadata field you want to retrieve(wepo field name).

    Thank you!

Viewing 1 replies (of 1 total)
  • The topic ‘Export extra product option with SQL requestion’ is closed to new replies.