Query de busca com relevância para as avaliações dos usuários e também para quando o conteúdo pesquisado está presente nos títulos, categorias e conteúdos.

woo-query.php

<?php

/*
  Seleciona todos os campos da tabela wp_posts (p.*) e todos os resultados vindos do LEFT OUTER JOIN (R.*).
  E também mostra a QUANTIDADE de avaliações e a MEDIA vindas dos outros LEFT OUTER JOIN (X e Y).
  O Primeiro LEFT OUTER JOIN (R) trata a busca pelas categorias de cada produto,
  os outros JOINS trazem a soma das avaliações e a quantidade de votos, a partir daí a MÉDIA é formada dividindo as soma pela quantidade de votos.
*/

SELECT p.*, R.*, X.TAXA AS TAXA, Y.QUANTIDADE AS QUANTIDADE,
(X.TAXA/Y.QUANTIDADE) AS MEDIA,


/*
  O MATCH() AGAINST() do mySQL é usado para trabalhar buscar mais precisas e mais rápidas.
  Ele é selecionado no início do SELECT para que apareça nos resultados e a gente possa organizar tudo por RELEVANCIA (de conteúdo geral) e RELEVANCIA_TITULO
  !!! O %s recebe os valores da consulta do wpdb->prepare() que trabalha a segurança.
*/
MATCH(p.post_title, p.post_content) AGAINST(%s) AS RELEVANCIA,
MATCH(p.post_title) AGAINST(%s) AS RELEVANCIA_TITULO
FROM wp_posts AS p

LEFT OUTER JOIN (
	
	SELECT r.object_id, tt.term_taxonomy_id, t.term_id, t.name, t.slug FROM wp_term_relationships AS r
	LEFT OUTER JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = r.term_taxonomy_id
	LEFT OUTER JOIN wp_terms AS t ON t.term_id = tt.term_id

) AS R
/* Cada registro do wp_posts terá ou não uma correspondência nos JOINS */

ON (p.ID = R.object_id)

LEFT OUTER JOIN (
	SELECT c.comment_post_ID AS POST, SUM(cm.meta_value) AS TAXA
	FROM `wp_comments` AS c
	LEFT OUTER JOIN `wp_commentmeta` AS cm
	ON c.comment_ID = cm.comment_id
	WHERE cm.meta_key = "rating"
	AND c.comment_approved = "1"
	AND cm.meta_value > 0
    GROUP BY POST
	ORDER BY POST DESC
) AS X
ON (p.ID = X.POST)

LEFT OUTER JOIN (
	SELECT c.comment_post_ID AS POST, COUNT(meta_value) AS QUANTIDADE
	FROM wp_comments AS c
	LEFT OUTER JOIN wp_commentmeta AS cm
	ON c.comment_ID = cm.comment_id
	WHERE c.comment_approved = "1"
	AND cm.meta_value > 0
	GROUP BY POST
	ORDER BY c.comment_post_ID DESC
) AS Y
ON (p.ID = Y.POST)

/* Condição para tratar a relevância da pesquisa */
WHERE MATCH(p.post_title, p.post_content) AGAINST(%s)

/* A pesquisa só retornará registros do woocommerce que estejam publicados */
AND p.post_type = "product"
AND p.post_status = "publish"

/* Uma última alternativa para encontrar a pesquisa no nome da categoria dos produtos */
OR R.name LIKE %s

/*
  - Agrupa pelo ID da publicação pois as queries trazem resultados duplicados por conta da necessidade do LEFT OUTER JOIN.
  - Ordena sequencialmente por título, por relevância geral e pelo melhor avaliado.
  3 ordenações para melhor posicionar os resultados.
  Pois a pesquisa poderia trazer algo relevante para o texto da busca mas posicionar no início um profissional mal avaliado, 
  dessa forma a gente trabalha tudo pra posicionar o mais relevante e melhor avaliado.
*/
GROUP BY p.ID
ORDER BY RELEVANCIA_TITULO DESC, RELEVANCIA DESC, MEDIA DESC

/* Trabalha a quantidade de saltos e registros por página */
LIMIT '. $step .', '. $pagination;