Product Data in Woo

The (free) WooCommerce Product Export facility writes out a CSV file that contain product-related descriptors.

Here is an example csv file showing the 39 fields output by the Product Export facility.

I produced this file in a local host dev env that only contains 1 plug-in: WooCommerce, running on MariaDB 10.3. .

Let’s examine a sample row I created in this sandbox db.

One thing that is already of interest in that the Weight Value (Column 19) of this record is “5..55”.  This was a deliberate typo on my part, and shows that in some cases Woo does not validate input data. (

Basic data validation of this type must therefore be handled at some point in our database load process. Should the entire load fail, when an error of this type is encountered, or should the error be ignored and the record uploaded with an error record added to s log file.

Another item of interest is the second column of this example CSV file.

It has a column of name of Type.  Below is an extended discussion of the naming of this column heading, and how we can find it’s intensional value (via SQL).

Here are the Woo REST API Product properties, which exceed in number those found in the CSV export file.

The  following query (props to Dataeo) is useful to locate where a column exists (ie, in which tables) in Woo’s Information schema.

Just go to your PHPmyadmin SQL tab and type this query (changing values in red!):

select tab.table_schema as database_name, tab.table_name
from information_schema.tables
as tab inner join information_schema.columns
as col on col.table_schema = tab.table_schema
and col.table_name = tab.table_name and column_name = ‘your column name
where tab.table_type = ‘BASE TABLE’
order by tab.table_schema, tab.table_name;

In the Woo CSV Product export file, we may note that the second column heading is called Type.

I  had a very hard time using the above query to locate a column with this name.

There was a very good reason for this:  there is no such column.

To understand how Product Types were modeled, we need to understand what Woo calls a “custom taxonomy.”

Do not confuse this concept with that of a Variable product that has a custom attribute. Because they both contain the word “custom”, this can be misleading.

A variable product is a product that has, well, variations; these can be quite minor, such as color, or major, such as having a Stinkability attribute.

As you can see from the Add New Product panel below, I am here adding a new variant that has a custom attribute STINKABILITY. Some products of this type will stink, other variations will not.  The Woo engine can tell the difference by parsing the binary options provided, which are separated by a verti bar in the Value(s) box.woocommerce add product

Let’s ‘s return now to the mysterious “Type” column (that we see as the 2nd column the Product export CSV file).

To understand it, we can first think of the notion of a plain vanilla post Category.  This is the classification scheme that WP bloggers use to classify post by topic (which can be anything).  Shop owners can also use Category to segment products.

This can be conceptually diagrammed as follows.

Prodiuct classification

The built-in Product Taxonomy is a little different.

There are four (4) provided Product Types that come out of the box with WooCommerce: Simple product, Grouped product, External/affiliate product, and Variable product.

Woo uses the same modeling technique it employs in user-defined Categories to create instances of these built-in Product Types, of which Simple product is by far the most popular.

To see these built-in Product Types associated to their correct Product instance, you need a 3-way inner join:

(1) use column ID from wp-posts as your initial join clause… (ID is a Product’s unique ID number, as assigned in semi random ascending order by PHP code)

→ to a column in wp_term_relationships called object_ID (which is part of a composite PK)

this table is an “intersection table” which resolves the M:N relationship between wp_term_taxonomy and wp_posts)

the second part of its composite key contains a column called term_taxonomy_id., which numerically identifies the  code for a product ID instance

(2) → this column (term_taxonomy_id) is the join clause predicate used to match the intensional value of a column in wp_term_taxonomy. that’s also called term_taxonomy_id,.  This join enables the query to correctly select the term_id (“term” is WP/Woo’s nomenclature for a type of product; in this case, it’s the code used to represent “simple”) associated with this instance of product ID

(3) → join with the wp_terms table, using the term_ID column as the join clause with the term_id PK column in the  wp_terms table. This terminative join delivers the desired result set. In this case, it’s a Product type instance with the value “Simple:” in the Name column

The following diagram illustrates this σisSimple 3-join query:

sql query joins


Let’s look how this works in practice, by using the Add Product panel in WooCommerce. to add a simple product type called Widget 1.

Click or tap to enlarge

And for the moment you’ve all been waiting for, here is the query itself (you can replace the single quotes with double quotes if you encounter problems running this code):

SELECT P.post_name as ‘Product Name’, as ‘Product Type’
FROM wp_posts P
INNER JOIN wp_term_relationships a     (1)
ON (P.ID = a.object_id )
INNER JOIN wp_term_taxonomy b            (2)
ON ( a.term_taxonomy_id = b.term_taxonomy_id )
INNER JOIN wp_terms c                                (3)
ON ( b.term_id = c.term_id )

Here is the result of the query off PHPmyadmin.

sql query result

As you can see there are funky rows that get added to the result set.  This probably has to do with autosave UI behavior. Extraneous detail will later have to scrubbed.