I have noticed today that a activedataprovider with relational tables used in a gridview makes a query for every (new) relational data accessed in a gridview. This means if in the gridview there are 50 entries with 3 columns displaying relational data, in worst case, there are 150 single database queries for this relational data.
I thought about to optimize this with "joins" into a single query, but how to combine this with dataprovider and gridview. Should i make a custom query and return a arraydataprovider instead of a activedataprovider?
I mean the specific problem when using gridview with a activedataprovider. When you define relations in your ActiveRecord models and use that in your gridview, for each usage of a relation there is a seperate SQL query.
For example:
class ScandatasumBase extends \yii\db\ActiveRecord
{
...
/**
* @return \yii\db\ActiveQuery
*/
public function getGround()
{
return $this->hasOne(Ground::className(), ['id' => 'ground_id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getProductNumber()
{
return $this->hasOne(Product::className(), ['product_number' => 'product_number']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getStorageLocation()
{
return $this->hasOne(StorageLocation::className(), ['id' => 'storage_location_id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getUser()
{
return $this->hasOne(User::className(), ['id' => 'user_id']);
}
...
}
class Scandatasum extends ScandatasumBase
{
...
public function search()
{
$query = Scandatasum::find();
$query->select('scandatasum.*, storage_location.name');
$query->joinWith(['productNumber', 'storageLocation']);
$dataProvider = new ActiveDataProvider([
'query' => $query,
'pagination' => [
'pageSize' => 20,
],
]);
// adjust the query by adding the filters
$query->andFilterWhere(['id' => $this->id]);
$query->andFilterWhere(['user_id' => $this->user_id]);
$query->andFilterWhere(['did' => $this->did]);
$query->andFilterWhere(['tour_id' => $this->tour_id]);
$query->andFilterWhere(['storage_location_id' => $this->storage_location_id]);
$query->andFilterWhere(['product_number' => $this->product_number]);
$query->andFilterWhere(['amount' => $this->amount]);
$query->andFilterWhere(['ground_id' => $this->ground_id]);
$query->andFilterWhere(['partie' => $this->partie]);
$query->andFilterWhere(['like', 'modified', $this->modified]);
return $dataProvider;
}
...
}
$scandatas = new Scandatasum(['scenario' => 'search']);
echo GridView::widget([
'dataProvider' => $scandatas->search(),
'filterModel' => $scandatas,
'columns'=>array(
'tour_id',
'storageLocation.name',
'partie',
'productNumber.name',
'ground.name',
'amount',
'modified', ...
In the search() method i defined already the selects and joins, but i cannot access it in the gridview (For example the storage_location.name field). Instead i use the defined relation in ScandatasumBase with storageLocation.name and this works. The problem is for every relation-field used in the gridview (in this case storageLocation.name, productNumber.name, ground.name) there is a seperate SQL query (can be seen in the yii debuger console)
Please refer to the definitive guide for Active Record (especially the sub-section of Lazy Loading and Eager Loading)
As it states clearly,
For example, when you queries a main model with 2 relations, then usually you’ll get 3 queries (assuming that you don’t have any junction tables). The 1st one is for the main models, and the other 2 for the related models. And when it’s for an ActiveDataProvider, the number of queries will be 4, because an extra query is needed to count the total number of records. The page size doesn’t matter. Whether your grid shows 10 rows or 50 rows, the number of queries doesn’t change from 4 when you are using the eager loading approach.
Didn’t you forget to include some relation defined in “with” or “joinWith” ? If yes, then it will be lazily loaded and will cost you very much in your grid view. Please examine the actual SQL queries carefully.
In addition, there’s no fundamental difference in Yii’s db query whether it’s executed for an ActiveDataProvider or for an array of ActiveRecords. It’s quite efficient by design. Don’t be too hasty to run to the ArrayDataProvider, which usually costs you much more.
