[ad_1]
I'm expanding on default WordPress search with fulltext, and have an SQL query that looks like this
SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*,
MATCH (wp_posts.post_title, wp_posts.post_content) AGAINST ('"string" @4' IN BOOLEAN MODE) AS p_score,
MATCH (wp_postmeta.meta_value) AGAINST ('"string" @4' IN BOOLEAN MODE) AS pm_score
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
LEFT JOIN wp_icl_translations ON wp_posts.ID = wp_icl_translations.element_id
LEFT JOIN wp_icl_languages ON wp_icl_translations.language_code = wp_icl_languages.code AND wp_icl_languages.active = 1
WHERE 1=1
AND IF(
MATCH (wp_posts.post_title, wp_posts.post_content) AGAINST ('"string" @4' IN BOOLEAN MODE) > MATCH (wp_postmeta.meta_value) AGAINST ('"string" @4' IN BOOLEAN MODE),
MATCH (wp_posts.post_title, wp_posts.post_content) AGAINST ('"string" @4' IN BOOLEAN MODE) > 0,
MATCH (wp_postmeta.meta_value) AGAINST ('"string" @4' IN BOOLEAN MODE) > 0
)
AND wp_icl_translations.language_code = 'hr'
AND wp_posts.post_status = 'publish'
ORDER BY p_score DESC LIMIT 0, 12
The problem is that I have both p_score and pm_score in the resulting table, so duplicates will happen like
ID | ... | p_score | pm_score
234 | ... | 9.234234 | 8.234323
234 | ... | 9.234234 | 0
While technically they're not duplicates (different scores), I am still seeing duplicates in my search.
Can a query be made such that once I have the results from the MATCH, I compare which score (p_score or pm_score) is higher and then just chose the one with the higher score to show? I tried something like that in the WHERE clause, but obviously, I'm doing something wrong.
[ad_2]
لینک منبع
