Batch Report Beta

css responsive app
Galaxy S5 view of the report, which is written using CSS flexbox

This is the STOREBOSS financial analysis report I am currently developing.

The purpose of this merchant-facing app is to allow a store owner to upload transactions from a credit card processor to a custom table, and perform various comparative analytics.

Currently, this is not possible to do in WooCommerce.

The app is written in PhP, and uses JS on the front end, and MariaDB on the back.

Once I am done developing the beta version of Batch Totals Report (the name of the app ), I will turn it into a stand alone WP plug in.

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.

woocommerce
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’, c.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.

A Closer Look at Woo Products

woocommerce admin panel
Figure 1

WooCommerce supports the notion of “grouped” products. This is implemented by a store owner creating a Product abstraction, sometimes referred to by Woo in the documentation as a parent product.

This parent instance of a group of products does not itself have a price.

Its functional role is to visually link via some theme UI interface X number of products and presented to a shopper as a unit.

This unit is not invariant, as Product instance is a group can be purchased separately, by removing items from the Cart.

Moreover, there is no discount for buying grouped products.

In other words, being grouped does not affect the normal retail price in any way – unless a coupon is applied at checkout, the total cost is arithmetically additive.

The shop owner’s ability to group products is built into Woo core; in other words, it’s free.

In order to present a customer with more sophisticated marketing offers, a store owner must purchase extensions, such as the one for Product Bundling, or another for what is referred to in WooLand as a Composite Product.

These extensions are not free.

WooCommerce utilizes the term “virtual” to describe a subtype of Product, 

Once the sku and Type are specified, a shop owner user is free to link n-number of other Product types (except for abstract classes: in other words, you can have groupings of grouped products) to this parent instance.

In the WooCommerce world, a Product instance is by default a “Simple Product.”

Three other Product types are also available:
a) Grouped product
b) External/Affiliate product
c) Variable product

Any of these four Product types can optionally be “virtual” or “downloadable, which are subtypes of Product.

In general, the notion of virtual products refers to intangible products, such as a subscription to some pricey newsletter.

A downloadable product subtype is something tangible (such as the zip file for a plug in, or the .pdf of some newsletter) that you can download your computer.

This Product class hierarchy provides flexibility to the shop owner.

In can be confusing at first, but speaking generally, what the designers of Woo mean by the term “virtual” is something that you cannot see or touch, and downloadable is typically thought of as an executable of some sort.

There are additional complexities to Product that we need to keep in mind.

For example, what exactly are Variable Products, and what is meant by Custom Attributes?

A class of Grouped Product types is not a true M:N BOM (bill-of-materials) case, which usually leads to elaborate complexity in order to implement correctly, and often entails a significant performance hit.