How are WooCommerce products stored in the database

In this article, we’ll explore the WooCommerce Products Database. We’ll examine where products are stored, focusing on the wp_posts and wp_postmeta tables and how product attributes and meta are managed. The article also covers product variations, highlighting how they’re structured and accessed. Additionally, we’ll provide examples of SQL queries for efficient data retrieval and manipulation, essential for advanced WooCommerce database management. In most cases, using built-in WooCommerce functions to get product data might be sufficient. However if you need to implement a functionality that is beyond the default PHP functions in WooCommerce,  familiarity with the WooCommerce database schema is essential for effective customization and data management.

Where are WooCommerce products stored in the WordPress database

WooCommerce products are stored within the WordPress database as a custom post type named ‘product’. This categorization is part of the broader WordPress content management system, where various types of content are differentiated as post types. The primary storage location for these product entries is the wp_posts table in the database.

To find all products in the wp_posts table, you can use the following MySQL query:

SELECT * FROM wp_posts WHERE post_type = 'product';

You can write this query in phpmyadmin or your favorite database tool. This returns a result like this:

Some of the key attributes of the wp_posts table relevant to WooCommerce products include:

• post_type: This field specifically identifies the entry as a product, differentiating it from other post types within WordPress.
• post_title: This field holds the name of the product. It’s displayed as the product title on the website and is crucial for identifying the product in the backend and to the customers.
• post_content: Here, the detailed description of the product is stored. This content is what appears on the product page as the main description, providing customers with essential information about the product.
• post_date and post_date_gmt: These fields represent the date and time when the product was created. post_date is in the local time zone set in WordPress, while post_date_gmt is in Greenwich Mean Time. These timestamps are important for sorting and organizing products, especially in time-sensitive contexts like sales or product launches.
• post_status: This attribute indicates the current status of the product. Common statuses include ‘publish’ (visible on the site), ‘draft’ (not published), or ‘pending’ (awaiting review). Managing this status is crucial for controlling the visibility and availability of products on the store.
• post_name: Known as the slug, this field is used in the product’s URL. It’s typically a URL-friendly version of the post_title and is crucial for SEO and ensuring that the product pages are accessible and easily shareable.
• post_excerpt: This is the short description of the product.
• post_author: The ID of the user who has created the product.

To find all published products you can use the following query:

SELECT * FROM wp_posts WHERE post_type = 'product' AND post_status = 'publish';

Where are product details and meta stored in the database

WooCommerce extends the functionality of the WordPress database by using the wp_postmeta table to store additional product details. This table operates on a key/value pair system to store various attributes of products. Each row in this table is linked to a specific product through a post_id field, which corresponds to the product’s ID in the wp_posts table. The data within wp_postmeta is organized with meta_key and meta_value columns, where meta_key is the name of the attribute and meta_value is the actual data associated with that attribute. This structure allows for extensive and varied product information to be stored efficiently.

To load the meta data of a specific product, you can use the following query:

SELECT * FROM wp_postmeta WHERE post_id = 17;

This query loads all meta fields for a product with ID 17. This is what the result looks like in the database:

Important meta_key values in WooCommerce product management include:

• _regular_price: This key holds the product’s regular price. It’s the default selling price before any discounts or sales are applied.
• _price: Represents the current selling price of the product. This may be the same as _regular_price or could be a special sale price. It’s important to note that there might be multiple rows with key _price for a single product, for example, when the product is variable.
• _sku: Short for Stock Keeping Unit, this key is used to store a unique identifier for each product. The SKU is instrumental in inventory tracking and management, especially for stores with a large number of products.
• total_sales: This key keeps a count of how many times the product has been sold. It’s a critical metric for understanding product popularity and for inventory replenishment strategies.
• _stock: Reflects the current stock level or the number of units available for the product. This key is constantly updated as sales occur and new stock is added, making it vital for inventory control.
• _stock_status: Indicates the availability status of the product, such as ‘in stock’, ‘out of stock’, or ‘on backorder‘. This status is dynamically displayed on the product page, informing customers about product availability.
• _wc_average_rating: Stores the average customer rating for the product, calculated from customer reviews. This key is used to showcase product quality and customer satisfaction on the product page.
• _wc_review_count: Counts the number of customer reviews a product has received. It’s an indicator of customer engagement and feedback, influencing buyer decisions.
• _product_attributes: This key is used for storing information about product attributes. These attributes can include anything from sizes, colors, and materials to custom attributes created by the store owner. The data structure for this key is serialized, allowing for the storage of multi-dimensional data. For variable products, this field contains the supported attributes, and the specific attributes for each variation are stored as variation metadata. For more information, refer to the Where are variation details stored section below. Here is an example of how the unserialized value for _product_attributes looks for a variable product:

Each product in WooCommerce can have numerous entries in the wp_postmeta table, each representing different facets of the product’s data. Understanding the method of storing data in key wp database tables like wp_posts and wp_postmeta is crucial for efficiently managing product information in WooCommerce.

Now, let’s see some examples of extracting some of this information using SQL queries.

Example: Querying products and their SKU

SELECT ID, post_title, sku_meta.meta_value AS `SKU` FROM wp_posts
LEFT JOIN wp_postmeta sku_meta on wp_posts.ID = sku_meta.post_id AND sku_meta.meta_key = '_sku'
WHERE post_type = 'product' AND post_status = 'publish';

This SQL query selects the ID and title of each published product from the wp_posts table and retrieves the corresponding SKU (Stock Keeping Unit) from the wp_postmeta table. It does this by joining wp_posts with wp_postmeta on the product ID (post_id) where the meta key is ‘_sku’. The query specifically targets products (post_type = ‘product’) that are currently published (post_status = ‘publish’), ensuring it only retrieves data for active, visible products in the store.

Example: Querying products and multiple meta values

Now let’s see an example of loading the SKU and Total Sales meta fields:

SELECT ID, post_title, sku_meta.meta_value AS `SKU`, sales_meta.meta_value AS `total_sales`
FROM wp_posts
LEFT JOIN wp_postmeta sku_meta on wp_posts.ID = sku_meta.post_id
  AND sku_meta.meta_key = '_sku'
LEFT JOIN wp_postmeta sales_meta on wp_posts.ID = sales_meta.post_id
  AND sales_meta.meta_key = 'total_sales'
WHERE post_type = 'product' AND post_status = 'publish';

In this case, the query makes two LEFT JOIN operations on the wp_postmeta table to fetch the SKU (_sku) and total sales (total_sales) for each product. The first JOIN fetches the SKU by matching the product ID (post_id) and the meta key _sku, while the second JOIN retrieves the total sales by matching the same product ID with the meta key total_sales. This is what the result looks like:

When considering performance, it’s important to note that each additional JOIN to wp_postmeta adds complexity to the query. This can potentially impact performance, especially if the database is large. Therefore, if multiple fields are required, optimizing the database and considering caching strategies to maintain efficient query performance is crucial.

Where are product variations stored in the database

In WooCommerce, each variable product is initially stored as a standard product record in the wp_posts table. This main product record serves as the parent for its variations. The variations themselves are then stored as separate records in the same wp_posts table, but with a distinct post_type of ‘product_variation’.

To load all variations, you can use the following query:

SELECT * FROM wp_posts WHERE post_type = 'product_variation';

This gives us a result like this:

The connection between a variable product and its variations is established through the post_parent column in the wp_posts table. For each variation, the post_parent field contains the ID of the main product, effectively creating a parent-child relationship in the database. This structure allows for efficient organization and retrieval of product variations in relation to their parent products.

Key columns in the wp_posts table that are relevant to product variations include:

• post_parent: This column is critical as it links each variation to its parent product, maintaining the association within the WooCommerce site.
• post_title: In the context of product variations, this usually contains information that differentiates each variation, like size or color.
• post_status: Similar to standard products, this indicates the status of each product variation (e.g., ‘publish’, ‘draft’, ‘pending’). It’s important for managing the visibility and availability of each variation.
• post_type: Set as ‘product_variation’ for variations, this differentiates them from standard products (which are set as ‘product’).

To load all variations for a specific product, you can use the following SQL query:

SELECT * FROM wp_posts WHERE post_type = 'product_variation' AND post_parent = 16;

This query loads all variations for the product with ID 16.

Where are variation details stored

The metadata for WooCommerce product variations is stored in the wp_postmeta table, similarly to how main product data is stored. Each product variation, represented as a record in the wp_posts table, has its corresponding meta entries in wp_postmeta. The post_id in wp_postmeta matches the ID of the product variation post, linking the metadata directly to the specific variation.

To load all meta fields for a given variation, you can use the following query:

SELECT * FROM wp_postmeta WHERE post_id = 32;

In this case, we load all meta fields for the variation with ID 32.

The metadata for product variations includes fields similar to those of the main product, such as _sku, total_sales, _price, among others. These meta fields play a similar role for variations as they do for standard products, storing crucial information like pricing, stock-keeping units, and sales data.

A unique aspect of product variation metadata is the storage of attributes. In WooCommerce, attributes can be defined in two ways, and this affects how they are stored in the database:

• Attributes created via Products > Attributes: When attributes are created through the Products > Attributes section of your dashboard, they are handled using WordPress taxonomies. The corresponding meta keys for these attributes in wp_postmeta are prefixed with attribute_pa_. For example, an attribute for color would be stored as attribute_pa_color. This prefix indicates that the attribute is a globally defined attribute.
• Attributes created from the Product Edit Page > Attributes: Alternatively, when attributes are created directly on the Product Edit page, they are not linked to global taxonomies. In this case, the meta keys are simply prefixed with attribute_. For instance, a custom attribute like ‘logo’ would be stored as attribute_logo. These attributes are specific to the individual product or variation and are not shared globally.

Example: Loading all product variation attributes

SELECT products.ID as product_id,
  products.post_title as product_name,
  variations.ID AS variation_id,
  variations.post_title as variation_name,
  attributes.meta_key AS attribute_name,
  attributes.meta_value AS attribute_value
FROM wp_posts AS products
LEFT JOIN wp_posts AS variations ON variations.post_parent = products.ID
LEFT JOIN wp_postmeta AS attributes ON attributes.post_id = variations.ID
WHERE products.post_type = 'product'
  AND variations.post_type = 'product_variation'
  AND attributes.meta_key LIKE 'attribute_%'

This SQL query retrieves a list of product variations from a WooCommerce database, along with their associated attributes. For each product, it lists out every variation, and for each variation, it details all attribute key-value pairs. The query joins the wp_posts table twice: first to connect products with their variations, then to link variations with their attributes stored in wp_postmeta. The result is a comprehensive table where each row represents a variation with a specific attribute. Therefore, a single product with multiple variations and multiple attributes per variation will generate several rows, one for each attribute of each variation:

How are product categories stored in the database

In WooCommerce, product categories are an integral part of organizing and managing products. These categories are stored in the WordPress database using a taxonomy system, which is the standard WordPress method for categorizing content. The taxonomy for product categories in WooCommerce is ‘product_cat’.

The actual storage of product categories involves several tables in the database:

• wp_terms: This table stores the basic information of each category, such as the category name and slug (a URL-friendly version of the name).
  
• wp_term_taxonomy: This table links the term (from wp_terms) to the taxonomy (‘product_cat’ for product categories). It includes the term_id (linking to wp_terms) and the taxonomy (indicating the type of term).
  
• wp_term_relationships: This table creates a relationship between posts (products) and terms (categories). It uses the object_id to reference a product’s ID in wp_posts and the term_taxonomy_id to reference a category in wp_term_taxonomy.
  

When a product is assigned to a category in WooCommerce, an entry is created in wp_term_relationships, linking the product to the category. This multi-table structure allows for a flexible and powerful system to categorize products.

Let’s see an example of how to load the categories

SELECT object_id AS product_id, tt.term_id AS category_id, name AS category_name
FROM wp_term_relationships AS rel
LEFT JOIN wp_term_taxonomy AS tt ON rel.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms AS terms ON terms.term_id = tt.term_id
WHERE object_id = 24
AND taxonomy = 'product_cat'

This SQL query is designed to retrieve the category information for a specific product in the WooCommerce database (in this case, product with ID 24):

• Accesses the wp_term_relationships table (aliased as rel) to find the term taxonomy IDs associated with the product (using object_id).
• Uses a LEFT JOIN to connect to the wp_term_taxonomy table (aliased as tt) to get the actual term IDs (term_id) for those term taxonomy IDs, filtering the results to include only those entries that belong to the ‘product_cat’ taxonomy, which represents product categories.
• Performs another LEFT JOIN with the wp_terms table (aliased as terms) to fetch the names of these categories (name).

The result is a list showing each category (with ID and name) that the product with ID 24 belongs to:

How are product tags stored in the database

Similarly to categories, product tags are stored using custom taxonomies. The only difference is the taxonomy name, which in this case is: product_tag.

With this information, we can modify the query above to load product tags instead of categories:

SELECT object_id AS product_id, tt.term_id AS tag_id, name AS tag_name
FROM wp_term_relationships AS rel
LEFT JOIN wp_term_taxonomy AS tt ON rel.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms AS terms ON terms.term_id = tt.term_id
WHERE object_id = 24
AND taxonomy = 'product_tag'

As you can see, the main difference in the query is setting the taxonomy to ‘product_tag’. We have also renamed some of the aliases for better readability. Similarly, we get a result like this:

Where is product type stored in the WooCommerce database

Product types in the WooCommerce plugin refer to different classifications of products based on their features and how they are sold. The core WooCommerce product types include:

• Simple – standard, single products
• Variable – products with variations like size or color
• Grouped – a collection of related products that can be purchased individually
• External/Affiliate – products sold on another site but advertised on your store

Product types are stored using the same taxonomy system as product categories. They are stored as a taxonomy named ‘product_type’.

The information about which taxonomy term is associated with a specific product is stored in the WordPress database tables that handle taxonomies and their relationships to posts (products). These tables include wp_terms, wp_term_taxonomy, and wp_term_relationships, which have been discussed previously in the context of product categories.

Similarly to retrieving categories, the product type information can be retrieved using the following database query:

SELECT object_id AS product_id, name AS product_type
FROM wp_term_relationships AS rel
LEFT JOIN wp_term_taxonomy AS tt ON rel.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms AS terms ON terms.term_id = tt.term_id
WHERE object_id = 24
AND taxonomy = 'product_type'

And this is the result of the query:

Product sales information

If you need to access more detailed and advanced product information and analytics without the need to write SQL queries, Users Insights’ reporting feature can be a valuable tool. It offers advanced product reports that explore your store’s performance specifics. This includes Best Selling Products, which highlights your top performers, Sales by Product, offering a detailed breakdown of sales figures for each product, Frequently Bought Together, which uncovers patterns in customer purchasing behavior, and Variation and Attribute Sales, providing insights into which product variations and attributes are most popular. To learn more, you can visit the WooCommerce Product Sales Reports page.

Conclusion

A clear understanding of the WooCommerce Products Database is key for anyone working with this e-commerce platform, especially plugin developers. We’ve covered how products, their variations, and categories are stored, along with the specifics of product metadata and types. Additionally, we’ve highlighted the importance of effective SQL querying for WooCommerce product data management. This knowledge is essential for optimizing your WooCommerce store and improving customer experiences.