John Davidson

Message:

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

---

My Answer:
To get the image path+name for the product category thumbnail_id, you can modify your query to include another JOIN to the wp_posts table. Here's an updated version of your query:

sql
SELECT wpt.term_id, wpt.name, wpt.slug, wppm.meta_value AS image_path
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')


In this query, we added another JOIN to the wp_posts table to get the image path for the thumbnail_id. We are joining on the post_id (which is the thumbnail_id) and selecting the meta_value where the meta_key is '_wp_attached_file', which should give you the image path for the product category.

This should give you the category name, slug, and the image path for each product category.