How to order results by custom relevance with PHP in WordPress

Introduction

This tutorial explains, in full detail, how to order WordPress query results by a custom relevance score computed with PHP and SQL. It covers design choices, security and performance considerations, multiple implementation patterns (simple LIKE-based scoring, fulltext MATCH AGAINST scoring), how to include meta fields and taxonomy hits, integration with WP_Query via filters, and how to preserve pagination (found_posts). Example code for each approach is provided. All code examples are ready to drop into a plugin or theme functions.php (with minor adjustments) and are explained line-by-line.

When and why to use custom relevance ordering

• Default WordPress ordering options (relevance from built-in search, date, menu_order, meta_value, etc.) are limited. You often need weighted scoring across title, content, excerpt, custom fields and taxonomy terms.
• Custom relevance is helpful for site search, boosted listings, product search, filtered catalogs and recommendations where certain fields should weigh more.
• Trade-offs: accuracy vs. complexity vs. performance. Fulltext MATCH is more efficient and accurate for large text, while LIKE-based scoring is simple but can be slower at scale.

High-level approaches

1. Simple weighted WHERE/CASE/LIKE expressions combined into a relevance value (easy to implement works on any MySQL setup).
2. Fulltext-based scoring with MATCH() AGAINST(… IN BOOLEAN MODE) and weighted combination across fields (requires FULLTEXT indexes much faster on large content).
3. External search engine (Elasticsearch, Algolia, MeiliSearch) if you need full-featured search, fuzzy matching, synonyms, very large datasets or advanced ranking.

Important common considerations

• Security: Always escape user inputs (use wpdb->esc_like, esc_sql and prepared statements when combining into raw SQL).
• Duplicates and joins: Joining postmeta or term tables can create duplicate rows use GROUP BY p.ID or use SUM(…) and COUNT(DISTINCT p.ID) when counting results.
• Pagination (found_posts): When you modify SQL (GROUP BY etc.) WP_Querys default found_posts may be wrong. Run a separate COUNT(DISTINCT p.ID) query or filter found_posts to return correct total.
• Performance: Avoid scanning too many rows add indexes, limit to needed post_types/statuses, consider transient caching or use an external engine for heavy loads.

How this tutorial integrates with WP_Query

All examples use the posts_clauses filter to modify the SQL parts (fields, join, where, groupby, orderby). Each example checks a custom query var (for example custom_relevance => true, relevance_term => …) so it only triggers when you intend to use the custom relevance ordering.

Example A — Simple weighted LIKE-based relevance (safe, portable)

This example computes a relevance score by checking whether the search term appears in the post_title, post_content and a custom meta field. Title matches are weighted higher than content, and meta fields get smaller weight. It demonstrates how to integrate with WP_Query via posts_clauses and how to return correct pagination count.

get(custom_relevance) is truthy.
    if (! query->get(custom_relevance) ) {
        return clauses
    }

    // Get the search term from the query var relevance_term
    term = (string) query->get(relevance_term)
    if ( === term) {
        return clauses
    }

    // Prepare escaped LIKE pattern: use esc_like   esc_sql
    like_esc = esc_sql( wpdb->esc_like( term ) )
    like_pattern = % . like_esc . % // quotes included for safe SQL insertion

    // Optional: restrict to specific post types or statuses (recommended)
    // If you need to limit post_type, do that using WP_Query args (preferred).
    // Example weights:
    weight_title   = 10
    weight_content = 5
    weight_meta    = 2

    // Join postmeta if you want to include a specific meta key in scoring.
    // Replace your_meta_key with your actual meta key or remove this join if not needed.
    meta_key = your_meta_key
    clauses[join] .=  LEFT JOIN {wpdb->postmeta} AS pm_relevance ON (pm_relevance.post_id = p.ID AND pm_relevance.meta_key = {meta_key}) 

    // Build the relevance expression. Using MySQL boolean to int conversion: (p.post_title LIKE %term%) evaluates to 1 or 0.
    // We SUM the weighted boolean expressions and also count meta matches SUM ensures multiple joined meta rows dont multiply absence/ presence incorrectly if duplicates exist.
    relevance_expr = SUM( (p.post_title LIKE {like_pattern})  {weight_title}
                           (p.post_excerpt LIKE {like_pattern})   . intval(weight_content) . 
                           (p.post_content LIKE {like_pattern})  {weight_content}
                           (pm_relevance.meta_value LIKE {like_pattern})  {weight_meta}
                     )

    // Add relevance to selected fields
    clauses[fields] .= , {relevance_expr} AS relevance_score 

    // Group by post to collapse any joined meta rows
    clauses[groupby] = p.ID

    // Order by computed relevance first, then fall back to date or other criteria
    clauses[orderby] = relevance_score DESC, p.post_date DESC

    // Optionally remove default search WHERE if WPs s param is present and you want to replace it
    // clauses[where] = preg_replace(/s ANDs (?s({wpdb->posts}.post_title.)/i, , clauses[where])

    // Save the final WHERE fragment on the query so we can reuse it for counting (found_posts)
    query->set(my_custom_relevance_sql_where, clauses[where])
    query->set(my_custom_relevance_sql_join, clauses[join])

    return clauses
}

/
  Recalculate found_posts when we used custom relevance (because GROUP BY changes WPs count).
 /
function my_custom_relevance_found_posts(found_posts, query) {
    global wpdb

    if (! query->get(custom_relevance) ) {
        return found_posts
    }

    where = query->get(my_custom_relevance_sql_where)
    join  = query->get(my_custom_relevance_sql_join)

    if (empty(where)) {
        return found_posts
    }

    // Build a COUNT(DISTINCT p.ID) query using the same join and where clauses but without LIMIT/OFFSET
    sql = SELECT COUNT(DISTINCT p.ID) FROM {wpdb->posts} AS p {join} WHERE 1=1 {where}
    count = (int) wpdb->get_var(sql)

    return count
}

/
  Example usage:
  q = new WP_Query(array(
    post_type => post,
    posts_per_page => 10,
    custom_relevance => true,
    relevance_term => blue bicycle,
  ))
 /
?>

Explanation of the key parts

• We attach to posts_clauses to modify SQL fields, join, groupby, orderby. The code only runs when the custom_relevance query var is present.
• wpdb->esc_like esc_sql are used to safely quote user input for LIKE pattern building.
• We left-join postmeta for a particular meta_key so that meta matches will contribute a small weight.
• We SUM weighted boolean expressions to form a numeric relevance_score. Using GROUP BY ensures duplicates from multiple meta rows do not multiply posts.
• We store the WHERE and JOIN on the WP_Query object so we can run a COUNT(DISTINCT p.ID) later to compute correct found_posts for pagination.

Example B — Faster, higher-quality scoring with FULLTEXT MATCH (requires FULLTEXT indexes)

Use MATCH() AGAINST() to compute relevance per-field and apply weights. This requires that you add FULLTEXT indexes to wp_posts (or your target table/columns) and optionally to wp_postmeta (only for long text fields). MATCH is orders-of-magnitude faster and performs tokenization, stopword handling and relevance ranking.

Important: Adding FULLTEXT indexes to core tables has trade-offs: backup test before applying on production, consider adding a dedicated search table instead.

-- Example: add a FULLTEXT index for posts title content (run once with caution)
ALTER TABLE wp_posts ADD FULLTEXT KEY ft_posts_title_content (post_title, post_content)

get(ft_relevance) ) {
        return clauses
    }

    term = trim((string) query->get(relevance_term))
    if ( === term) return clauses

    // Prepare the standalone boolean-mode query. For phrase or complex queries, build it safely.
    // For safety, escape quotes in the term and remove dangerous characters as needed.
    term_esc = esc_sql( str_replace(, , term) ) // crude sanitization for example

    // Weigh title heavier than content. MATCH() returns a score multiply by weight.
    title_weight = 5
    content_weight = 1

    // MATCH against post_title and post_content. Using IN BOOLEAN MODE for faster boolean-like matching.
    title_match = MATCH(p.post_title) AGAINST ({term_esc} IN BOOLEAN MODE)
    content_match = MATCH(p.post_content) AGAINST ({term_esc} IN BOOLEAN MODE)

    // Combine weighted MATCH scores and optionally add meta/term matches (smaller weight).
    // Example combining: higher weight on title
    relevance_expr = ({title_weight}  {title_match}   {content_weight}  {content_match})

    // If you want to add taxonomy terms into scoring, join term tables and add a boolean match on terms.name.
    // Example join for terms (optional):
    clauses[join] .=  LEFT JOIN {wpdb->term_relationships} tr ON (tr.object_id = p.ID)
                          LEFT JOIN {wpdb->term_taxonomy} tt ON (tt.term_taxonomy_id = tr.term_taxonomy_id)
                          LEFT JOIN {wpdb->terms} t ON (t.term_id = tt.term_id) 

    // Add the expression to fields
    clauses[fields] .= , {relevance_expr} AS relevance_score

    // Group by to collapse joins
    clauses[groupby] = p.ID

    // Order by relevance_score desc
    clauses[orderby] = relevance_score DESC, p.post_date DESC

    // Save join/where for found_posts recalculation
    query->set(my_ft_relevance_sql_where, clauses[where])
    query->set(my_ft_relevance_sql_join, clauses[join])

    return clauses
}

function my_ft_relevance_found_posts(found_posts, query) {
    global wpdb

    if (! query->get(ft_relevance) ) {
        return found_posts
    }

    where = query->get(my_ft_relevance_sql_where)
    join  = query->get(my_ft_relevance_sql_join)

    if (empty(where)) return found_posts

    sql = SELECT COUNT(DISTINCT p.ID) FROM {wpdb->posts} AS p {join} WHERE 1=1 {where}
    count = (int) wpdb->get_var(sql)
    return count
}

/
  Usage:
  q = new WP_Query(array(
    post_type => post,
    posts_per_page => 10,
    ft_relevance => true,
    relevance_term => search phrase,
  ))
 /
?>

Notes on MATCH() AGAINST()

• CREATE FULLTEXT indexes on the columns you MATCH() against. Without them, MATCH() may produce no results or be slow.
• Match scoring values are implementation-dependent combining scores by multiplying by weights is common.
• MySQL fulltext has stopwords and minimum word length tune server settings if necessary or use an external search engine.

Including taxonomy terms in scoring

To include taxonomy names (category, tags, custom taxonomy) in the score, join the term tables and add matches on t.name (or MATCH against t.name). Example concept:

// join term tables (example snippet)
clauses[join] .=  LEFT JOIN {wpdb->term_relationships} tr ON (tr.object_id = p.ID)
                      LEFT JOIN {wpdb->term_taxonomy} tt ON (tt.term_taxonomy_id = tr.term_taxonomy_id)
                      LEFT JOIN {wpdb->terms} t ON (t.term_id = tt.term_id) 

// then include (t.name LIKE %term%)weight in relevance expression

Correct pagination and found_posts

When you modify SQL (especially adding GROUP BY), WPs default found_posts (the total number of results used for pagination) may be wrong because WordPress uses an internal COUNT query that doesnt account for your custom GROUP BY or JOINs. The examples above demonstrate saving the WHERE and JOIN fragments and running a separate COUNT(DISTINCT p.ID) query in a found_posts filter to return the correct total. This is the safest approach.

Performance tips and production readiness checklist

• Limit the query to required post_types and post_status to reduce scanned rows.
• Prefer MATCH() AGAINST() with FULLTEXT indexes for larger sites.
• Consider incremental precomputed relevance (denormalized scores) for static ranking that only updates on content change.
• Use transient caching for expensive queries where appropriate (cache query result IDs or HTML output for a short time).
• Monitor slow queries (use slow query log or New Relic) and tune indexes/queries accordingly.
• Protect against SQL injection by using wpdb->esc_like, esc_sql, and avoiding raw user input in SQL.
• Test on a dataset representative of production scale.

Advanced: multi-term tokenization and phrase boosting

For more precise relevance, split the user search into tokens and compute weighted matches for phrase matches and per-token matches. For example:

• Boost exact phrase matches (title/phrase match) strongly.
• Add incremental weight for each token present in title, content and meta.
• Use MATCH AGAINST on the full phrase for phrase score and use boolean operators for token presence.

// Example conceptual snippet (not full code):
tokens = preg_split(/s /, term)
token_clauses = array()
foreach (tokens as t) {
    esc = esc_sql( wpdb->esc_like(t) )
    token_clauses[] = (p.post_title LIKE %{esc}%)3   (p.post_content LIKE %{esc}%)1
}
token_expr = implode(   , token_clauses)
// Phrase boost:
phrase_esc = esc_sql(wpdb->esc_like(term))
phrase_expr = (p.post_title LIKE %{phrase_esc}%)20
relevance_expr = SUM( {phrase_expr}   ({token_expr}) ) AS relevance_score

Fallback: avoid altering core search where possible

If you only need to reorder results from a pre-existing query (for example you already have a list of posts from another query), consider computing the relevance in PHP for that result set and sorting with usort. This avoids complex SQL changes but is only feasible for reasonably small result sets (e.g., < 500 items).

// Simple PHP fallback: compute relevance for an array of WP_Post objects and sort
function compute_simple_relevance(post, term) {
    score = 0
    t = strtolower(term)
    if (false !== stripos(post->post_title, term)) score  = 10
    if (false !== stripos(post->post_content, term)) score  = 5
    // inspect meta
    meta = get_post_meta(post->ID, your_meta_key, true)
    if (meta  false !== stripos(meta, term)) score  = 2
    return score
}
usort(posts, function(a, b) use (term) {
    return compute_simple_relevance(b, term) <=> compute_simple_relevance(a, term)
})

Edge cases and debugging

• Duplicate posts: ensure GROUP BY p.ID or DISTINCT when joining repeating rows.
• Empty relevance (0) for all results: check that your LIKE patterns or MATCH queries match your data and that FULLTEXT indexes exist when using MATCH.
• Pagination wrong: check found_posts recalculation as shown above.
• Slow query: run EXPLAIN on your final SQL and profile indexes.
• Character encoding: ensure MySQL and PHP are using compatible character set/collation for accurate matching.
• Stopwords/minimum length (FULLTEXT): may exclude short tokens consider tokenizing manually or adjusting server settings.

When to use an external search engine

Consider external search engines when you need:

• Near-instant indexing on large content sets with advanced ranking and faceting.
• Typo-tolerance, synonyms, language analyzers, fuzzy matching and complex relevance tuning.
• Real-time analytics on search queries and user behavior to refine ranking.

Quick reference summary

Useful references

• WordPress posts_clauses hook
• WP_Query reference
• wpdb reference
• MySQL fulltext search docs

Conclusion

Ordering results by a custom relevance score in WordPress is a powerful technique to deliver better search and ranking. Choose an approach that balances simplicity and performance: start with a LIKE-based weighted approach for small sites or prototypes, upgrade to FULLTEXT MATCH for larger content, and consider external search engines for production-grade, feature-rich search solutions. Always escape inputs, test on representative data, and ensure pagination counts are accurate when you modify SQL.

	Acepto donaciones de BAT's mediante el navegador Brave 🙂