CODEWITHSUNDEEP
phpmysqlsearchsql-like
Pablo Cardozo
Pablo Cardozo

Reputation: 103

Mysql - Order query by relevance without full-text method

I want to know how can I achieve this. I need to order a query by relevance but I cannot do that, I do not want to use the full-text + boolean mode, etc because I have a lot of records that do not appears this way (50%+ of regs) and so on, so I've create a query in a loop to search word by word and I exclude the irrelevant words by myself before doing the query, the result of the process for two words query is something like this:

SELECT UsrNames, UsrLastNames 
FROM user
WHERE 1 AND (UsrNames LIKE '%FirstWord%'
            OR UsrLastNames LIKE '%FirstWord%'
            OR UsrCI LIKE '%FirstWord%'
            OR UsrEmail LIKE '%FirstWord%'
            OR UsrRUC LIKE '%FirstWord%'
            OR UsrCod LIKE '%FirstWord%'
            OR UsrPhone LIKE '%FirstWord%'
            OR UsrNames LIKE '%SecondWord%'
            OR UsrLastNames LIKE '%SecondWord%'
            OR UsrCI LIKE '%SecondWord%'
            OR UsrEmail LIKE '%SecondWord%'
            OR UsrRUC LIKE '%SecondWord%'
            OR UsrCod LIKE '%SecondWord%'
            OR UsrPhone LIKE '%SecondWord%') ORDER BY  UsrNames ASC 
 LIMIT 20
 OFFSET 0

It is right now in development, so the don't you care about the "WHERE 1" at the beginning.

Hope this great comunity could help.

Upvotes: 0

Views: 192

Answers (1)

Barmar
Barmar

Reputation: 782653

This will order by the number of matches:

ORDER BY IF(UsrNames LIKE '%FirstWord%', 1, 0)
        + IF(UsrLastNames LIKE '%FirstWord%', 1, 0)
        + IF(UsrCI LIKE '%FirstWord%', 1, 0)
        + IF(UsrEmail LIKE '%FirstWord%', 1, 0)
        + IF(UsrRUC LIKE '%FirstWord%', 1, 0)
        + IF(UsrCod LIKE '%FirstWord%', 1, 0)
        + IF(UsrPhone LIKE '%FirstWord%', 1, 0)
        + IF(UsrNames LIKE '%SecondWord%', 1, 0)
        + IF(UsrLastNames LIKE '%SecondWord%', 1, 0)
        + IF(UsrCI LIKE '%SecondWord%', 1, 0)
        + IF(UsrEmail LIKE '%SecondWord%', 1, 0)
        + IF(UsrRUC LIKE '%SecondWord%', 1, 0)
        + IF(UsrCod LIKE '%SecondWord%', 1, 0)
        + IF(UsrPhone LIKE '%SecondWord%', 1, 0) DESC,
    UsrNames ASC

FIDDLE

The IF() expressions convert each of the comparisons to a number and totals them. If you wanted, you could even make some fields more important in the ranking by giving them a higher value than 1.

Upvotes: 1

Related Questions