Natural sort methd

[pankus]

I already asked something concerning custom sort functions (#99) and nowadays I would like to get back briefly on that point. My specific concern focuses on natural sorting. I was able to set up a natural sorting function in postgres (taken from here) and to use such a function sqlalchemy ( e.g. q=db.session.query(Table).order_by(func.public.naturalsort(Table.code)) ). My point now is: how to pass to the columns constructor some indication that a given column must be ordered by a custom sort function (already created in Postgres)? For instance, would it be possible something like this for columns containing alphanumeric strings: columns = [ ColumnDT(User.id),
ColumnDT(User.code, sort_method = "func.public.naturalsort"),
... ]

Does this option already exist or must be created?

[tdamsma]

This option does not exist, good suggestion. Didn't know postgres provided a natural sort method. Of course there are also more hacky more ways to do this, one I can think of is cast the integers to a space padded string, I think that would also solve it. But beng able to pass a custom sort function seams like a good suggestion to me. Would you be willing to give it a go and submit a PR? Best to branch of the v2 branch @Pegase745 is working on

[tdamsma]

One suggestion already, pass the sort function as a function, not a string, so

columns = [
    ColumnDT(User.id),
    ColumnDT(User.code, sort_method = func.public.naturalsort),
]

[tdamsma]

And even better probably:

columns = [
    ColumnDT(User.id),
    ColumnDT(User.code, sort_method = func.public.naturalsort(User.code)),
]

[Pegase745]

we might even get to merge nulls_order property with the new sort_method one

[pankus]

Well, I did not have time to set up a different dev environment (to be honest, I don't know yet how to integrate git branches in a working project), but the first tests look very promising. First of all, I followed the @tdamsma's instruction concerning how to pass the new sort fuction in the view.py. Then, after having added "sort_method" keyword to __new()__ in ColumnDT class, I simply inserted the following two lines of code in _self_sort_expressions, just before the code check for the sorting direction:

if column.sort_method is not None:
    sort_expr = column.sort_method

p.s. I need to check that column.sort_method is not None:, because the simple if column.sort_method: returns an error "Boolean value of this clause is not defined" (and I did not figure yet why)

Be that as it may, the code works quite well, with a very limited impact on the execution speed (18 vs 105 milliseconds).

[pankus]

@Pegase745's idea looks good but probably nulls_order would need a different name

[tdamsma]

Closed old issue