Closed simonw closed 3 years ago
I can do this with a regular expression. I'm going to assume that the column name is double-quoted, since that's the generated SQL I'll be using for order by clauses, so the expression can be quite simple:
re.compile(' order by "[^"]+"( desc)?$')
This looks like it works:
In [4]: sql = """select * from (
...: select * from (
...: select id, slug, link_url, link_title, via_url, via_title, commentary, created, metadata, search_document, import_ref, card_image from blog_blogmark
...: ) as results order by "link_title"
...: ) as results order by "link_url" desc"""
In [21]: r = re.compile('(^.*) order by "[^"]+"( desc)?$', re.DOTALL)
In [24]: print(r.match(sql).group(1))
select * from (
select * from (
select id, slug, link_url, link_title, via_url, via_title, commentary, created, metadata, search_document, import_ref, card_image from blog_blogmark
) as results order by "link_title"
) as results
I'm going to attempt match the regex. If it matches, I'll use the captured group and add the order-by on the end. If it doesn't match, I'll wrap the whole thing in a sub-query and stick the order by on that.
Here are the tests I wrote for this: https://github.com/simonw/django-sql-dashboard/blob/b88350fb8f214365eeaec47c46c2576f8e036281/test_project/test_utils.py#L22-L52
Currently if you click "sort ascending" and then "sort descending" in the cog menu you get SQL something like this:
Each sort wraps the previous sorted query in another nested query.
We can do better than this! Sorting really complex queries with
with (...)
in them and suchlike is hard, but we can at least notice if the query we are re-sorting ends withorder by X
already and replace that clause rather than wrapping the whole thing.