Wordpress Custom Query - Woocommerce Search Order By Rating

woo.text

'
1º LEFT OUTER JOIN somente a soma das avaliacoes
2º LEFT OUTER JOIN somente a quantidade das avaliacoes
resulta na média
POSTS, COMENTARIOS, METADADOS_COMENTARIOS
inner join nao vai trazer os posts sem comentarios ou comentarios sem classificacao rating
left outer join vai trazer os posts, comentarios coincidentes no inner (na relacao descrita no ON) e tambem os posts sem comentarios, comentario sem rating
select * from POSTS
select * from COMENTARIOS as c
left outer join METADADOS_COMENTARIOS as mc
on c.comment_ID = mc.comment_id
'

SELECT p.ID, X.POST, Y.POST, p.post_title, (X.TAXA/Y.QUANTIDADE) AS MEDIA FROM wp_posts AS p

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)

WHERE  p.post_type = 'product'
AND p.post_status = 'publish'
OR p.post_status = 'private'
ORDER BY MEDIA
DESC LIMIT 0, 100;