Mysql OR conditions and sort by relevance

Question

I'm doing a query with a dynamic amount of OR conditions depending on the amount of terms retrieved by $_GET[].

    if(isset($_GET['terms']))
    {
        $concat_search = 'AND wp_posts.ID = wp_term_relationships.object_id';

        $query_concat_strings .= 'AND (';

        $no_commas = rtrim($_GET['terms'], ", \t\n");

        $terms = explode(',', $no_commas);

        $total_terms = count($terms);

        for($x = 0; $x < $total_terms; $x++)
        {
            $term_id = term_exists($terms[$x], 'especialidad');

            if ($term_id !== 0 && $term_id !== null)
            {           
                if($x == 0)
                {
                    $query_concat_strings .= ' wp_term_relationships.term_taxonomy_id ='.$term_id[term_id];
                }
                else
                {
                    $query_concat_strings .= ' OR wp_term_relationships.term_taxonomy_id ='.$term_id[term_id];
                }
            }
        }
        $query_concat_strings .= ')';
    }

    $querystr = "
        SELECT $wpdb->posts.* 
        FROM $wpdb->posts, $wpdb->postmeta, $wpdb->term_relationships
        WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
        ".$concat_search."
        AND $wpdb->postmeta.meta_key = 'wpcf-fin-oferta'
        AND $wpdb->postmeta.meta_value > $currentdate
        AND $wpdb->posts.post_status = 'publish' 
        AND $wpdb->posts.post_type = 'oferta'".$query_concat_strings."
        ORDER BY $wpdb->posts.ID 
        LIMIT $start, $posts_per_page
        ";

//The query echoed: SELECT wp_posts.* FROM wp_posts, wp_postmeta, wp_term_relationships WHERE wp_posts.ID = wp_postmeta.post_id AND wp_posts.ID = wp_term_relationships.object_id AND wp_postmeta.meta_key = 'wpcf-fin-oferta' AND wp_postmeta.meta_value > 1456700400 AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'oferta' AND ( wp_term_relationships.term_taxonomy_id =50 OR wp_term_relationships.term_taxonomy_id =7)

This will return all values matching wp_term_relationships.term_taxonomy_id =50 OR wp_term_relationships.term_taxonomy_id =7, but I would like to order the results giving priority to those that match not only one but more conditions, being first those witch matches more conditions than others.

Have tried this:

SELECT wp_posts.*, SUM(IF(wp_term_relationships.term_taxonomy_id ='11',1,NULL)) AS cond_matched, SUM(IF(wp_term_relationships.term_taxonomy_id ='50',1,NULL)) AS  cond_matched, SUM(IF(wp_term_relationships.term_taxonomy_id ='7',1,NULL)) AS cond_matched FROM wp_posts, wp_postmeta, wp_term_relationships WHERE wp_posts.ID = wp_postmeta.post_id AND wp_posts.ID = wp_term_relationships.object_id AND wp_postmeta.meta_key = 'wpcf-fin-oferta' AND wp_postmeta.meta_value > 1456786800 AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'oferta' GROUP BY wp_posts.ID ORDER BY cond_matched;

But the results appear ordered completely randomly. The expected result should be: If I search for 1, 2, 3

Result 1: The one that contains all 3 values: 1,2,3,4,5,6,7 Result 2: Those having at least 2: 1,3,7,9 Result 3: those having only 1: 3,11,90,12

check that question http://stackoverflow.com/questions/16936891/sql-order-by-using-case-when-for-different-ascending-descending-and-cust solved before this will help you — Monier Shokry, Feb 29 '16 at 10:41
Sorry, maybe I'm too obfuscated right now but I can't see how can this help me, also I guess my explanation on the problem is not too accurate. My query has several conditions in WHERE clause and at the end I have this AND (condition1 = 'whatever' OR condition1 = 'whatever another' OR condition1 = 'whatever ever') the problem I have is that, whenever only one of this conditions is TRUE I will get results, but I would like to short them by relevance depending on how many coincidences have been found. If this explanation is more clear I will edit the question. — user997593, Feb 29 '16 at 11:18
If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Feb 29 '16 at 11:51

score 0 · Accepted Answer · answered Mar 03 '16 at 11:18

0

The only thing I needed was adding:

ORDER BY COUNT(*) DESC

At the end of the query. Obviously there was some missunderstanding, from my side, about the way Mysql works.

answered Mar 03 '16 at 11:18

user997593

423
5
16

Mysql OR conditions and sort by relevance

1 Answers1