Cogito ergo sum

How to perform SQL queries with WooCommerce?4 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.

6) In case you use Yoast SEO plugin for SEO on your site and you want to get te know which focus keywords and meta description are used or related to a product, you have to run the following query: 

SELECT * FROM wp_postmeta WHERE post_id = xxxx AND (meta_key = '_yoast_wpseo_focuskw' OR meta_key = '_yoast_wpseo_metadesc')

xxxx must be replaced with product id.

7) If you want to select all Yoast SEO focus keywords and meta description for all products from a category, execute the following query: 

SELECT postmeta.meta_id,postmeta.post_id,postmeta.meta_key, postmeta.meta_value,posts.post_title FROM wp_postmeta as postmeta 
LEFT JOIN wp_posts as posts ON postmeta.post_id = posts.id 
LEFT JOIN wp_term_relationships as terms ON posts.ID = terms.object_id 
WHERE terms.term_taxonomy_id = XXXX  AND (postmeta.meta_key = '_yoast_wpseo_focuskw' OR postmeta.meta_key = '_yoast_wpseo_metadesc')

xxxx must be replaced with product category id.

 

 

 

To be continued…..

About the author

Peshmerge Morad

a machine learning & software engineer based in Germany, whose interests span multiple fields.

6 comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • This is highly interesting for me. If you could create an sql sample file, that creates one sample product in woocommerce – simple product with some attributes and a picture – we would love you! 😉

    .. or hire you 😉

      • Ok, more specific:

        We have to import around 2 mio. products to woocommerce.
        All known commercial or free products (import-solutions) are much too slow for that and wouldmneed two month for the job.

        that’s why we decide to add the products by sql command into the related tables.

        of course that’s no magic but need a week of studying the tables documentation.

        as you are known for knwosing about that, you could create a sample sql script, that adds one sample product.

        we would use that script information to add the 2 mio. products in the same way.

        “we” are an agency, working (also) for people, using wordpress. 🙂

Cogito ergo sum