Sorting MySQL results for diversity

Question

I have a table called Classes which stores information on College classes. It has the columns Department, Course, and Section. I currently have an autocomplete where users can enter classes. For example if they type "ECON" it shows ECON-E 201 12345, etc. The autocomplete query is LIMIT'ed to 10.

Now, the problem with my autocomplete is that it doesn't give diversity in its responses. If they type "E" it will show 10 ECON classes, but not ENG (English) classes. Is there a way to sort the response to give as many departments, courses, and sections (in that order) as possible?

Answer 1

There is a way to do this although it depends on where you want to do it at. For example do you want MySQL to do the delimiting or do you want to push this to the server side scripting to delimit/diversify your data?

Personally I see as a possibility, querying the database one or two times with different queries to diversify your data. Such as "SELECT * FROM classes WHERE couse like e" and then potentially take the first result such as "ECON..." and on next query do perhaps "SELECT * FROM classes WHERE !(course like "ECON")"

If you want server side scripting diversification it will require querying a much larger set of data instead of the 10. Lets say 50. From this you could return 0,1,2,3,4,20,30,40,49,50 of the result set. This might not be most intelligent way and it also requires you to pull a larger data set from database.

Original SELECT:

SELECT Class_ID, Department_Code, Course_Code, Class_Code, FROM Classes WHERE REPLACE(REPLACE(REPLACE(CONCAT(Department_Code, Course_Code, Class_Code), '-', ''), '(', ''), ')', '') LIKE '%". $q ."%' LIMIT 10

Diversify Query: (I will use to replace the above original to keep it shorter)

<originalQuery> UNION SELECT * FROM Classes WHERE SUBSTRING(<originalQuery>,1,1) LIKE '%".$q."%'

This might not be exactly it, but basic idea is you want your original result set (left side of the union) along with the second query where I take the result and call a substring on it so in your example probably "E" is like the user query. Hopefully this would give make a mixed set, I feel its not quite right but it should give you an idea of what you can do.

Answer 2

It depends also on the setup of your database. If you have a separate field for the names (such as "ECON"), then you could do:

SELECT DISTINCT course FROM classes WHERE UPPER(course) LIKE 'E'

Looking at your comment, how does this work for you? (I'm only checking against Department_Code, but in place of that, you could have your REPLACE line...)

SELECT Class_ID, Department_Code, Course_Code, Class_Code FROM Classes GROUP BY CASE WHEN (SELECT COUNT(DISTINCT Department_Code)=1 FROM Classes WHERE Department_Code LIKE '%" . $q ."%') THEN Class_ID ELSE Department_Code END HAVING Department_Code LIKE '%" . $q ."%' LIMIT 10

This will return only a single record for each of the Departments that begin with 'E', but if the user enters 'ENG', then all of the courses for 'ENG' will be pulled.