WordPress: Return category result count from product post search

post-category-results.sql

/*
  This query looks into post fields and returns an ordered list of results based on the attached categories to the posts searched.
  This can be used to help weight results or to potentially show links to categories instead of the posts themselves.
  
  Can be modified to add additional filters. Still not sure how to tie this into pre-get-posts but working on it.
+------------+---------+--------------+
| name       | term_id | result_count |
+------------+---------+--------------+
| Category A |      15 |           12 |
| Category C |      17 |           12 |
| Category B |      16 |           11 |
| Category D |      21 |            8 |
+------------+---------+--------------+

*/

SELECT term.name,term.term_id,count(*) as result_count
FROM wp_terms as term
LEFT JOIN wp_term_relationships as tie
	ON term.term_id = tie.term_taxonomy_id
LEFT JOIN wp_posts as post
	ON post.ID = tie.object_id
LEFT JOIN wp_term_taxonomy as tax
	ON term.term_id = tax.term_id
WHERE CONCAT_WS(post.post_content,post.post_title,post.post_excerpt) LIKE '%A%'
AND tax.taxonomy = "product_cat"
GROUP BY term.term_id
ORDER BY result_count DESC;