MySQL sorting problem for autocomplete

Question

I have autocomplete on my site where a user enters a class at his school. It then provides a drop-down list of classes in which there are words that they entered somewhere in their fields. The Classes table has the fields Class_ID, Term_ID, Department_Code, Course_Code, Class_Code, Course_Title, Instructor, which everyone is looking for according to the conditions that the user enters. Suppose the use introduces the words "international econ". He will then search for these two members in the fields as follows:

SELECT Class_ID, Department_Code, Course_Code, Class_Code, Course_Title, Instructor FROM Classes AND ((REPLACE(REPLACE(REPLACE(CONCAT(Department_Code, Course_Code, Class_Code), '-', ''), '(', ''), ')', '') LIKE '%international%' OR Instructor LIKE '%international%' OR Course_Title LIKE '%international%') AND (REPLACE(REPLACE(REPLACE(CONCAT(Department_Code, Course_Code, Class_Code), '-', ''), '(', ''), ')', '') LIKE '%econ%' OR Instructor LIKE '%econ%' OR Course_Title LIKE '%econ%')) LIMIT 10 

Two quick notes on this request. I am doing REPLACE () and CONCAT () so that users can enter depts / courses / sections as a unit, for example. ECON-101 (department and course together). In addition, I inserted a very simplified query that does not take into account how I am currently sorting and grouping.

However, my question is about how I can sort the results. I want the records corresponding to Class_Code to be returned at the top, then Course_Code, then Dept, then Course_Title, finally prof. In other words, I want the results to be sorted by the number of specifications that the user provides in autocomplete. If they know Class_Code, I would like it to be at the top.

I can’t think of a way to make this look that doesn’t require a huge request. So far, the only way I can do this is to overlay a heap of LIKE comparison for each term in each field, and then ORDERing with these aliases. This is equivalent to 5 times the number of words that the user enters into autocomplete. In other words, a huge ugly request.

Is there a simple and effective way to make this look? If there was something like the INSTR () function, which occupied multiple lines for searching, I could just simply compare all the words in the autocomplete with each field and always sort by 5 aliases created by this.

Any input is welcome, even if it is a general sentence that goes beyond the scope of the question. However, I want to note in advance that I cannot use something like FULLTEXT or a search engine such as Sphinx, because I do not feel that it is suitable for a non-intensive fast query like this. In addition, I cannot restructure my database. Therefore, I would like to do this with a good MySQL query for what I already have.

Thanks in advance.

Answer 1

Have you considered using the UNION operator? You can separate the different queries into your own SELECT statement, and then combine them all together using UNION. This will automatically save them in the groups that you want them to - in the order in which you execute the SELECT statements.

It can make your request large, but it must be effective.

Answer 2

ORDER BY takes several values, so just list the columns in the order you want:

 SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC 

Another possibility you can try is to write a SQL generator function. Sometimes, if a script is very ugly and needs to change, a function that generates a script in a modular format can make it easier to see what is happening.

Answer 3

you could do

 order by case when code like '%blabla%' then 0 else 1 end ,case when dept like '%blabla%' then 0 else 1 end ,case when title like '%blabla%' then 0 else 1 end 

Thus, all matches in the code will be sorted to matches in dept, etc.

Updated for comments. If I correctly understand the last comment, your problems are that you will repeat complex queries in the query? In this case, you can either wrap the replacement / concat, etc. In the view (see below), or create a view.

 select * from (select class_code ,department_code ,department_code ,replace(concat(...)) as code ,replace(concat(...)) as dept ,replace(concat(...)) as title from classes where class_code like '%blabla%' or department_code like '%blabla%' or class_title like '%blabla%' ) as wrapped order by case when code like '%blabla%' then 0 else 1 end ,case when dept like '%blabla%' then 0 else 1 end ,case when title like '%blabla%' then 0 else 1 end 

Just remember to do the actual search in real columns, not calculated columns, to make sure mysql can use indexes.

By the way, mysql doesn't care if the query is long and complex if the filters and joins look "simple".