WooCommerce database query : Get product category image path+name from thumbnail_id

I am trying to list all my Woocommerce Product Categories

| category name | category slug | … | … | category image path+name |

---

Using this query below, gets me most of the way:

SELECT *
    FROM wp_terms wpt
LEFT JOIN
    wp_termmeta wptm
    ON 
        wptm.term_id = wpt.term_id
    AND
        wptm.meta_key = 'thumbnail_id'
LEFT JOIN
    wp_postmeta wppm
    ON
        wppm.post_id = wptm.meta_value AND
        wppm.meta_key = 'wp_attached_file'
WHERE
    wpt.term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy='product_cat')

I have:

| term_id | name           | slug          | ... | meta_key     | meta_value |
| 96      | Machine Screws | machine_screw | ... | thumbnail_id | 2657       |

From here I can’t work out how or where to JOIN the thumbnail_id to get my image path+name.

---

edit:

I have done a fair amount of reading and I know I should be looking for an attachment to a post, but I can’t quite join the dots for myself.

---

note:

• posted on https://wordpress.stackexchange.com/posts/393551/
• it was closed as off topic
• reposting here in case it is useful to someone else

Answer

It looks like I’ve managed to work it out.

I needed to:

• JOIN to wp_posts

With the link keys:

• TABLE : wp_postmeta
  • column : meta_key
• TABLE : wp_posts
  • column : ID

---

SELECT *
    FROM wp_terms wpt
LEFT JOIN
    wp_termmeta wptm
    ON 
        wptm.term_id = wpt.term_id
    AND
        wptm.meta_key = 'thumbnail_id'
LEFT JOIN
    wp_postmeta wppm
    ON
        wppm.post_id = wptm.meta_value AND
        wppm.meta_key = 'wp_attached_file'
LEFT JOIN
    wp_posts wpp
    ON 
        wpp.ID = wptm.meta_value
WHERE
    wpt.term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy='product_cat')

---

That’s given me the listing I want.

I now have a column:

| category name | category slug | … | … | guid |

That has the image url:

https://test.somesite.com/wp-content/uploads/2021/06/category-Machine-Screws_150x150.svg

I am now going to EXPORT to .CSV

Then I’ll try to work out how to get to my end goal:

My end goal is to do an SQL update to associate images with my product categories. I have 1,400 product categories and counting – half-way though importing 31,000 products. Probably 60% of the subcategories share the same images. Doing the image links via the Admin GUI is too labour intensive.

So my aim will be to:

• find the thumbnail_id
• for each common image
• shared by each multiple ‘common’ Product Category

Then I should be able to update that thumbnail_id into each common Product Category directly in the database.