Yii Dataprovider date sorting

Question

I have ArrayDataProvider with field of type DateTime - birthdate. I use that dataprovider for a gridview in view section.

Since birthdates include the birthyear the sorting is not working as expected. Is there a way to somehow tell the sorting mechanism not to account years and only sort by month and day ? A custom sort function perhaps ?

Edit: Something like sort in C++ where you can pass the compare function.

Edited current solution description: My solution currently is to include a birthyear as a separate field in array and birthdate's years are set to current year. Now dates are from the same year and the sorting works. But separate field for birth year doesn't feel right. I wish i could get all necessary data from one date object.

This separate field grinds my gears. It's not perfect.

Edit: Oh, and its Yii2

Update There are also a PHP array sorting functions that take a compare callback - for example uasort that can be used on associative arrays like mine:

uasort($persons, function($a, $b){
    return strcasecmp( $b['date']->format('m-d'), $a['date']->format('m-d') );
});

Now i need to find the way to implement it into ArrayDataProvider. Any ideas here ?

Answer 1

If you have an array dataprovider you can not use sorting of the database of course.

Solution using Yii

To allow sorting by some expression you have to calculate the value beforehand and configure the sorting for the attribute to use the calculated value instead of the original one:

// assuming $data contains your data and 'birthdate' is the value you want to use for sorting

foreach($data as $key => $value) {
    $data[$key]['sort_birthdate'] = date('m-d', strtotime($value['birthdate']));
}
$dataProvider = new \yii\data\ArrayDataProvider([
    'allModels' => $data,
    'sort' => [
        'attributes' => [
            'birthdate' => [
                'asc' => [
                    'sort_birthdate' => SORT_ASC, 
                ],
                'desc' => [
                    'sort_birthdate' => SORT_DESC, 
                ],
                'label' => 'Date',
                'default' => SORT_ASC
            ],
            // list all other attributes here
        ],
    ]
]);

Solution extending Yii

If you want a custom comparison function, you have to extend the Yii class to support this. You can create a custom ArrayDataProvider class which extends from the one that comes with Yii and override the sortModels()-method.

Answer 2

You should try something like

use yii\db\Expression;
$dataProvider->setSort([
            'attributes' => [
................................
                'birthday' => [
                    'asc' => [
                        new Expression('DATE_FORMAT(birthday, "%m%d")') => SORT_ASC, 
                    ],
                    'desc' => [
                        new Expression('DATE_FORMAT(birthday, "%m%d")') => SORT_DESC, 
                    ],
                    'label' => 'Birthday',
                    'default' => SORT_ASC
                ],
................................
            ]
        ]);

I have not tried this.

What works for sure is to do something like this

$query->select([
                    new Expression('DATE_FORMAT(birthday, "%m%d") as show_date'), 
      .................................
                ]);

Then

    /**
     * Setup your sorting attributes
     * Note: This is setup before the $this->load($params) 
     * statement below
     */
     $dataProvider->setSort([
        'attributes' => [
            'id',
            'show_date' => [
                'asc' => [
                    'sort_date' => SORT_ASC, 
                ],
                'desc' => [
                    'sort_date' => SORT_DESC, 
                ],
                'label' => 'Date',
                'default' => SORT_ASC
            ],
            'price',
            'gst',
            'cost',
            'quantity',
            'profit',
        ]
    ]);