Typesafe dynamic filtering/ordering/paging

[rspeele]

Right now there is no way to dynamically order queries.

This makes the library unsuitable for applications that let the user sort a paged table of records.
That's a lot of applications!

This should be solvable without giving up type safety. Here's the design I have in mind:

Any command that consists of a single SELECT statement which is not known to be a single-row SELECT should be dynamically orderable.

e.g.

type MyQuery = SQL<"select Column1, 1+1 as Column2 from SomeTable">

This means that it gets the following extra stuff in its generated type:

• A nested type MyQuery.By with a private constructor and an overrided ToString().
• Static getters MyQuery.By.Column1 and MyQuery.By.Column2, which return instances of MyQuery.By
• A static method MyQuery.By.OfString("Column1") which checks that the passed string is one of the statically known output column names. MyQuery.By.OfString(MyQuery.By.Column2.ToString()) should work.
• A static OrderBy method taking a MyQuery.By and a DU Ascending|Descending and returning a MyQuery.OrderedQuery
• MyQuery.OrderedQuery has an instance method ThenBy taking another by+direction, Page taking limit+offset
• MyQuery.OrderedQuery has an instance Command method which is like the regular MyQuery.Command(param1 = ...) method, but includes the orderings applied so far

Hypothetical usage:

type MyQuery = SQL<"select Column1, 1+1 as Column2 from SomeTable where Name like @name">

let example (conn : ConnectionContext) =
    MyQuery
        .OrderBy(MyQuery.By.Column1, Ascending)
        .ThenBy(MyQuery.By.Column2, Descending)
        .Page(25, 50)
        .Command(name = "%a%")
        .Execute(conn)

I think it would be OK to limit to one ThenBy clause. Orderings more complicated than that don't make much sense to generate dynamically.

Thoughts?

[rkosafo]

This will be a nice very very addition.

For the orderBy, is there any overhead in supporting more than two columns ?
If it has to be limited to .OrderBy(...).ThenBy(...), is it possible to add another function that takes a list to support any number of columns.

E.g .OrderWith ([MyQuery.By.Columns1, Ascending; MyQuery.By.Columns2, Descending; MyQuery.By.Columns3, Ascending])

This could work nicely with .OfString and a list of items to order by. I think I read somewhere about issues with generating DU from TP. Where the DU is not possible, we could even use bool.

With respect to the paging, one other piece of data that is also generated is the count of the records so the ui can properly show paging data when not using infinite scroll. Eg. showing 1-25 of 365. Not sure how it fits in here though.

[rspeele]

Excellent point about the total count for paging. I now think there should be a typesafe API for wrapping a static query as a subquery, letting you produce anything of the form:

SELECT (* | COUNT(*))
FROM (statically-compiled-query) x
[ WHERE runtime-expression ]
[ ORDER BY runtime-column-name1 (ASC|DESC) [ , runtime-column-name2 (ASC|DESC) [ , etc ] ]
[ LIMIT runtime-limit [ OFFSET runtime-count ] ]

As long as statically-compiled-query consists of a single SELECT statement.

Every database engine, even SQLite, can push down predicates and limit clauses into subqueries. They basically have to be good at this to support views, because otherwise views would be pretty useless. So I'm not too worried about the performance implications of putting these filters/limits in an outer query.

This would only work for queries that do not use navigation properties. With a MANY(...) nav property, limit/offset/count would be all messed up, plus you wouldn't be able to write meaningful filters using the columns of the nested objects.

These dynamic features would be most commonly used when you have a flat result set anyway (that you're displaying to the end user as tabular data), so not letting them be used on nav-property queries seems OK for now. Maybe in the future a subset of operations could be allowed -- no count/limit/offset, and only involving the columns that aren't under nav properties.