What is recommended way to sort collection by related column?

Yii 2.0 REST APIs
1

I have simplest relation between 2 tables.
Item table has: id, identifier, type_id
Type table has id, name

I use expand=type to get type names. I want to sort items by type.name.
What is recommended way to do that?

[
    {
        "identifier": "item 1",
        "type": {
            "name": "type C"
        }
    },
    {
        "identifier": "item 2",
        "type": {
            "name": "type A"
        }
    },
    {
        "identifier": "item 3",
        "type": {
            "name": "type B"
        }
    }
]
2

Sort it at model level. If it is a relation call orderBy in relation method

public function getTypes()
{
    return $this->hasMany(Type::className(), ['id' => 'identifier'])
                ->orderBy('name DESC');
}
3

I need to do that dynamically: ?expand=type&sort=type.name or &sort=-type.name
What is easiest way to do that?

4

Never done that before and not sure if it works or not.
I’ll leave that to whoever is more competent.
I will however suggest creating new action with sort as query string and do it manually!

5

In your data provider you can prepare custom attribute and add it as a sort option like

new ActiveDataProvider(
[
    // ...
    'sort' => [
        // ...
        'attributes' => [
            // ...
            'type.name' => [
                'asc' => ['xxx' => SORT_ASC],
                'desc' => ['xxx' => SORT_DESC],
                'default' => SORT_ASC,
            ],
    ],
]);

where xxx is the definition of the columns in relation (might be just type.name but I’m not sure how your db is configured).

2 Likes
6

This does not solve the problem for me either. First of all i cannot access the ActiveDataProvider from the “out of the box” -index-action provided by \yii\rest\ActiveController. But even if i did overwrite the indexAction:

namespace app\controllers;

class ItemController extends \yii\rest\ActiveController
{


    public $modelClass = 'app\models\ItemModel';

    public function additionalSortings() {
        return [
            'type.name' => [
                'asc' => ['type.name' => SORT_ASC],
                'desc' => ['type.name' => SORT_DESC],
                'default' => SORT_ASC,
            ]
        ]; 
    }

    public function actions() {
            $actions = parent::actions();
            $actions['index']['class'] = '\app\components\rest\IndexAction';
            return $actions;
    }

namespace app\components\rest;

class IndexAction extends \yii\rest\IndexAction
{
    /**
     * {@inheritdoc}
     */
    public function run()
    {
       //...
        if(method_exists(Yii::$app->controller, 'additionalSortings')) {
            $dataProvider->sort->attributes = array_merge(
                $dataProvider->sort->attributes,
                Yii::$app->controller->additionalSortings(),
            );
        }
        
        return $dataProvider;
    }

… i would still get an error on requesting the enpoint “/api/items?expand=type&sort=type.name” because the expanded relation gets only populated by the yii\rest\Serializer on “afterAction”. Therefor i get an SQL exception stating: “Column not found: 1054 Unknown column ‘type.name’ in ‘order clause’”.
Any other ideas how to solve this?

7

Have you checked prepareDataProvider?