Ignore empty values when sorting in server-side

tryx

I'm trying to change the order () method inside ssp.class.php so that when a datatable is sorted, if column has blank values, it'll always end up at the bottom. A clientside implementation of this can be seen here: http://jsfiddle.net/j4drozuh/12/
On server-side, I believe the query need to be modified so that the query looks something like this:

select * from table order by if(field = '' or field is null,1,0),field

But I'm having trouble modifying the order () function within ssp.class.php and make it work. This is the order () function:

    static function order ( $request, $columns )
    {
        $order = '';

        if ( isset($request['order']) && count($request['order']) ) {
            $orderBy = array();
            $dtColumns = self::pluck( $columns, 'dt' );

            for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
                // Convert the column index into the column data property
                $columnIdx = intval($request['order'][$i]['column']);
                $requestColumn = $request['columns'][$columnIdx];

                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];

                if ( $requestColumn['orderable'] == 'true' ) {
                    $dir = $request['order'][$i]['dir'] === 'asc' ?
                        'ASC' :
                        'DESC';

                    $orderBy[] = '`'.$column['db'].'` '.$dir;
                }
            }

            if ( count( $orderBy ) ) {
                $order = 'ORDER BY '.implode(', ', $orderBy);
            }
        }

        return $order;
    }

Any help is appreciated!

allan

Line 22 in your code above is where you would need to add your if statement. Is it MySQL you are using for the database? What have you tried there?

Thanks,
Allan

tryx

Hi allan,
Yes, I understand line 22 is where I have to make the modifications. However I'm not able to work out how it should be modified. And yes, I am using MySQL as database. I tried several combinations to equate each terms within $orderBy to '' or null, but none of it worked and threw syntax errors. That SQL query is hard for me to grasp with multiple columns being imploded together .

allan

I haven't tried it locally, but this should match the syntax you had:

orderBy[] = 'if (`'.$column['db'].'` or '.$column['db'].' is null, 1, 0), '.$column['db'];

That doesn't specify the order direction, but the original statement didn't either, so I guess you have that somewhere else?

Allan

abushelmet

@allan I'm having similar issues as OP. When I use your orderBy syntax, the sorting only happens once. I believe it's lacking the order direction. How would I include that into the statement?

abushelmet

Update on my previous comment, I added $dir to @allan 's code and made it work:
$orderBy[] = 'if (`'.$column['db'].'` or '.$column['db'].' is null, 1, 0), '.$column['db'].' '.$dir;

allan

Many thanks for the update