Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Get also WooCommerce product category thumbnail Id using a SQL query

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search