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.

Answer 2

Instead of rely on MySQL query my suggestion is please look into the REST API provided by WooCommerce.

For your need the good candidate is

Get Category API service

Which have the details which you expect like this

{
  "id": 9,
  "name": "Clothing",
  "slug": "clothing",
  "parent": 0,
  "description": "",
  "display": "default",
  "image": {
    "id": 730,
    "date_created": "2017-03-23T00:01:07",
    "date_created_gmt": "2017-03-23T03:01:07",
    "date_modified": "2017-03-23T00:01:07",
    "date_modified_gmt": "2017-03-23T03:01:07",
    "src": "https://example.com/wp-content/uploads/2017/03/T_2_front.jpg",
    "name": "",
    "alt": ""
  },
  "menu_order": 0,
  "count": 36,
  "_links": {
    "self": [
      {
        "href": "https://example.com/wp-json/wc/v3/products/categories/9"
      }
    ],
    "collection": [
      {
        "href": "https://example.com/wp-json/wc/v3/products/categories"
      }
    ]
  }
}

For multiple categories

List all product categories

Sample output will be like this:

[
  {
    "id": 15,
    "name": "Albums",
    "slug": "albums",
    "parent": 11,
    "description": "",
    "display": "default",
    "image": [],
    "menu_order": 0,
    "count": 4,
    "_links": {
      "self": [
        {
          "href": "https://example.com/wp-json/wc/v3/products/categories/15"
        }
      ],
      "collection": [
        {
          "href": "https://example.com/wp-json/wc/v3/products/categories"
        }
      ],
      "up": [
        {
          "href": "https://example.com/wp-json/wc/v3/products/categories/11"
        }
      ]
    }
  },
  {
    "id": 9,
    "name": "Clothing",
    "slug": "clothing",
    "parent": 0,
    "description": "",
    "display": "default",
    "image": {
      "id": 730,
      "date_created": "2017-03-23T00:01:07",
      "date_created_gmt": "2017-03-23T03:01:07",
      "date_modified": "2017-03-23T00:01:07",
      "date_modified_gmt": "2017-03-23T03:01:07",
      "src": "https://example.com/wp-content/uploads/2017/03/T_2_front.jpg",
      "name": "",
      "alt": ""
    },
    "menu_order": 0,
    "count": 36,
    "_links": {
      "self": [
        {
          "href": "https://example/wp-json/wc/v3/products/categories/9"
        }
      ],
      "collection": [
        {
          "href": "https://example/wp-json/wc/v3/products/categories"
        }
      ]
    }
  },
  {
    "id": 10,
    "name": "Hoodies",
    "slug": "hoodies",
    "parent": 9,
    "description": "",
    "display": "default",
    "image": [],
    "menu_order": 0,
    "count": 6,
    "_links": {
      "self": [
        {
          "href": "https://example.com/wp-json/wc/v3/products/categories/10"
        }
      ],
      "collection": [
        {
          "href": "https://example.com/wp-json/wc/v3/products/categories"
        }
      ],
      "up": [
        {
          "href": "https://example.com/wp-json/wc/v3/products/categories/9"
        }
      ]
    }
  }
]

On All product categories API please check available parameters some of the important parameters are:

1. page
2. per_page
3. search
4. hide_empty