Yii2 gridView Sort last record from a hasMany relation

Yii 2.0 General Discussions
1

I’m trying use a sort (in header of gridView) by 'id_state’last record from a hasMany relation, and the sort not apply correctly.

tables:

formation

id, name

formation_registration

id, id_formation, id_user

formation_registration_steps

id, id_formation_registration, id_state

Im trying to get that last state row with a relation in yii2 as follows:

formationRegistration model:

public function getFormationRegistrationSteps()
    {
        return $this->hasMany(FormationRegistrationSteps::className(), ['id_formation_registration' => 'id']);
    }


    public function getFormationRegistrationStepsLast()
    {
        return $this->hasOne(FormationRegistrationSteps::className(), ['id_formation_registration' => 'id'])
            ->orderBy(['id' => SORT_DESC]);
    }

formationRegistrationSearch model:

public function search($params)
    {
        $query = FormationRegistration::find();

        // add conditions that should always apply here
        $query->joinWith('formationRegistrationStepsLast lastStep');

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        $dataProvider->setSort([
            'attributes' => [
                'lastStep.id_state' => [
                    'asc' => ['lastStep.id_state' => SORT_ASC],
                    'desc' => ['lastStep.id_state' => SORT_DESC],
                ],
            ],
        ]);

        //(..)
    }

gridView

(..)
['attribute' => 'lastStep.id_state',
'format' => 'raw',
'value' => function ($model) {
 if(isset($model->formationRegistrationSteps->id_sate))
   return $model->formationRegistrationSteps->id_sate;
 }
],
(..)
2

What do you mean by “sort not apply correctly”?

3

@samdark

in gridview column lastStep.id_state when i sort (ex: ASC)
what I wanted:
lastStep.id_state
1
1
1
1
3
3
4
5
5

(example) what happens:
lastStep.id_state
1
1
3
5
4
1
5
1
3

I don’t understand what type of ordering he’s doing.
But it’s definitely not ASC.

4

@samdark
Any idea?
may be a problem in the query below, because here it applies SORT 'id' => SORT_DESC

 return $this->hasOne(FormationRegistrationSteps::className(), ['id_formation_registration' => 'id'])
            ->orderBy(['id' => SORT_DESC]);

and when I will sort in the gridview
is another SORT done?

5

Can you try with orderBy removed?

6

@samdark yes! without the orderBy it orders the results correctly in the gridView (SORT_ASC and SORT_DESC).

but this result is not the last, as I get with orderBy and hasOne (last record from a hasMany relation).
what is your suggestion?

7

https://www.yiiframework.com/wiki/621/filter-sort-by-calculatedrelated-fields-in-gridview-yii-2-0

8

@samdark I’ve read everything, but I didn’t where can this documentation help?

9

That’s compound field, in the wiki page it describes sorting on it.

10

@samdark
I think I already did everything in my first post.
I’m lost. :frowning:

11

@samdark
I did not manage implement this.
Alternatively is it a good idea to create a MySQL View with an id_state column last record from a hasMany relation, like this below?

formation_registration view

id, id_formation, id_user, id_state

12

Using an SQL view is awesome idea. Will help simplifying code a lot. Sorry that I wasn’t able to look into the issue more these days :frowning:

13

no problem, it’s fine! :slight_smile: