Export extra product option with SQL requestion

Resolved

(@aastone)

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)

3 years, 3 months ago
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.

Tags