Yii2: QueryInterface: support for NULLS FIRST and LAST sorting in orderBy

[manquer]

It would be great if support for explict NULL sorting in orderBy is implemented
while not all SQL vendors implement this functionality it is defined by SQL:2003 standard extension T611, "Elementary OLAP operations" and some vendors do implement it ( Oracle and PostgreSQL both have ) and there are workaround methods for MySQL and MSSQL etc.

[cebe]

This should already work like this ->orderBy('example ASC NULLS FIRST').

[mdmunir]

@cebe
It will quoted. should be ->orderBy([new yii\db\Expression('example ASC NULL FIRST')]).
Next question, how doing in component yii\data\Sort?

[arogachev]

Using PostgreSQL.
The solution for yii\data\Sort that I used with SqlDataProvider:

use yii\db\Expression;
use yii\data\SqlDataProvider;

$dataProvider = new SqlDataProvider([
    'sort' => [
        'attributes' => [
            'current_week_count' => [
                'asc' => [new Expression('current_week_count NULLS FIRST')],
                'desc' => [new Expression('current_week_count DESC NULLS LAST')],
            ],
        ],
    ],
]);

[gadd33]

In ActiveDataProvider-> sort -> defaultOrder this method doesn't work. Any solutions?

[arogachev]

Set the sort attributes, in defaultOrder use just names of attributes, for example:

defaultOrder => ['current_week_count' => SORT_ASC],

You should not pass db expressions in default order.

[gadd33]

I need 'DESC NULLS LAST'. This method with new Expression(..) gives no results. defaultOrder is inactive. Do you have any idea?

[arogachev]

Show full code.

[gadd33]

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => ['pageSize' => 10],
            'sort'  => ['defaultOrder' => [new Expression('"finishTime" DESC NULLS LAST')]],
        ]);

PHP Notice – yiibaseErrorException
Undefined offset: 0

1. in C:wampwwwzbieraczvendoryiisoftyii2dataSort.php at line 213
   [...]
2. $definition = $this->attributes[$attribute];
3. $columns = $definition[$direction === SORT_ASC ? 'asc' : 'desc'];

[cebe]

 $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => ['pageSize' => 10],
            'sort'  => [
                'attributes' => [
                    'finishTime' => [
                        'asc' => [new Expression('finishTime NULLS FIRST')],
                        'desc' => [new Expression('finishTime DESC NULLS LAST')],
                   ],
                    // more attribute definitions here
                ],
                'defaultOrder' => ['finishTime' => SORT_DESC],
        ]);

[gadd33]

Great. Thank you!

[rakot]

For MySQL - NULLS LAST

$dataProvider->sort->attributes['rank'] = [
     'asc' => [new Expression('`rank` IS NULL ASC, `rank` ASC')],
     'desc' => [new Expression('`rank` IS NULL ASC, `rank` DESC')],
];

[edgardmessias]

My sugestion:

Make yiidbDirection like yiidbExpression:

Example:
yiidbQueryBuilder:

    public function buildOrderBy($columns)
    {
        if (empty($columns)) {
            return '';
        }
        $orders = [];
        foreach ($columns as $name => $direction) {
            $orders[] = $this->buildOrderByDirection($name, $direction);
        }

        return 'ORDER BY ' . implode(', ', $orders);
    }

    public function buildOrderByDirection($name, $direction)
    {
        if ($direction instanceof Expression) {
            return $direction->expression;
        }
        if ($direction instanceof Direction){
            //non supported DB
            $direction = $direction->direction;
        }
        return $this->db->quoteColumnName($name) . ($direction === SORT_DESC ? ' DESC' : '');
    }

For postgresql:

    public function buildOrderByDirection($name, $direction) {
        if ($direction instanceof \yii\db\Direction){
            return $this->db->quoteColumnName($name)
                    . ($direction->direction === SORT_DESC ? ' DESC' : '')
                    . ($direction->nullsFirst ? ' NULLS FIRST' : ' NULLS LAST');
        }
        return parent::buildOrderByDirection($name, $direction);
    }

For mysql:

    public function buildOrderByDirection($name, $direction) {
        if ($direction instanceof \yii\db\Direction){
            return  'ISNULL(' . $this->db->quoteColumnName($name) . ')'
                    . ($direction->nullsFirst ? ' DESC' : ' ')
                    . ', ' . $this->db->quoteColumnName($name)
                    . ($direction->direction === SORT_DESC ? ' DESC' : '');
        }
        return parent::buildOrderByDirection($name, $direction);
    }

Use:

$query->orderBy([
    'name' => new \yii\db\Direction(SORT_DESC, false),
    'date' => new \yii\db\Direction(SORT_DESC, true)
]);

Output:

-- postgresql
SELECT * FROM "operations" ORDER BY "name" DESC NULLS LAST, "date" DESC NULLS FIRST

--Mysql
SELECT * FROM `operations` ORDER BY ISNULL(`name`), `name` DESC, ISNULL(`date`) DESC, `date` DESC

I can make a pull request later

[klimov-paul]

Resolved by commit 473598ac8ccbc3f0782e41d0ad2a01abd4b55fb2