0

I am doing a full-text search against a WordPress database, I am using match() against() to test the search term against multiple fields from multiple tables, all is working great, but I want to improve the order so when a field value is exactly the same as the search term it comes first before any other matches.

This is the current query that searches the database for the term traduction:

    SELECT  `ID`
        FROM  `wp_posts`
        JOIN  
        (
            SELECT  `post_id`, `meta_key`, `meta_value`
                FROM  `wp_postmeta`
                WHERE  `meta_key` IN ('definition', 'note', 'definition_en',
                            'observation', 'الملاحظة', 'التحديد')
        ) AS pm  ON `wp_posts`.`ID` = pm.`post_id`
        JOIN  
        (
            SELECT  `wp_terms`.`term_id`, `name`, `object_id`
                FROM  `wp_term_relationships`
                JOIN  `wp_term_taxonomy`  ON `wp_term_relationships`.`term_taxonomy_id` = `wp_term_taxonomy`.`term_taxonomy_id`
                JOIN  `wp_terms`  ON `wp_term_taxonomy`.`term_id` = `wp_terms`.`term_id`
                WHERE  `taxonomy` IN ('terme', 'terme_arabic', 'terme_english',
                            'sous-domaine')
        ) AS td  ON `wp_posts`.`ID` = td.`object_id`
        WHERE  (MATCH (`post_title`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
            OR  MATCH (`meta_value`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
            OR  MATCH       (`name`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE)
               )
        ORDER BY  MATCH (`post_title`) AGAINST ("traduction" IN NATURAL LANGUAGE MODE) DESC 

In the results, the post with the title traduction is ordered after other posts with titles like traduction didactique, aide à la traduction,...

How can I modify the query so exact field value matches rank higher than other matches?

0
ORDER BY post title != 'traduction',
         MATCH...

Explanation:

  • != will generate 0 for = and 1 for !=, so the exact match will come first.
  • Then the rest ORDER BY will control the ordering.

(Please indicate which table each column is in.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.