Complex search in MySQL

Question

I need to make a complex search in the MySQL database. I would like to solve it with a query to avoid most of the changes in PHP code.

This is how it should work:

1. User enters text in the search field
2. System searches in keywords
3. System searches in names
4. System searches in text
5. System searches in categories.

If the system finds a match in the keywords: 1 point, if it is also in the name: 1 point, if it is also in the text: 1 point/mention, if it is also in category: 1 point.

The user with the most points is first place, the user with the second most points is in second place, and so on

Is this query doable using IF, ELSE, CASE, WHEN? I've never done this kind of a complex search. Problem to be bigger, I'm looking at two tables:

TABLE USERS:
id    |    name    |    lastname    |    category    |    keywords    |    text    |
TABLE PAGES:
id    |    user_id    |

I need to list table "users" but IF user have own pages to first display users who have "pages" and after that to list others who don't have "pages".

Answer 1

You can do this with a simple order by. Here is an example using like, just for the search term:

select u.*
from users u
order by ((keywords like '%@SEARCHTERM%') +
          (name like '%@SEARCHTERM%') +
          (text like '%@SEARCHTERM%') +
          (category like '%@SEARCHTERM%')
         ) desc;

To get information about pages, join that in:

select u.*
from users u left outer join
     (select user_id, count(*) as numpages
      from pages p
      group by user_id
     ) p
     on u.id = p.user_id
order by (p.numages > 0) desc,
         ((keywords like '%@SEARCHTERM%') +
          (name like '%@SEARCHTERM%') +
          (text like '%@SEARCHTERM%') +
          (category like '%@SEARCHTERM%')
         ) desc;

The conditions in the order by use the fact that MySQL treats boolean values like numbers, with 1 for true and 0 for false. You can add them up to get total of the matches.

This example uses like. You can do something similar with match . . . against if you prefer full text search.

EDIT:

If you are concerned that the columns might contain NULL, then use coalesce():

order by (p.numages > 0) desc,
         ((coalesce(keywords, '') like '%@SEARCHTERM%') +
          (coalesce(name, '') like '%@SEARCHTERM%') +
          (coalesce(text, '') like '%@SEARCHTERM%') +
          (coalesce(category, '') like '%@SEARCHTERM%')
         ) desc;

Answer 2

Yes, it's possible.

I take the specification provided to mean that if a match is not found in keyword column, points is 0. If match is found in keyword but not in name column, points is 1. If match is found in both keyword and name column, but not in text column, score is 2. etc. (I may have misunderstood the specification, attributing more meaning to the order the matches were listed, and the "if it is also in"... if a match is found in name, but not in keywords, then we wouldn't say "it also matches name".)

One alternative, using MySQL IF() function.

SELECT t.id
     , t.name
     , t.lastname
     , t.category
     , t.keywords
     , t.text
     , IF(t.keywords LIKE '%text%'
         ,IF(t.name LIKE '%text%'
            ,IF(t.text LIKE '%text%'
               ,IF(t.category LIKE '%text%'
                  ,4
                  ,3)
               ,2)
            ,1)
         ,0) AS `points`
  FROM `users` t
  LEFT
  JOIN ( SELECT p.user_id FROM pages p GROUP BY p.user_id ) q
    ON q.user_id = t.id
HAVING `points` > 0
 ORDER
    BY `points` DESC, q.user_id IS NULL DESC

The equivalent can be achieved with an ANSI standard CASE expression

SELECT t.id
     , t.name
     , t.lastname
     , t.category
     , t.keywords
     , t.text
     , CASE WHEN t.keywords LIKE '%text%' THEN
         CASE WHEN t.name     LIKE '%text%' THEN
           CASE WHEN t.text     LIKE '%text%' THEN
             CASE WHEN t.category LIKE '%text%' THEN
               4
             ELSE 3 END
           ELSE 2 END
         ELSE 1 END
       ELSE 0 END AS `points`
  FROM `users`
  LEFT
  JOIN ( SELECT p.user_id FROM pages p GROUP BY p.user_id ) q
    ON q.user_id = t.id
HAVING `points` > 0
 ORDER
    BY `points` DESC, q.user_id IS NULL DESC

---

FOLLOWUP

If the specification means that if the user supplied text occurs in any of the four columns, and the total number of "points" for each row is the number of columns in the row that had a match, then we can use simple addition of the conditional tests, rather than nesting them.

SELECT t.id
     , t.name
     , t.lastname
     , t.category
     , t.keywords
     , t.text
     , IF(t.keywords LIKE '%text%',1,0) +
       IF(t.name     LIKE '%text%',1,0) +
       IF(t.text     LIKE '%text%',1,0) +
       IF(t.category LIKE '%text%',1,0) AS `points`
  FROM `users` t
  LEFT
  JOIN ( SELECT p.user_id FROM pages p GROUP BY p.user_id ) q
    ON q.user_id = t.id
HAVING `points` > 0 WHERE 
 ORDER BY `points` DESC, q.user_id IS NULL DESC

This query (like the prior queries) will list the users that had the highest calculated value for points first, then by whether or not there's a matching row found in the pages table. (The expression q.user_id will be NULL if there are no rows in pages that match on the id from users, and will be non-NULL if there was a match. So ordering by the expression "q.user_id IS NULL" will sort the users with pages before users without pages.