How to sort a dataprovider on relational data

Yii 2.0 General Discussions
1

I cannot find an answer that seems to work correctly.

I have a HousingUnit model which has a relation of a HousingType model which in turn has a relation of an Occupant model.

I am trying to get the data into a dataprovider and setting the default order by the Occupant last name then the Occupant first name.

When I try the first sorting option below, the error comes back as

Undefined index: housingType.occupant.last_name


$dataProvider = new ActiveDataProvider([

	'query' => HousingUnit::find()->with(['housingType','housingType.occupant'])->where(['unit_id' => (int)$id]),

	'sort'=> ['defaultOrder' => ['housingType.occupant.last_name'=>SORT_ASC, 'housingType.occupant.first_name'=>SORT_ASC]],

	'pagination' => [

		'pageSize' => 30,

	],

]);

When I comment out the ‘sort’ line and add the following after the dataProvider creation, the order is not in the proper order.


$dataProvider->setSort([

	'attributes'=>[

		'housingType.occupant'=>[

			'asc'=>['last_name'=>SORT_ASC, 'first_name'=>SORT_ASC],

			'default'=>SORT_ASC

		],

	]

]);

I cannot find how to set the default order for a dataProvider when the order should be based on relational data.

Can anyone point me in the proper direction?

2 

$dataProvider = new ActiveDataProvider([

        'query' => HousingUnit::find()->with(['housingType','housingType.occupant'])

                   ->where(['unit_id' => (int)$id])

                   ->orderBy(['housingType.occupant.first_name ASC']),

        'pagination' => [

                'pageSize' => 30,

        ],

]);
3

That will not work. I keep getting this error:


SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'order clause'

The SQL being executed was: SELECT * FROM `tbl_housing_unit` WHERE `unit_id`=2 ORDER BY `0` LIMIT 30

when I use this code:


$dataProvider = new ActiveDataProvider([

        'query' => HousingUnit::find()->with(['housingType','housingType.occupant'])

           		->where(['unit_id' => (int)$id])

           		->orderBy(['housingType.occupant.first_name ASC']),

        'pagination' => [

                'pageSize' => 30,

        ],

]);

I am not sure why housingType.occupant.first_name equates to ‘0’.

4

I still cannot find any example on how to order these records by relation values.

Would it be better to load all of the records once then resorting them in another function?

5

->orderBy([‘housingType.occupant.first_name’ => SORT_ASC]),

6

Still receiving the following error:


Error Info: Array (     [0] => 42S22     [1] => 1054     [2] => Unknown column 'housingType.occupant.first_name' in 'order clause' )
7

Apparently you have to order by the database table name and not the relation name.

This finally worked:


$dataProvider = new ActiveDataProvider([

        'query' => HousingUnit::find()->joinWith(['housingType','housingType.occupant'])

                        ->where(['unit_id' => (int)$id])

                        ->orderBy(['tbl_occupant.first_name ASC']),

        'pagination' => [

                'pageSize' => 30,

        ],

]);