CODEWITHSUNDEEP
mysqlsqlwordpresswoocommerce
Nathan Clark
Nathan Clark

Reputation: 657

Woocommerce SQL query to show products sold this week?

Woocommerce has a reporting tool that will show me the top products sold for the last 7 days. But it only shows the top 12 products.

I am wanting to create a SQL query that will show me all products with their total count sold for the last 7 days instead of just the top 12.

Has anyone done this before?

Upvotes: 4

Views: 3869

Answers (3)

profexorgeek
profexorgeek

Reputation: 1200

WooCommerce borrows heavily from the way WordPress itself stores data: a Post serves as the basic data object with a handful of columns common to all custom posts. Any unique data specific to a custom type is stored as key value pairs in post_meta. This means there aren't clean columns or tables to query for things like order line items, sku, line item price, etc.

It's worth mentioning that for orders, WC does not store products it stores line items. This is because you can add fees, generic line items and possibly other things to an order that are not products. Also, WC needs to store the price at the time of the order as the customer may have had a discount or the product price may have changed.

WooCommerce uses both the WordPress postmeta table AND its own order_itemmeta table. Here's how that breaks down:

  • The order itself is stored as a custom post type of "shop_order" in the wp_posts table
  • Order line items are stored in a relationship table called wp_woocommerce_order_items
  • Order item details are stored as key value pairs in wp_woocommerce_order_itemmeta
  • Finaly order details are stored in the wp_postmeta table

So, let's say you want to see all line items for a period of time and you want to know things like the item title, price and what order it belonged to. To do this you must JOIN multiple tables and either JOIN or subquery the meta tables for the specific fields you want. In the example below I used subqueries because I believe they are more readable, please note that JOINs are very likely faster.

SELECT
-- Choose a few specific columns related to the order
o.ID as order_id,
o.post_date as order_created,

-- These come from table that relates line items to orders
oi.order_item_name as product_name,
oi.order_item_type as item_type,

-- We have to subquery for specific values and alias them. This could also be done as a join
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_product_id") as product_id,
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_product_variation_id") as variant_id,
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_qty") as qty,
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_fee_amount") as fee,
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_line_subtotal") as subtotal,
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_line_subtotal_tax") as tax,
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_line_total") as total,
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_tax_class") as tax_class,
(SELECT meta_value FROM wp_woocommerce_order_itemmeta WHERE order_item_id = oi.order_item_id AND meta_key = "_tax_status") as tax_status,

-- This wasn't specifically mentioned in the question but it might be nice to have some order meta data too
(SELECT meta_value FROM wp_postmeta WHERE post_id = o.ID AND meta_key = "_order_total") as order_total,
(SELECT meta_value FROM wp_postmeta WHERE post_id = o.ID AND meta_key = "_customer_user") as user_id

FROM wp_posts o
LEFT JOIN wp_woocommerce_order_items oi ON oi.order_id = o.id
LEFT JOIN wp_posts p ON p.ID = oi.order_item_id
WHERE o.post_type = "shop_order"

As you can see it takes a subquery/join for every line item field you want making these queries pretty expensive. I suspect that WC limits how much is queried for reports for this reason.

This answer was tested against WC version 3.3.4.

Upvotes: 4

Danijel
Danijel

Reputation: 12709

Instead of writin a new query just modify the existing one with filter: 

woocommerce_reports_get_order_report_query

Is seems that the limit is the same for all parts of the reports page, so changing this will affect all queries with limit clause. I would not go too far with the number of products because a new sql query is executed for each product listed.

add_filter( 'woocommerce_reports_get_order_report_query', function( $query ) 
{
    if ( isset( $query['limit'] ) ) $query['limit'] = 'LIMIT 20';  <-- set limit to 20 products
    return $query;  
});

Upvotes: 0

Mihai
Mihai

Reputation: 26784

Just a guess

....WHERE dateColumn BETWEEN DATE_SUB(NOW(),INTERVAL 1 WEEK) AND NOW()

Add more info,for a more precise answer.

Upvotes: -1

Related Questions