search

Pegase745 / sqlalchemy-datatables

SQLAlchemy integration of jQuery DataTables >= 1.10.x (Pyramid and Flask examples)
MIT License
161 stars 65 forks source link

Configure SQL ORDER BY null value behavior #35

Closed enplotz closed 8 years ago

enplotz commented 8 years ago

Hi, I'm not sure if this is the right place, but after some hours of digging in the DT and SQLA docs, I think it is.

I want to configure the behavior when ordering a column that contains NULL values. For example, when ordering a year column, I want them to be displayed at the end. With DT's server side processing, this is not possible to configure in the JS source after what I read in the docs.

As per SQLA this should be done with calls to nullsfirst() and nullslast() around the asc() and desc() methods (see SQLElement).

In init.py#L332 the sort calls are made, so I think the null options should be included there. What do you think?

Pegase745 commented 8 years ago

I'll seek into it asap

Pegase745 commented 8 years ago

I think it's a good idea. thanks

Pegase745 commented 8 years ago

@enplotz could you try out the branch feature/nullsOrder please ?

I believe it will work with Oracle, Postgres but not Mysql nor SQLite.. :-/

marksher commented 8 years ago

This does work in Postgres as of 8.3.

For MySql the only thing I can find that will work is to lead the actual sort column with isnull() on the same field

select * from table_name order by isnull(column_name), column_name asc; select * from table_name order by isnull(column_name), column_name desc;

both work, as long as the desired effect is to keep nulls out of the sort completely.

Not sure if this is too much of a hack, but it's all I could find, figured I'd share in case anyone else comes across here looking.