Smarter sorting that doesn't keep nesting the sorts

[simonw]

Currently if you click "sort ascending" and then "sort descending" in the cog menu you get SQL something like this:

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

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 with order by X already and replace that clause rather than wrapping the whole thing.

[simonw]

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)?$')

[simonw]

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

[simonw]

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.

[simonw]

Here are the tests I wrote for this:

django-sql-dashboard/test_project/test_utils.py

Lines 22 to 52 in b88350f

	@pytest.mark.parametrize(
	"sql,sort_column,is_desc,expected_sql",
	(
	(
	"select * from foo",
	"bar",
	False,
	'select * from (select * from foo) as results order by "bar"',
	),
	(
	"select * from foo",
	"bar",
	True,
	'select * from (select * from foo) as results order by "bar" desc',
	),
	(
	'select * from (select * from foo) as results order by "bar" desc',
	"bar",
	False,
	'select * from (select * from foo) as results order by "bar"',
	),
	(
	'select * from (select * from foo) as results order by "bar"',
	"bar",
	True,
	'select * from (select * from foo) as results order by "bar" desc',
	),
	),
	)
	def test_apply_sort(sql, sort_column, is_desc, expected_sql):
	assert apply_sort(sql, sort_column, is_desc) == expected_sql