How to perform SQL queries with WooCommerce?3 min read

This post will be updated regularly. This is a work in progress 🙂

DISCLAIMER: Maybe the title is a bit misleading, but this blog post is meant to be as a reference for people just like me who are forced to google everytime how to get a or to manipulate product, product category and all other stuff related to WooCommerce using SQL.

Some useful SQL queries to fetch/edit data in your WP database when it comes to WooCommerce:

I am assuming here that your database tables prefix is wp_, it could be anything! So don't forget to change it when you want to use it!

1) All products are saved in wp_terms , to get A list of all products you have to execute the following query:

SELECT * FROM wp_posts where post_type ='product'

2) All products categories (not only products categories, also normal categories for pages and posts) are saved in the table wp_terms, but in our case there is nothing that indicates it’s a product categories, except the name. In case you have a couple of categories you can still know which ones are for products, but in case you have more than a couple, you have to do a bit more to make sure you are dealing/fetching correct product categories (otherwise you will delete unintentionally a lot of data).
The table  wp_terms contains the categories ID’s, name and slug, but it doesn’t represents a category on itself, it must be joined with the wp_term_taxonomy. In other words: “A term is not a category or tag on its own. It must be given context via the term_taxonomy table.” Check this official WordPress page:
To get a list of all categories:

SELECT tx.term_taxonomy_id, tm.term_id, tm.name, tm.slug, tx.parent as parent_category, tx.count as products_count FROM wp_term_taxonomy as tx left join wp_terms as tm on tx.term_id = tm.term_id where tx.taxonomy = 'product_cat' limit 100

3) To get all product images (attachments) from the database. Run this query:

SELECT * FROM wp_posts as atta left join wp_posts as pro on atta.post_parent = pro.ID WHERE atta.post_type='attachment' AND pro.post_type = 'product'

Note: in general when you upload an image to WordPress and include it as a featured image or as a media item in any post/custom post_type (in our case product) the column post_parent for that image will be the ID of that post/custom post_type.
Bonus: to get information about your image, for example: alt_text, path, and other meta_data: You have to execute the following query on wp_postmeta

SELECT * FROM `wp_postmeta` WHERE `post_id` = ID_OF_THE_ATTACHMENT

4) To get amount of products in a product category, execute this query:

SELECT count(object_id) FROM `mo_term_relationships` WHERE `term_taxonomy_id` = xxxx

xxxx must be replaced with product category id.
5) To get all products (id and title) from a specific category, execute this following query:

SELECT ID, post_title from wp_posts as posts LEFT JOIN wp_term_relationships terms on posts.ID = terms.object_id WHERE terms.term_taxonomy_id = xxxx

Or you can execute the following query (using sub-query, without left join):

SELECT ID, post_title from wp_posts WHERE ID in ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id = xxxx)

xxxx must be replaced with product category id.

To be continued…..