I have a bootstrap table with a column (called: prio) where I can enter a number in a text input.
Like this:
This column should be sorted from low to high numbers, so first 1 then 2 then 3 etc. But if I sort that way, it first shows the empty cells, and at the bottom it shows 1,2,3 etc.
How can I make sure the empty cells are ignored in the sort?
Hello,
Where is the data coming from and are they empty strings or null values?
Data is coming from a server action with a database query. It is an integer field in the database.
The empty values are ‘null’ in the database
I am afraid that won’t be possible.
nulls are considered lower than the rest of the numbers.
Maybe a workaround could be setting a default value for empty fields, like 999 so that they always stay at the end.
Is this not possible because of Wappler itself or does all coding with databases have the same problem?
Giving it a 999 is not ideal. My end goal is to sort the orders (=rows) that we need to process, so a human always has to give the priority to an order so that the staff knows which order to process first. Is there another way to achieve this?
But do you need to show the rows with null values in these fields?
When there is a new order, there is no priority assigned to the order. So that is why the fields have a null value. Because my staff should manually set the priority of that order, after it’s created.
So yes the rows with null values should be visible, otherwise no priority can be assigned.
Ok, i understand.
Unfortunately there is no way to tell the databse query to ignore nulls, so when the values are sorted by ASC order, they will always load first.
Is this a Wappler limitation? Because a google search seems to give me some solutions, but it requires raw coding.
In other words, can this be a feature request?
When you have NULL values in the column and a sort is applied, the decision whether the NULLs should come first or last varies per database type.
MARIADB / MYSQL / SQLSERVER
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes first before any non-NULL value; using DESC order, the NULL comes last.
DB2 / ORACLE / POSTGRESQL
If you specify the ORDER BY clause, NULL values by default are ordered as more than values that are not NULL. Using the ASC order, a NULL value comes after any non-NULL value; using DESC order, the NULL comes first.
And yes, there are some “hacks” for this using hand coding but, that’s not currently possible in Wappler as it just uses what the database returns.
How about have a filter on the page greater than or equal to 1? Then have a separate view for records that do not fit that criteria? ie View ‘Awaiting Priority’ for example?
Good one, thanks!
No problem Nevil. You could have two separate queries with two tables on the page. One for those with a Priority value and one for those without, allowing for both circumstances. This way attention can be drawn to those records, specifically, without a Priority, and those with a Priority set. Hide either or table based on if Results exist for instance? This way if there are no records without a Priority set the table won’t be displayed, and if there are results it will be displayed…
In this circumstance I usually go in to File Manager find the original query (Action file) and save it as a copy (rename it accordingly), then go in to Server Connect and adjust the filter (on the copy of the original Action), save and upload. Then use this copy with the slight variation to produce the second set of results. That way you can copy the table and paste it, change it’s Action, and everything works as it should without recreating everything again without too much time wasted.
I frequently need to manipulate the sort order - eg where certain characters should be excluded from the beginning of field. Before Wappler, I would do this by using trim and other functions in the query. This is not possible in Wappler - so I would say it is a limitation of Wappler. Using a generated field to sort on (at least in MySQL) is a good workaround.
@Dave has given you a good alternative, but if you did want a single list with NULL values coming last, this would also be possible.
There is an existing feature request which would address issues like this.
Hi Tom,
What you are referring to:
Has nothing to do with the topic and the fact how MySQL sorting handles null values, which I already explained in my last post 
Not sure why you even mention something like this in this topic…
Quite often people ask a specfic question which has a more general solution or application (for this reason, you frequently change the topic title). While this question was specific, @nevil asked if a limitation in Wappler was the issue - it is, but there is a workaround which can be used to address his issue and any other issues people might have related to sorting. I considered this a potentially useful contribution.
@Dave offered an alternative approach. You explained how Nulls are treated in various databases. I gave a solution to the question. That’s why I mentioned it.
The problem is going off topic with an issue totally not related with the original one - i.e. how mysql database is handling NULLs on sorting