How to filter and sort by a relation that either returns a max value or is empty?

I have a two ActiveRecord models Revision and Page. Page has a this relation:




public function getLastRevision() 

{

	return $this->hasOne(Revision::className(), ['record_id' => 'page_id'])->where([

		'date' => Revision::find()->select('max(date)')->groupBy('record_id')

	]);

}



In PageSearch model




public function search($params) 

{

	$query = Page::find()->joinWith('lastRevision');

	$dataProvider = new ActiveDataProvider([

		'query' => $query,

		'sort' => ['defaultOrder' => ['page_id' => SORT_ASC]],

	]);

	$dataProvider->sort->attributes['updated'] = [

		'asc' => ['{{%revision}}.date' => SORT_ASC],

		'desc' => ['{{%revision}}.date' => SORT_DESC],

	];

	if (!($this->load($params) && $this->validate())) {

		return $dataProvider;

	}

	$query->with(['lastRevision' => function ($q) {

		if ($this->updated) {

			$q->andWhere('{{%revision}}.date = '.$this->updated);

		}

	}

	return $dataProvider;

}



In view:




echo  GridView::widget([

	'dataProvider' => $dataProvider,

	'filterModel' => $searchModel,

	'columns' => [

		[

			'attribute' => 'updated',

			'value' => function ($data) {

				return $data->lastRevision->date;

			}

		],

		...

	]

]);



The Problem is that in the database there are pages that have no corresponding revision entry. This is why I changed the hasOne to a hasMany relation:




public function getLastRevision() 

{

	return $this->hasMany(Revision::className(), ['record_id' => 'page_id'])->where([

		'date' => Revision::find()->select('max(date)')->groupBy('record_id')

	]);

}



In Grid view I changed the return value:




'columns' => [

	[

		'attribute' => 'updated',

		'value' => function ($data) {

			return (!empty($data->lastRevision)) ? $data->lastRevision[0]->date: null;

		}

	],

	...

]



But I can’t figure out how to filter and sort for the updated attribute. Any ideas?