WooCommerce - Datamodel: verschil tussen versies
Regel 106: | Regel 106: | ||
# | # | ||
# * Value of 1 unit expressed in euros | # * Value of 1 unit expressed in euros | ||
− | # * | + | # * Use 3 significant numbers |
− | # | + | # * Date is the month of the most recent update of the exchange rate |
+ | # * The second date was another recent update: To get an impression of | ||
+ | # fluctuations | ||
+ | # * Quickest way to update exchange rates (so far): Just go to | ||
+ | # https://www.xe.com/currencyconverter/convert/?Amount=1&From=AUD&To=EUR and | ||
+ | # change the "from" currency through the browser interface | ||
+ | |||
+ | |||
+ | # aud | ||
+ | ######################################## | ||
# | # | ||
− | + | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=AUD&To=EUR | |
− | set @ | + | # * 2022.06: 0,667 |
− | set @ | + | # * 2022.08: 0,684 +2,5% |
− | set @ | + | # * 2022.10: 0.643 |
− | set @ | + | # * 2022.12: 0.635 |
− | set @ | + | # |
− | set @ | + | set @aud=0.635; |
− | set @ | + | |
− | set @ | + | |
− | set @ | + | # cad |
− | set @ | + | ######################################## |
− | set @ | + | # |
− | set @ | + | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CAD&To=EUR |
− | set @ | + | # * 2022.06: 0,743 |
− | set @ | + | # * 2022.08: 0,763 +2,7% |
− | set @ | + | # * 2022.10: 0.742 |
+ | # * 2022.12: 0.688 | ||
+ | # | ||
+ | # set @cad=0.688; | ||
+ | set @cad=0.742; | ||
+ | |||
+ | |||
+ | # chf | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CHF&To=EUR | ||
+ | # * 2022.06: 0,966 | ||
+ | # * 2022.08: 1,03 +6,6% | ||
+ | # * 2022.10: 1,02 | ||
+ | # * 2022.12: 1.01 | ||
+ | # | ||
+ | set @chf=1.01; | ||
+ | |||
+ | |||
+ | # cny | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CNY&To=EUR | ||
+ | # * 2022.06: 0,141 | ||
+ | # * 2022.08: 0,144 +2,1% | ||
+ | # * 2022.10: 0,141 | ||
+ | # * 2022.12: 0.135 | ||
+ | # | ||
+ | set @cny=0.135; | ||
+ | |||
+ | |||
+ | # gbp | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=GBP&To=EUR | ||
+ | # * 2022.06: 1.19 | ||
+ | # * 2022.08: 1.19 +/-0% | ||
+ | # * 2022.10: 1.15 | ||
+ | # * 2022.12: 1.13 | ||
+ | # | ||
+ | set @gbp=1.13; | ||
+ | |||
+ | |||
+ | # czk | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CZK&To=EUR | ||
+ | # * 2022.06: 0,0405 | ||
+ | # * 2022.08: 0,0406 +0,2% | ||
+ | # * 2022.10: 0,0407 | ||
+ | # * 2022.12: 0.0413 | ||
+ | # | ||
+ | set @czk=0.0413; | ||
+ | |||
+ | |||
+ | # dkk | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=DKK&To=EUR | ||
+ | # * 2022.06: 0.134 | ||
+ | # * 2022.08: 0.134 | ||
+ | # * 2022.10: 0.134 | ||
+ | # * 2022.12: 0.134 | ||
+ | # | ||
+ | set @dkk=0.134; | ||
+ | |||
+ | |||
+ | # inr | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=INR&To=EUR | ||
+ | # * 2022.06: 0.0122 | ||
+ | # * 2022.08: 0.0123 +8% | ||
+ | # * 2022.10: 0.0123 | ||
+ | # * 2022.12: 0.0113 -10% | ||
+ | # | ||
+ | set @inr=0.0113; | ||
+ | |||
+ | |||
+ | # jpy | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=JPY&To=EUR | ||
+ | # * 2022.06: 0.00743 | ||
+ | # * 2022.08: 0.00739 -9,9% | ||
+ | # * 2022.10: 0.00683 | ||
+ | # * 2022.12: 0.00711 +4,1% | ||
+ | # | ||
+ | set @jpy=0.00711; | ||
+ | |||
+ | |||
+ | # ngn | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=NGN&To=EUR | ||
+ | # * 2022.06: 0.00230 | ||
+ | # * 2022.08: 0.00234 +1,7% | ||
+ | # * 2022.10: 0.00234 | ||
+ | # * 2022.12: 0.00208 | ||
+ | # | ||
+ | set @ngn=0.00208; | ||
+ | |||
+ | |||
+ | # nzd | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=NZD&To=EUR | ||
+ | # * 2022.06: 0.603 | ||
+ | # * 2022.08: 0.618 + 2,5% | ||
+ | # * 2022.10: 0.580 | ||
+ | # * 2022.12: 0.592 + 2% | ||
+ | # | ||
+ | set @nzd=0.592; | ||
+ | |||
+ | |||
+ | # pln | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=PLN&To=EUR | ||
+ | # * 2022.06: 0.215 | ||
+ | # * 2022.08: 0.212 - 1,4% | ||
+ | # * 2022.10: 0.209 | ||
+ | # * 2022.12: 0.213 - delta = (0.213-0.209)/0.209 = +1,9% | ||
+ | # | ||
+ | set @pln=0.213; | ||
+ | |||
+ | |||
+ | # sek | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=SEK&To=EUR | ||
+ | # * 2022.06: 0.0952 | ||
+ | # * 2022.08: 0.0965 + 1,4% | ||
+ | # * 2022.10: 0.0913 | ||
+ | # * 2022.12: 0.0895 | ||
+ | # | ||
+ | set @sek=0.0895; | ||
+ | |||
+ | |||
+ | # try | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=TRY&To=EUR | ||
+ | # * 2022.06: 0.0596 | ||
+ | # * 2022.08: 0.0543 - 9% | ||
+ | # * 2022.10: 0.0550 | ||
+ | # * 2022.12: 0.0499 - Delta = ((499-550)/550)*100% = -9,3% | ||
+ | # | ||
+ | set @try=0.0550; | ||
+ | |||
+ | |||
+ | # uah | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=UAH&To=EUR | ||
+ | # * 2022.06: 0.0324 | ||
+ | # * 2022.08: 0.0265 -18% | ||
+ | # * 2022.10: 0.0277 | ||
+ | # * 2022.12: 0.0253 - Delta = -8,7% | ||
+ | # | ||
+ | set @uah=0.0253; | ||
+ | |||
+ | |||
+ | # usd | ||
+ | ######################################## | ||
+ | # | ||
+ | # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=USD&To=EUR | ||
+ | # * 2022.06: 0.953 | ||
+ | # * 2022.08: 0.975 +2,3% | ||
+ | # * 2022.10: 1.02 | ||
+ | # * 2022.11: 0.933 -9% | ||
+ | # | ||
+ | set @usd=0.933; | ||
Versie van 3 jan 2023 15:57
The datamodel of WooCommerce and how to manipulate stuff through SQL. This article functions as a data snippet repository.
Orders & order lines
An impression. There are considerably more fields available, but this goes a long way. Also note the self-joins:
################################################################################ # Get quite some order data ################################################################################ # select ID, post_date, post_type, post_status, wp_woocommerce_order_items.order_item_id, wp_woocommerce_order_items.order_item_type, wp_woocommerce_order_items.order_item_name, itemmeta_01.meta_value as sellprice_ex_vat, itemmeta_02.meta_value as product_post_id, wp_postmeta.meta_value as sku from # wp_posts: Basic order stuff ######################################## # wp_posts join # wp_woocommerce_order_items: Various ######################################## # wp_woocommerce_order_items on wp_posts.ID = wp_woocommerce_order_items.order_id join # wp_woocommerce_order_items (1): sellprice ######################################## # # First of several self-joins # wp_woocommerce_order_itemmeta as itemmeta_01 on wp_woocommerce_order_items.order_item_id = itemmeta_01.order_item_id join # wp_woocommerce_order_items (2): product_id ######################################## # # Product_id is used for retrieving sku further on # wp_woocommerce_order_itemmeta as itemmeta_02 on wp_woocommerce_order_items.order_item_id = itemmeta_02.order_item_id join # wp_postmeta: sku ######################################## # # Finally: Here's the sku! # wp_postmeta on wp_postmeta.post_id = itemmeta_02.meta_value where post_type = "shop_order" and post_status = "wc-completed" and itemmeta_01.meta_key = "_line_subtotal" and itemmeta_02.meta_key = "_product_id" and wp_postmeta.meta_key = "_sku";
Revenue per month
# # Read-out monthly revenue figures webshops server dvb12 ################################################################################ # # # Output ######################################## # # * First figure: "Sales" - "Verkoopbedrag" # * Second figure: "Revenue" - "Omzet" # # # Recommendations ######################################## # # * Develop similar script for sites on server "dvb13" # * Use the same associate array loop as now used for site maintenance # + use WP-CLI as SQL-wrapper # # ################################################################################ # Exchange rates ################################################################################ # # * Value of 1 unit expressed in euros # * Use 3 significant numbers # * Date is the month of the most recent update of the exchange rate # * The second date was another recent update: To get an impression of # fluctuations # * Quickest way to update exchange rates (so far): Just go to # https://www.xe.com/currencyconverter/convert/?Amount=1&From=AUD&To=EUR and # change the "from" currency through the browser interface # aud ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=AUD&To=EUR # * 2022.06: 0,667 # * 2022.08: 0,684 +2,5% # * 2022.10: 0.643 # * 2022.12: 0.635 # set @aud=0.635; # cad ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CAD&To=EUR # * 2022.06: 0,743 # * 2022.08: 0,763 +2,7% # * 2022.10: 0.742 # * 2022.12: 0.688 # # set @cad=0.688; set @cad=0.742; # chf ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CHF&To=EUR # * 2022.06: 0,966 # * 2022.08: 1,03 +6,6% # * 2022.10: 1,02 # * 2022.12: 1.01 # set @chf=1.01; # cny ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CNY&To=EUR # * 2022.06: 0,141 # * 2022.08: 0,144 +2,1% # * 2022.10: 0,141 # * 2022.12: 0.135 # set @cny=0.135; # gbp ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=GBP&To=EUR # * 2022.06: 1.19 # * 2022.08: 1.19 +/-0% # * 2022.10: 1.15 # * 2022.12: 1.13 # set @gbp=1.13; # czk ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CZK&To=EUR # * 2022.06: 0,0405 # * 2022.08: 0,0406 +0,2% # * 2022.10: 0,0407 # * 2022.12: 0.0413 # set @czk=0.0413; # dkk ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=DKK&To=EUR # * 2022.06: 0.134 # * 2022.08: 0.134 # * 2022.10: 0.134 # * 2022.12: 0.134 # set @dkk=0.134; # inr ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=INR&To=EUR # * 2022.06: 0.0122 # * 2022.08: 0.0123 +8% # * 2022.10: 0.0123 # * 2022.12: 0.0113 -10% # set @inr=0.0113; # jpy ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=JPY&To=EUR # * 2022.06: 0.00743 # * 2022.08: 0.00739 -9,9% # * 2022.10: 0.00683 # * 2022.12: 0.00711 +4,1% # set @jpy=0.00711; # ngn ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=NGN&To=EUR # * 2022.06: 0.00230 # * 2022.08: 0.00234 +1,7% # * 2022.10: 0.00234 # * 2022.12: 0.00208 # set @ngn=0.00208; # nzd ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=NZD&To=EUR # * 2022.06: 0.603 # * 2022.08: 0.618 + 2,5% # * 2022.10: 0.580 # * 2022.12: 0.592 + 2% # set @nzd=0.592; # pln ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=PLN&To=EUR # * 2022.06: 0.215 # * 2022.08: 0.212 - 1,4% # * 2022.10: 0.209 # * 2022.12: 0.213 - delta = (0.213-0.209)/0.209 = +1,9% # set @pln=0.213; # sek ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=SEK&To=EUR # * 2022.06: 0.0952 # * 2022.08: 0.0965 + 1,4% # * 2022.10: 0.0913 # * 2022.12: 0.0895 # set @sek=0.0895; # try ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=TRY&To=EUR # * 2022.06: 0.0596 # * 2022.08: 0.0543 - 9% # * 2022.10: 0.0550 # * 2022.12: 0.0499 - Delta = ((499-550)/550)*100% = -9,3% # set @try=0.0550; # uah ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=UAH&To=EUR # * 2022.06: 0.0324 # * 2022.08: 0.0265 -18% # * 2022.10: 0.0277 # * 2022.12: 0.0253 - Delta = -8,7% # set @uah=0.0253; # usd ######################################## # # * https://www.xe.com/currencyconverter/convert/?Amount=1&From=USD&To=EUR # * 2022.06: 0.953 # * 2022.08: 0.975 +2,3% # * 2022.10: 1.02 # * 2022.11: 0.933 -9% # set @usd=0.933; ################################################################################ # Set variable for month & year ################################################################################ # # Set to last month ######################################## # # set @month=date_format(now(),"%m")-1; # set @month=if(@month=0,12,@month); # set @year=date_format(now(),"%Y"); # set @year=if(@month=12,@year-1,@year); # Set manually ######################################## # set @month=5; set @year=2022; ################################################################################ # 4 - example_ch ################################################################################ # select "example_ch" as webshop, round(@chf*sum(wp_postmeta_order_total.meta_value),2) as sales, round(@chf*sum(round(wp_postmeta_order_total.meta_value - wp_postmeta_order_shipping.meta_value - wp_postmeta_order_shipping_vat.meta_value - wp_postmeta_vat.meta_value, 2)),2) as subtotal from example_ch.wp_posts join example_ch.wp_postmeta as wp_postmeta_order_shipping on wp_posts.ID = wp_postmeta_order_shipping.post_id join example_ch.wp_postmeta as wp_postmeta_order_shipping_vat on wp_posts.ID = wp_postmeta_order_shipping_vat.post_id join example_ch.wp_postmeta as wp_postmeta_order_total on wp_posts.ID = wp_postmeta_order_total.post_id join example_ch.wp_postmeta as wp_postmeta_vat on wp_posts.ID = wp_postmeta_vat.post_id where post_type = "shop_order" and post_status = "wc-completed" and wp_postmeta_order_total.meta_key="_order_total" and wp_postmeta_vat.meta_key="_order_tax" and wp_postmeta_order_shipping.meta_key="_order_shipping" and wp_postmeta_order_shipping_vat.meta_key="_order_shipping_tax" and date_format(post_date, "%Y")=@year and date_format(post_date, "%m")=@month ################################################################################ # 5 - example_uk ################################################################################ # union select "example_uk" as webshop, round(@gbp*sum(wp_postmeta_order_total.meta_value),2) as sales, round(@gbp*sum(round(wp_postmeta_order_total.meta_value - wp_postmeta_order_shipping.meta_value - wp_postmeta_order_shipping_vat.meta_value - wp_postmeta_vat.meta_value, 2)),2) as subtotal from example_uk.wp_posts join example_uk.wp_postmeta as wp_postmeta_order_shipping # for "subtotal" on wp_posts.ID = wp_postmeta_order_shipping.post_id join example_uk.wp_postmeta as wp_postmeta_order_shipping_vat # for "subtotal" on wp_posts.ID = wp_postmeta_order_shipping_vat.post_id join example_uk.wp_postmeta as wp_postmeta_order_total # For "sales" & "subtotal" on wp_posts.ID = wp_postmeta_order_total.post_id join example_uk.wp_postmeta as wp_postmeta_vat # For "subtotal" on wp_posts.ID = wp_postmeta_vat.post_id where post_type = "shop_order" and post_status = "wc-completed" and wp_postmeta_order_total.meta_key="_order_total" and wp_postmeta_vat.meta_key="_order_tax" and wp_postmeta_order_shipping.meta_key="_order_shipping" and wp_postmeta_order_shipping_vat.meta_key="_order_shipping_tax" and date_format(post_date, "%Y")=@year and date_format(post_date, "%m")=@month # Etc.
See also Ordertotalen (WooCommerce).
Revenue per product
################################################################################ # Calculate revenue per product ################################################################################ # select wp_postmeta.meta_value as sku, wp_woocommerce_order_items.order_item_name, sum(itemmeta_01.meta_value) as product_revenue_ex_vat from # This table is only used to select the right related data # wp_posts join wp_woocommerce_order_items on wp_posts.ID = wp_woocommerce_order_items.order_id join # Revenue # wp_woocommerce_order_itemmeta as itemmeta_01 on wp_woocommerce_order_items.order_item_id = itemmeta_01.order_item_id join # product-post-id # wp_woocommerce_order_itemmeta as itemmeta_02 on wp_woocommerce_order_items.order_item_id = itemmeta_02.order_item_id join # wp_postmeta: sku ######################################## # # Finally: Here's the sku! # wp_postmeta on wp_postmeta.post_id = itemmeta_02.meta_value where post_type = "shop_order" and post_status = "wc-completed" and itemmeta_01.meta_key = "_line_subtotal" and wp_postmeta.meta_key = "_sku" group by sku order by product_revenue_ex_vat desc;
Product data - Short variant
WooCommerce product information is mainly found in two places [1]:
- wp_posts
- wp_postmeta
Example: Select the key data from wp_posts and the SKUs from wp_postmeta:
select wp_posts.ID as post_id, wp_posts.post_title as post_title, wp_posts.post_name as slug, wp_posts.post_content as content, # =product description wp_postmeta.meta_value as sku from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id where post_type="product" and post_status="publish" and meta_key="_sku";
Product data - With sku & sku_oem
select wp_posts.ID as post_id, wp_posts.post_title as post_title, wp_postmeta.meta_value as sku, wp_postmeta_02.meta_value as sku_oem from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id join wp_postmeta as wp_postmeta_02 on wp_posts.ID = wp_postmeta_02.post_id where post_type="product" and post_status="publish" and wp_postmeta.meta_key="_sku" and wp_postmeta_02.meta_key="sku_oem";
Product data - With sku, sku_oem & prices
In this case, both _price
and _regular_price
contains the same price. However, I think only one of these fields is actually used (I don't know which one right now).
select wp_posts.ID as post_id, wp_posts.post_title as post_title, wp_postmeta.meta_value as sku, wp_postmeta_02.meta_value as sku_oem, wp_postmeta_03.meta_value as price from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id join wp_postmeta as wp_postmeta_02 on wp_posts.ID = wp_postmeta_02.post_id join wp_postmeta as wp_postmeta_03 on wp_posts.ID = wp_postmeta_03.post_id where post_type="product" and post_status="publish" and wp_postmeta.meta_key="_sku" and wp_postmeta_02.meta_key="sku_oem" and wp_postmeta_02.meta_value="1824" and ( wp_postmeta_03.meta_key="_price" or wp_postmeta_03.meta_key="_regular_price" );
With these two price-related fields both included in the output:
select wp_posts.ID as post_id, wp_posts.post_title as post_title, wp_postmeta.meta_value as sku, wp_postmeta_02.meta_value as sku_oem, wp_postmeta_03.meta_value as price, wp_postmeta_04.meta_value as price_regular from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id join wp_postmeta as wp_postmeta_02 on wp_posts.ID = wp_postmeta_02.post_id join wp_postmeta as wp_postmeta_03 on wp_posts.ID = wp_postmeta_03.post_id join wp_postmeta as wp_postmeta_04 on wp_posts.ID = wp_postmeta_04.post_id where post_type="product" and post_status="publish" and wp_postmeta.meta_key="_sku" and wp_postmeta_02.meta_key="sku_oem" and wp_postmeta_03.meta_key="_price" and wp_postmeta_04.meta_key="_regular_price";
Product data - Long variant
- Includes primary and secondary image, price + explanation, but without taxonomic information
- MISSING: Short product description of Yoast (in its own Yoast table)
- Note that most of the constraints on the self-joins, are included as relations, not as WHERE-clauses. This might be less confusing.
# # Example: Retrieve all product data ################################################################################ # use wp_tmp; # # ################################################################################ # Select ################################################################################ # select wp_posts.id as post_id, wp_posts.post_title as post_title, wp_posts.post_name as slug, wp_posts.post_content as content, wp_posts.guid as guid, wp_postmeta.meta_value as sku, wp_postmeta_03.meta_value as thumbnail_path, wp_postmeta_05.meta_value as second_image_path, wp_postmeta_06.meta_value as price from wp_posts # Join 01 on wp_postmet: "sku" ############################################################ # left join wp_postmeta on wp_posts.id = wp_postmeta.post_id # Join 02 on wp_postmeta: "_thumbnail_id" ############################################################ # # "_thumbnail_id" on its turn, is a wp_postmeta.product_id # left join wp_postmeta as wp_postmeta_02 on wp_postmeta_02.post_id = wp_posts.id and wp_postmeta_02.meta_key = "_thumbnail_id" # Join 03 on wp_postmeta: thumbnail-path ############################################################ # # This join is different from the two below, as it is a # double join: It retrieves the meta_id for the thumbnail- # attachment # left join wp_postmeta as wp_postmeta_03 on wp_postmeta_03.post_id = wp_postmeta_02.meta_value and wp_postmeta_03.meta_key = "_wp_attached_file" # Join 04 on wp_postmeta: "_product_image_gallery" ############################################################ # # Retrieve the meta_value for "_product_image_gallery": # It is the post_id of everything related to the second # image # left join wp_postmeta as wp_postmeta_04 on wp_postmeta_04.post_id = wp_posts.id and wp_postmeta_04.meta_key = "_product_image_gallery" # Join 05 on wp_postmeta: "_product_image_gallery" ############################################################ # # Retrieve the meta_value for "_product_image_gallery": # It is the post_id of everything related to the second # image # left join wp_postmeta as wp_postmeta_05 on wp_postmeta_05.post_id = wp_postmeta_04.meta_value and wp_postmeta_05.meta_key = "_wp_attached_file" # Join 06 on wp_postmeta: "_price" ############################################################ # # "_price" and "_regular_price" seem always identical → Only # fetch the first one # left join wp_postmeta as wp_postmeta_06 on wp_postmeta_06.post_id = wp_posts.id and wp_postmeta_06.meta_key = "_price" # Where clause ############################################################ # where post_type="product" and post_status="publish" and wp_postmeta.meta_key="_sku" # Limit ############################################################ # limit 0, 10;
Product data - Figuring out missing images
Missing data from gMC was imported into table missing_image
. How this was connected to existing data:
# # Missing images - Overview, diagnosis, etc. ################################################################################ # # ################################################################################ # Select db ################################################################################ # use tmp; ################################################################################ # Retrieve all info ################################################################################ # select missing_image.title as gmc_title, missing_image.image_link as gmc_image_url, missing_image.file_name as gmc_file_name, wp_posts.ID as wp_post_id, concat("https://example.com/",wp_posts.post_name) as wp_product_url, wp_postmeta_sku.meta_value as wp_sku, wp_postmeta_sku_oem.meta_value as wp_sku_oem, concat("https://example.com/wp-content/uploads/",wp_postmeta_thumbnail_path.meta_value) as wp_image_url from missing_image join wp_posts on missing_image.title = wp_posts.post_title join wp_postmeta as wp_postmeta_sku on wp_posts.ID = wp_postmeta_sku.post_id join wp_postmeta as wp_postmeta_sku_oem on wp_posts.ID = wp_postmeta_sku_oem.post_id join # # Retrieve _thumbnail_id # wp_postmeta as wp_postmeta_thumbnail_id on wp_posts.ID = wp_postmeta_thumbnail_id.post_id join # # Retrieve _thumbnail-path # wp_postmeta as wp_postmeta_thumbnail_path on wp_postmeta_thumbnail_path.post_id = wp_postmeta_thumbnail_id.meta_value where wp_postmeta_sku.meta_key = "_sku" and wp_postmeta_sku_oem.meta_key = "sku_oem" and wp_postmeta_thumbnail_id.meta_key = "_thumbnail_id" and wp_postmeta_thumbnail_path.meta_key = "_wp_attached_file";
Shop-page widgets
The widgets/filters on the shop page are coded in table wp_options
in a few fields with serialised content:
select option_value from wp_options where option_name = "widget_woocommerce_product_categories" # Category-widget select option_value from wp_options where option_name = "widget_woocommerce_layered_nav" # Attribute-widgets
Order adresses
- Order IDs can be found in table
wp_posts
- Address details can be found in table
wp_postmeta
.
Example:
# Retrieve a list of all countries to which there were orders ################################################################################ # # ################################################################################ # Select database ################################################################################ # use knl; ################################################################################ # All countries to which there have been orders ################################################################################ # select distinct meta_value from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id where post_type = "shop_order" and meta_key like "%country";