WooCommerce 주문 항목 및 메타 데이터를 검색하기 위한 SQL 선택 쿼리

WooCommerce 주문 항목 및 메타 데이터를 검색하기 위한 SQL 선택 쿼리

WooCommerce 플러그인을 통해 생성된 온라인 주문을 보다 쉽게 볼 수 있도록 Wordpress 데이터베이스에 대한 SQL Views를 만드는 작업을 하고 있습니다.저는 WooCommerce 구조와 데이터가 저장되는 위치에 대해 잘 알고 있지만 SQL 초보자인 저는 마지막 장애물에 갇혀 있습니다.

이 참조를 사용하여 모든 주문 세부 정보의 첫 번째 보기를 만들 수 있었습니다.

http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-including-metadata/

이제 나는 이 주문들 각각에 대해 비슷한 라인 항목의 뷰를 작성해야 합니다.주문 품목 정보를 저장하는 테이블은 2개입니다.

wp_wocommerce_order_items wp_wocommerce_order_itemmeta

wp_woocommerce_order_items 테이블에서 다음과 같이 기본적인 레코드 목록을 만들 수 있었습니다.

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

이렇게 하면 다음과 같은 멋진 레코드 목록이 반환됩니다.

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

이제 wp_woocommerce_order_itemmeta 테이블에서 각 줄 항목에 대한 추가 열을 추가하고자 합니다.이 표에는 다음과 같이 각 줄 항목에 대한 여러 레코드가 포함되어 있습니다.

 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

출력이 다음과 같이 구성될 수 있도록 이들 각각을 새 열로 변환하고자 합니다.

 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

여기서 어떤 SQL 구문을 사용해야 할지 잘 모르겠습니다. 위의 링크와 유사한 구문을 사용하려고 했지만 지금까지 이 구문이 제대로 작동하는지 알 수 없었습니다.

저는 시행착오를 통해서 이 문제를 해결할 수 있었습니다.최종적으로 작동한 쿼리는 다음과 같습니다.

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

이 쿼리를 사용하면 모든 메타데이터를 포함하여 완료된 모든 주문을 가져올 수 있습니다.

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

@mostafa Norzade 댓글 다는 것이 없어서 답으로 글을 올립니다.

user_id의 meta_key는 '_customer_user'이므로 max(cASE WHEN pm. meta_key = '_customer_user' 및 p를 추가하기만 하면 됩니다.아이디 = pm.post _id THEN pm. meta_value END)을 customer_user로,

언급URL : https://stackoverflow.com/questions/38743011/sql-select-query-for-retrieving-woocommerce-order-items-and-meta-data