WARNING: This is not discussing a WooCommerce report I’ve written, this is raw SQL. If you are not a developer, this isn’t the post you are looking for.
A Short History of Cal
Back in the early ’90s I was working for my parents company as a programmer. I wrote a report once that would scan the entire order system and list out the customers who had purchased a specific product. At that time, and in our industry, this was revolutionary. We actually had vendors calling us asking us to run it for various reasons. (None of them spam, this was pre-email)
Fast forward to today and I still need this information. These days however, I don’t have to sequentially scan hundreds of files of order history to find the information. We’ve got SQl and RDBMS to help with that. it should be easy, right? Easy is such a relative term.
For Nomad PHP, we use WooComemrce to handle all order processing. WooCommerce uses WordPress’s internal structure for most of it’s data storage. This means that things like First Name, Last Name, and Email Address are buried in a field named meta_value in the wp_postmeta table. This means that you lose all the advantage to storing things in a RDBMS. Still, I need this data.
Just Give Me the Code
It turns out, it is possible to get this data out of the system if you are patient and willing to write some incredibly ugly SQL. Still, it was fun (for me) since it’s been a long time since I’ve hand-coded a pivot table. (What we in the FoxPro world used to call a Cross Tab)
Here’s the code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
DROP TABLE IF EXISTS cal_holding; SELECT @SKU := "YOUR ITEM'S SKU"; SELECT @SKU_ID := post_id FROM wp_postmeta WHERE meta_key="_sku" AND meta_value=@SKU ; CREATE TABLE cal_holding AS SELECT order_id, meta_key, meta_value FROM wp_postmeta LEFT JOIN (SELECT order_id FROM wp_woocommerce_order_items `items` LEFT JOIN (SELECT order_item_id FROM wp_woocommerce_order_itemmeta WHERE meta_key='_product_id' AND meta_value=@SKU_ID) AS `orders` ON `items`.order_item_id = `orders`.order_item_id WHERE orders.order_item_id IS NOT NULL) AS `all_orders` ON all_orders.order_id = wp_postmeta.post_id WHERE order_id IS NOT NULL AND (wp_postmeta.meta_key='_billing_email' OR wp_postmeta.meta_key='_billing_first_name' OR wp_postmeta.meta_key='_billing_last_name'); SELECT h0.order_id, h1.meta_value AS email, h2.meta_value AS first_name, h3.meta_value AS last_name FROM cal_holding h0 INNER JOIN cal_holding h1 ON h0.order_id=h1.order_id AND h1.meta_key='_billing_email' INNER JOIN cal_holding h2 ON h0.order_id=h2.order_id AND h2.meta_key='_billing_first_name' INNER JOIN cal_holding h3 ON h0.order_id=h3.order_id AND h3.meta_key='_billing_last_name' GROUP BY order_id; DROP TABLE IF EXISTS cal_holding;
Now It’s Time for the Breakdown
I’m not going to break it down line by line but I’ll hit the highlights.
Line 1 just makes sure that the holding table doesn’t already exist.
Line 2 is very important. Where it says YOUR ITEM’S SKU, put in you item’s SKU.
Lines 7-26 create a table called
cal_holding that holds the first name, last name, and email address of everyone who has purchased the SKU. This info is usable but not in the format we want. all the data is contained in individual rows and it’s still a pain to work with. There are 3 selects in that statement and two of them are sub-selects for joins. It is not pretty. However, if you run the first two select statements (lines 2 and 4) you can run the selects individually to see what they produce.
Lines 28-36 create our pivot table. We take the data stored in the field meta_value and move it into fields named more appropriately, first_name, last_name and email. We use the holding table but we use three times. Then we group on the order id to give us a single row for each order, that contains each of the three fields.
This is not a scalable solution. If you’ve got more than about 5 fields, this gets unwieldy fast. There are articles out there on how to automate this but they required prepared statements and I just didn’t feel like doing that.
While it is possible to execute this in the MySQL cli client, I would strongly recommend you use a better tool. I like SequelPro for macOS and HeidiSQL for Windows. Both of these are excellent tools, both are free, (but you should donate) and both will make it much easier for you to play with this code and tinker a bit.
The above code is released to the public domain. I claim no copyright on it at all. If you want to use it in a plugin that puts a button on a product that runs this and let’s me export the results to CSV, I’m fine with that. If you want to charge for it, I’m fine with that. If you want to give me a copy for free, I’M FINE WITH THAT.
I wrote it mainly because I’m tired of asking the question “In WooCommerce, how do I…?” and being told that the answer is, “I’m going to need your credit card number…every year.”
Until next time,
I <3 |<