Add click to sort by column in the results window

[alexmalins]

A really useful feature of Datagrip/DBeaver is the ability to sort the results of a query by one column, ascending then descending, from the column title in the results window. Very handy when EDAing query results.

In Harlequin this could be implemented by clicking / terminal cursoring then enter on each column title in the results window.

Harlequin would have to under the hood wrap the user's query in a CTE/subquery, then SELECT * ... ORDER BY {col_name} ASC/DESC then requery the database to get the ordered results.
If fetch_all is being used sorting could also be done directly on the PyArrow results table without a requery.

Is a feature like this feasible Ted? Happy here to contribute code/work on this if it's a goer. 🙌

[tconbeer]

it is totally possible. I actually think the data table already implements sorting (without going back to the db), we just don't have an interface for it yet. I think sorting just the data in the table is the best approach here; I don't like datagrip's behavior of rewriting queries by default, since the conflict with limit can get confusing (I would also like this to be near-instantaneous).

The changes would mostly be in the upstream project, https://github.com/tconbeer/textual-fastdatatable; if we want to make this change configurable in that project, we would have to add the right config when initializing the table in Harlequin.

There is potentially a related issue, tconbeer/textual-fastdatatable#49, which is that you can't actually select the header row in the data table with the cursor at the moment.

The bigger question in my head is if a simple click on the header should sort the table or select the whole column. I'd like to see some research here. As a start:

Sorts on Header Click

• Datagrip (shows sort icon in header always)
• TablePlus (no sort icon)
• Metabase (from a dashboard - shows sort icon in header)

Selects on Header Click

• Snowflake/Snowsight (can sort using hover menu with second click)
• SSMS (can't easily sort)
• Excel/Sheets (normal mode)

No-ops on Header Click

• BigQuery console (can't select cells at all - can sort using sort icon)
• Excel/Sheets (table mode - can sort using sort icon)

Opens Context Menu on Click

• Metabase (from a Question)

Alternatives

Other options for sorting on a column:

• a key binding
• add an arrow icon in each column header; sort only on clicks of that icon (the coordinate math here could get fun)
• a right-click on the header
• a context menu that opens on a header click (or right click) or a key binding
• another UI element, maybe a dropdown to select a column to sort by (don't love this)

Other options for selecting a column:

• a key binding (sadly excel's ctrl+space doesn't work, at least on my terminal; it sends the null character)
• a context menu that opens on a header click (or right click) or a key binding
• currently: ctrl+up, ctrl+shift+down

[alexmalins]

I think sorting just the data in the table is the best approach here; I don't like datagrip's behavior of rewriting queries by default, since the conflict with limit can get confusing (I would also like this to be near-instantaneous).

Ok - sorry I was confused. Datagrip & DBeaver actually have that behaviour in the SQL editor windows:

• they only locally sort the results of the custom SQL query the user has executed, and don't re-query with a new ORDER BY
• But in the table explorer / viewer windows they do re-query. E.g. if you sort desc on id it will return the top id rows in the whole table, and not just the top rows within the x (e.g. 500) rows it had pulled unordered first.

So if Harlequin did the former it would be equivalent to Datagrip & DBeaver's behaviour.

Sorts on Header Click

Databricks UI does this (again no re-query)

Opens Context Menu on Click

• Metabase (from a Question)

DBeaver does this - by clicking on a little down triangle at the right end of the column header.

[tconbeer]

I'm good to sort on click! Thanks for continuing the research here