WordPress query non-empty tags or categories. Query only tags/categories that are assigned to a product.
If you need to query tags or categories not particularly related to any tag category or product. You can go ahead and query data directly from two tables: wp_term and wp_termmeta. Table wp_term contain 4 fields including name and slug of tag/category in wp_termmeta we can find more information about particular term including number of products which are assigned to them. Just search meta_key column for product_count_product_tag or product_count_product_cat in wp_termmeta table. The corresponding meta value will return a number of items assign to this product.
Tables structure
wp_terms Table
show create table wp_terms;
+----------+--------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Table | Create Table
+----------+--------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| wp_terms | CREATE TABLE `wp_terms` (
`term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`slug` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`term_group` bigint(10) NOT NULL DEFAULT 0,
PRIMARY KEY (`term_id`),
KEY `slug` (`slug`(191)),
KEY `name` (`name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+--------------------------------------------------------------------------
--------------------------------------------------------------------------------------wp_termmeta Table
Table wp_termmeta represent a classical example of non SQL (not relational) database design. Instead of relational database structure we have two very flexible columns meta_key and meta_value to which we can add numerous custom defined keys and values.
show create table wp_termmeta;
+-------------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Table | Create Table
+-------------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------------
| wp_termmeta | CREATE TABLE `wp_termmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`term_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`meta_id`),
KEY `term_id` (`term_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------------SQL Query
SELECT t.*, tm.* FROM wp_termmeta tm
LEFT JOIN wp_terms t ON tm.term_id = t.term_id
WHERE tm.meta_key = 'product_count_product_tag' -- product_count_product_cat for category
-- AND tm.meta_value > 0 -- select only non empty tags
ORDER BY t.name ASCOutput
+---------+-------------------------+---------------------+------------+---------+---------+---------------------------+------------+
| term_id | name | slug | term_group | meta_id | term_id | meta_key | meta_value |
+---------+-------------------------+---------------------+------------+---------+---------+---------------------------+------------+
| 36 | tag-kapusta | kapusta | 0 | 21 | 36 | product_count_product_tag | 0 |
| 37 | tag-cebula | cebula | 0 | 22 | 37 | product_count_product_tag | 0 |
| 38 | tag-ziemniak | ziemniak | 0 | 23 | 38 | product_count_product_tag | 0 |
| 39 | tag-cebule | cebule | 0 | 24 | 39 | product_count_product_tag | 1 |
| 40 | tag-ziemniaki | ziemniaki | 0 | 25 | 40 | product_count_product_tag | 1 |
| 41 | tag-kapusty | kapusty | 0 | 26 | 41 | product_count_product_tag | 1 |
| 42 | tag-pomidory | pomidory | 0 | 27 | 42 | product_count_product_tag | 2 |
| 43 | tag-jabłka | jablka | 0 | 28 | 43 | product_count_product_tag | 2 |
| 44 | tag-ogórki | ogorki | 0 | 29 | 44 | product_count_product_tag | 1 |
| 45 | tag-pomarańcze | pomarancze | 0 | 30 | 45 | product_count_product_tag | 1 |
| 46 | tag-pomidory koktajlowe | pomidory-koktajlowe | 0 | 31 | 46 | product_count_product_tag | 2 |
+---------+-------------------------+---------------------+------------+---------+---------+---------------------------+------------+
11 rows in set (0.001 sec)