Pretty much every web app I've written has had some sort of search feature with a table of results, where the user can filter on multiple columns of the search. That requires either:

a. Dynamically adding "and SomeColumn like @someValue" to the where clause of the query

or

b. Having the query include all filters but ignore them when they are left blank, such as

`where @someValue is null or SomeColumn like @someValue`

The latter approach does allow you get by without string-building queries for filtering, but tends to perform worse if you have a lot of columns you might filter on.

And you still have to play the string concatenation game if you let the user click column headers to change the ORDER BY.

With a decent ORM, like Entity Framework for C#, any of this is very simple and there is no need to write SQL string manipulation code in your application. But under the covers that is what is happening.

Is this just a complaint about a lack of good tooling for SQL code? User-configurable columns are probably always going to be implemented as an array of columns that gets built at runtime and then sent to whatever database management system you're using. In any programming language and database system, isn't the only thing automatically preventing you from building a malformed array of columns at runtime some sort of type-checking or linting? It seems to be that this mistake is just as easy to make using an ORM or data access library without type-checking or linting.

The way LINQ in C# works allows a good amount of type-checking to be done on the expressions fed into the ORM. So you can compose queries in a much more sensible way than string concatenation. The only downside is that LINQ makes it easy to express certain things that SQL makes hard, so you can sometimes write LINQ queries that turn into monstrous SQL.

I authored a library for F# that did static typechecking of SQL in strings at compile time. So it could tell you if you, for example, mistyped a column name or selected a column that didn't exist on a table or passed a SQL function the wrong number or type of parameters, etc. That was nice, but it still sucked for trying to compose queries or dynamically add filtering/sorting to a query.

The main place I've run into this is a table with sorting, pagination and filtering while lazy loading data and I encounter this need a lot.

With some runtime query builder most databases will have decent performance for the space between "too much to reasonably load in memory" to "we need a dedicated query service like elastic". Unfortunately taking the example of sort by X then by Y where X and Y are dynamic I don't know of a nice solution in SQL only in MSSQL 2012 or MySQL 5.

I think you are playing into the comment, though. You'd be surprised how far you can go with a table that doesn't support sorting and filtering. Basic pagination can often be foregone, if you can instead resort to offering export and let them use whatever software they are used to for this purpose. (Pagination is also just easier if you forego custom sort and filtering.)

At some point, yes, you will want these things. But the basics can get you really far.

For sure you can offer a good approximation through SQL but I'm unlucky enough that I've needed to support dynamic sorting and query builders against tables at least 4 times for different datasets in the past years.

I'm particularly proud of the last one I built that still absolutely flies despite doing wildcard querying on text fields (no full text index) with multiple combined queries and a dynamically computed status from aggregate child properties in MySQL 5 with its lack of window functions. The solution was basically dynamically construct an efficient as possible inner query before applying the joins.

To clarify I believe in doing as much as possible in raw SQL and have a fondness for the stored procedure driven system I worked with but this problem remains unsolvable in SQL alone and I just keep encountering it.