Mysql: [match() against()] full text search order full field value match first

Question

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?

Answer 1

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.)