Should sorting be part of the search query?

Question

I'm developing a CRM software for my company, and I'm currently working on the third major release. The CRM handles lot's of items(customer problems, long-running projects, daily/weekly tasks etc.), each with it's own fields, and being able to search and sort those items is an important part of the workflow.

TL;DR

What is a simple yet powerful UI control to do filtering and sorting with multiple columns?

In the first major release, I added a sort-filter panel at the top of the page where each item-field had an input-field. For some item-fields which were multiple-selection out of a small list I made a checkbox for each option in the search panel.

Those checkboxes were a big mistake. Few months after the initial release, the managers wanted to add many options to those multiple-selection fields. It looked nice enough in development, when I had 5 options, but with 50+ options you couldn't even see the header row of the result table because the search panel was too damn high!

So, in the second major release I decided to turn the search panel into a "drop-down" panel - it appears as a simple row with textual summary of the current search&sort, but when you click on a button it opens up the big panel with all the options. I also ditched the checkboxes to and used combo-boxes instead.

This was much better than the first version, but not good enough, as the second major release also allowed adding custom fields to different types of items. The search panel was still nice when it was closed, but when opened it was so big and complex you had to use the browser's search to find the search-field you need.

So, for the third major release I'm going to implement a suggestion I got from one of the users, and use a single textbox for the searching. Typing regular text in the searchbox will perform a general search, and I'll introduce syntax for making more specific searches(searching by field, searching in a date-range etc.). With JQueryUI's Autocomplete I can make this intuitive enough.

My problem is - how to do the sorting UI? When I had huge searching panels with lot's of fields, I could add a big sorting control that lets the user sort by multiple fields. Now that I'm reducing the search UI to a single textbox, having that big sort control will be too big a burden on the layout.

I came up with a few options:

1. Add sorting syntax to the searchbox. This will be fairly easy to implement, and won't add additional burden on the UI. The downside is that it's far from intuitive - even basic sorts will require learning of the sorting syntax.

2. Add another textbox with sorting query. The problem is that unlike the searchbox, the sortbox requires some syntax even for basic sorts, since the user must be able to choose between ascending and descending orders.

3. Nerf the sorting. Instead of letting the user sort by multiple fields, limit them to a single field. This way I can make it with just a combobox and ascending&descending radio buttons. The downside is obvious - less power for the user.

4. Use the table that displays the results as the sorting control. Clicking on a column's header will sort by that column. Clicking again on the same column will reverse the sort. This will make the sort very intuitive, but the downside is technical - it'll require a trip to the server every time the user clicks on a column header. If the user wants to do a search and sort by two fields, it'll take 5 trips to the server(searching, sorting by X, sorting by X descending, sorting by Y, sorting by Y descending), each performing a query to the database. I can probably do some client-side caching to reduce that number to 3 and make those db queries quicker - but it's still worse than 1 trip in the previous options.

Any suggestions on which option I should pick? Or suggestions for more options?

Answer 1

I'd suggest using the 4th option. It is the most direct way to do the sorting, without any extra fields or radio buttons. It is the result columns that define the fields sorting is based on, after all. This approach is the most used option, as well, AFAIK.

To tackle the "technical difficulties", one could use client side sorting (no round-trips to server), caching in some appropriate level (no calls to actual servers/databases) or just plain getting more hardware to the backend; it's hard to give more detailed advice without knowing the environment and situation.