Get also WooCommerce product category thumbnail Id using a SQL query

Question

I'm using WordPress with WooCommerce and I have written the following SQL query which gets the product category terms IDs names and slugs:

SELECT t.term_id AS id, t.name AS post_title,t.slug AS post_url
FROM   wp_terms t 
LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
WHERE  tt.taxonomy = 'product_cat'
ORDER  BY name

How should I change this SQL query to get also the product category thumbnail Id?

Note: I'm only interested in a SQL query, but not anything else like a WP_Query.

Answer 1

To get additionally the thumbnail ID in your SQL query for WooCommerce product category terms, you can use the following instead:

SELECT t.term_id AS id, t.name AS post_title,t.slug AS post_url, tm.meta_value AS thumb_id 
FROM   wp_terms t 
LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
LEFT JOIN wp_termmeta tm ON t.term_id = tm.term_id
WHERE  tt.taxonomy = 'product_cat'
AND tm.meta_key = 'thumbnail_id'
ORDER BY t.name

Tested and works.