Gridview one call for multiple columns?

Yii 2.0 General Discussions
1

I’m using a gridview to display a table’s data as well as certain related table data.

As such, I’m using multiple

‘value’ => function($model){
$items = ProjectsLegs::find()
->select([“IF(projects_legs.DispatcherId IS NULL, ‘–’, CONCAT(user.FirstName, ’ ', user.LastName)) DispatcherName”])
->leftJoin(‘user’, ‘projects_legs.DispatcherId = user.id’)
->where([‘projects_legs.ProjId’ => $model->ProjId])
->orderBy([‘projects_legs.LegNo’ => SORT_ASC, ‘projects_legs.StartDt’=>SORT_ASC])
->asArray()
->all();
}

So I’m making multiple very similar queries against the database over and over for each row in the gridview.

I was wondering 2 things

Is there a way to run a single query using all the $model->ProjId being displayed on the gridview instead of multiple single queries?
If not, is there a way to run a single query for a row that holds all the columns I needs and push there values to the right columns as their value, instead of individual functions/queries?

2

Yii has very powerful relation mechanism, take a look at this https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#relational-data
Another method if you only want to display related data in gridview is to define views in db and then generate model on top of the view

3

Use the eager loading approach in your search() method.

Something like:

$query = Project::find()->with(['projectLegss', 'projectLegss.dispatcher'])-> ...

This will reduce the number of SQL queries from 1+N to 1+2.
1 stands for the main query, N for the number of records returned by the main query and 2 (in this particular case) for the number of related models.

https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#lazy-eager-loading

GridView column can be like this:

‘value’ => function($model){
    $names = [];
    foreach($model->projectLegss as $pl) {
        if ($pl->DispacherId == null) {
            $names[] = '--';
        } else {
            $names[] = $pl->dispatcher->FirstName . ' ' . $pl->dispacher->LastName;
        }
    }
    return implode(", ", $names);
},

There will be no SQL queries for fetching ProjectLegs and Dispatcher for each row at this point. They are already fetched by with().

4

Thank you for the explicit instructions softark, I am going to try it this morning.

5

Here’s is my modified search model function

public function search($params)
{
	$this->load($params);
	$session = Yii::$app->session;
	$search = Yii::$app->request->getQueryParam('ProjectsSearch');

	$query = Projects::find()
	->select(['projects.ProjId', 'projects.ProjNo', 'projects.StatusId', 'projects.StatusIdJustificationId', 'projects.Invoiced', 'projects.XeroInvoiceId', 'projects.StartDt',
			'projects.ClientId', 'projects.DispatcherId', 'projects.CompTripNo', 'projects.TruckNo']);
	$query->with(['projectsLegs', 'client', 'dispatcher', 'status']);
	// $query->joinWith('status');
	// $query->joinWith('client');
	// $query->joinWith('dispatcher');
	// $query->joinWith('projectsLegs');
	if( ArrayHelper::getValue($search, 'ProjLocked') == 1 ){
		$query->rightJoin('projects_locks', 'projects.ProjId = projects_locks.ProjId');
	}

	$dataProvider = new ActiveDataProvider([
		'query' => $query,
	]);
	
	$pageSize = (isset($session['pageSize']) ? $session['pageSize'] : '100');
	$dataProvider->pagination->pageSize = $pageSize;

	/* SQL/Azure Version 
	********************************************/
	$dataProvider->sort->attributes['ProjNo'] = [
		'asc' => [new Expression('projects.ProjNo ASC')],
		'desc' => [new Expression('projects.ProjNo DESC')],
	];
	$dataProvider->sort->attributes['StartDt'] = [
		'asc' => [new Expression('CASE WHEN projects.StartDt IS NULL THEN 0 ELSE 1 END, projects.StartDt ASC')],
		'desc' => [new Expression('CASE WHEN projects.StartDt IS NOT NULL THEN 0 ELSE 1 END, projects.StartDt DESC')],
	];
	$dataProvider->sort->attributes['StatusId'] = [
		'asc' => [new Expression('CASE WHEN lst_projects_statuses.Status IS NULL THEN 0 ELSE 1 END, lst_projects_statuses.Status ASC')],
		'desc' => [new Expression('CASE WHEN lst_projects_statuses.Status IS NOT NULL THEN 0 ELSE 1 END, lst_projects_statuses.Status DESC')],
	];
	$dataProvider->sort->attributes['ClientId'] = [
		'asc' => [new Expression('CASE WHEN clients.Company IS NULL THEN 0 ELSE 1 END, clients.Company ASC')],
		'desc' => [new Expression('CASE WHEN clients.Company IS NOT NULL THEN 0 ELSE 1 END, clients.Company DESC')],
	];
	$dataProvider->sort->attributes['CompTripNo'] = [
		'asc' => [new Expression('CASE WHEN projects.CompTripNo IS NULL THEN 0 ELSE 1 END, projects.CompTripNo ASC')],
		'desc' => [new Expression('CASE WHEN projects.CompTripNo IS NOT NULL THEN 0 ELSE 1 END, projects.CompTripNo DESC')],
	];
	$dataProvider->sort->attributes['TruckNo'] = [
		'asc' => [new Expression('CASE WHEN projects.TruckNo IS NULL THEN 0 ELSE 1 END, projects.TruckNo ASC')],
		'desc' => [new Expression('CASE WHEN projects.TruckNo IS NOT NULL THEN 0 ELSE 1 END, projects.TruckNo DESC')],
	];
	$dataProvider->sort->attributes['DispatcherId'] = [
		'asc' => [new Expression('CASE WHEN user.FirstName IS NULL THEN 0 ELSE 1 END, user.FirstName ASC')],
		'desc' => [new Expression('CASE WHEN user.FirstName IS NOT NULL THEN 0 ELSE 1 END, user.FirstName DESC')],
	];
	$dataProvider->sort->attributes['ProjLegEmpId'] = [
		'asc' => [new Expression('CASE WHEN projects_legs.EmpId IS NULL THEN 0 ELSE 1 END, projects_legs.EmpId ASC')],
		'desc' => [new Expression('CASE WHEN projects_legs.EmpId IS NOT NULL THEN 0 ELSE 1 END, projects_legs.EmpId DESC')],
	];
	$dataProvider->sort->attributes['ProjLegServTypeId'] = [
		'asc' => [new Expression('CASE WHEN projects_legs.ServTypeId IS NULL THEN 0 ELSE 1 END, projects_legs.ServTypeId ASC')],
		'desc' => [new Expression('CASE WHEN projects_legs.ServTypeId IS NOT NULL THEN 0 ELSE 1 END, projects_legs.ServTypeId DESC')],
	];
	$dataProvider->sort->attributes['ProjLegLocFrom'] = [
		'asc' => [new Expression('CASE WHEN projects_legs.LocFrom IS NULL THEN 0 ELSE 1 END, projects_legs.LocFrom ASC')],
		'desc' => [new Expression('CASE WHEN projects_legs.LocFrom IS NOT NULL THEN 0 ELSE 1 END, projects_legs.LocFrom DESC')],
	];
	$dataProvider->sort->attributes['ProjLegLocOrigin'] = [
		'asc' => [new Expression('CASE WHEN projects_legs.LocOrigin IS NULL THEN 0 ELSE 1 END, projects_legs.LocOrigin ASC')],
		'desc' => [new Expression('CASE WHEN projects_legs.LocOrigin IS NOT NULL THEN 0 ELSE 1 END, projects_legs.LocOrigin DESC')],
	];
	$dataProvider->sort->attributes['ProjLegLocDest'] = [
		'asc' => [new Expression('CASE WHEN projects_legs.LocDest IS NULL THEN 0 ELSE 1 END, projects_legs.LocDest ASC')],
		'desc' => [new Expression('CASE WHEN projects_legs.LocDest IS NOT NULL THEN 0 ELSE 1 END, projects_legs.LocDest DESC')],
	];
	$dataProvider->sort->attributes['ProjLegStartDt'] = [
		'asc' => [new Expression('projects_legs.StartDt ASC')],
		'desc' => [new Expression('projects_legs.StartDt DESC')],
	];
	$dataProvider->sort->attributes['ProjLegEmpInvRcvd'] = [
		'asc' => [new Expression('projects_legs.EmpInvRcvd ASC')],
		'desc' => [new Expression('projects_legs.EmpInvRcvd DESC')],
	];
	$dataProvider->sort->attributes['ProjLegEmpInvRcvdDt'] = [
		'asc' => [new Expression('projects_legs.EmpInvRcvdDt ASC')],
		'desc' => [new Expression('projects_legs.EmpInvRcvdDt DESC')],
	];
	$dataProvider->sort->attributes['ProjLegVendorTotalBilled'] = [
		'asc' => [new Expression('projects_legs.VendorTotalBilled ASC')],
		'desc' => [new Expression('projects_legs.VendorTotalBilled DESC')],
	];

	$dataProvider->sort->attributes['ProjLegDispatcherId'] = [
		'asc' => [new Expression('projects_legs.DispatcherId ASC')],
		'desc' => [new Expression('projects_legs.DispatcherId DESC')],
	];

	$dataProvider->sort->defaultOrder = ['ProjNo' => SORT_DESC];

	if (!$this->validate()) {
		return $dataProvider;
	}
	
	//If no filter applied restrict to the (last 3 months) OR (those not cancelled or Completed)
	$appliedSearchParams = false;
	if(isset($search)){
		foreach ($search as $key => $value) {
			if (strlen($value)>0){
				$appliedSearchParams = true;
				break;
			}
		}
	}
	if ($appliedSearchParams === false) {
		$startDate = date("Y-m-d", strtotime('-3 month'));
		$endDate = date("Y-m-d");
		$query->where(['between', 'projects.StartDt', $startDate, $endDate]);
		$query->orWhere(['not in', 'projects.StatusId', [1, 6]]);
	}else{
		$query->andFilterWhere([
			'projects.Invoiced' => $this->Invoiced,
			'projects.ProjNo' => $this->ProjNo,
			'projects.StatusId' => $this->StatusId,
			'projects.ContactId' => $this->ContactId,
			'projects.DispatcherId' => $this->DispatcherId,
			'projects_legs.EmpId' => $this->ProjLegEmpId,
			'projects_legs.ServTypeId' => $this->ProjLegServTypeId,
			'projects_legs.EmpInvRcvd' => $this->ProjLegEmpInvRcvd,
			'projects_legs.DispatcherId' => $this->ProjLegDispatcherId,
			'projects_legs.VendorTotalBilled' => $this->ProjLegVendorTotalBilled,
		]);

		$query->andFilterWhere(['like', 'clients.Company', $this->ClientId])
			->andFilterWhere(['like', 'projects.CompTripNo', $this->CompTripNo])
			->andFilterWhere(['like', 'projects.TruckNo', $this->TruckNo])
			->andFilterWhere(['like', 'projects_legs.LocFrom', $this->ProjLegLocFrom])
			->andFilterWhere(['like', 'projects_legs.LocDest', $this->ProjLegLocDest]);

		if (isset($this->ProjLegStartDt) && $this->ProjLegStartDt != '') {
			$date1 = trim($this->ProjLegStartDt)." 00:00:00.000000";
			$date2 = trim($this->ProjLegStartDt)." 23:59:59.999999";
			$query->andFilterWhere(['between', 'projects_legs.StartDt', $date1, $date2]);
		}
		if (isset($this->ProjLegEmpInvRcvdDt) && $this->ProjLegEmpInvRcvdDt != '') {
			$date_explode = explode(" to ", $this->ProjLegEmpInvRcvdDt);
			$date1 = trim($date_explode[0])." 00:00:00.000000";
			$date2 = trim($date_explode[1])." 23:59:59.999999";
			if ( $date1 != $date2) {
				$query->andFilterWhere(['=', 'projects_legs.EmpInvRcvd', 1])
					  ->andFilterWhere(['>=', 'projects_legs.EmpInvRcvdDt', $date1])
					  ->andFilterWhere(['<=', 'projects_legs.EmpInvRcvdDt', $date2]);
			}else{
				$query->andFilterWhere(['=', 'projects_legs.EmpInvRcvd', 1])
					  ->andFilterWhere(['=', 'projects_legs.EmpInvRcvdDt', $date1]);
			}
		}
		if (isset($this->StartDt) && $this->StartDt != '') {
			$date_explode = explode(" to ", $this->StartDt);
			$date1 = trim($date_explode[0])." 00:00:00.000000";
			$date2 = trim($date_explode[1])." 23:59:59.999999";
			if ( $date1 != $date2) {
				$query->andFilterWhere(['>=', 'projects.StartDt', $date1])
					  ->andFilterWhere(['<=', 'projects.StartDt', $date2]);
			}else{
				$query->andFilterWhere(['=', 'projects.StartDt', $date1]);
			}
		}
	}
	return $dataProvider;
}

and then applying your value function, that means it runs a separate query for each row of the gridview, in my case 100 rows, so another 100 queries to be executed.

Is there a way, as part of the Search to return those columns and use them directly instead of using a value function in the gridviewl column?

‘value’=>‘client.Company’

since Company is from the clients tables, which is already in use as part of the SearchModel, can it be accessed directly in the gridview column?

6

With the code @softark provided it shouldn’t be doing a query each row. Maybe its this ‘value’=>‘client.Company’. Try adding this company attribute in the with parameters.

7

There’s an important difference among with, joinWith and xxJoin.

  1. with will do the eager loading of the related models, but doesn’t join the related table in the query of the main model.
  2. joinWith joins the related table in the main query, and also do the eager loading of the related model.
  3. xxJoin joins the related table in the main query, but will not do the eager loading.

So, if you want to filter the result of the main query by an attribute of the related table, you have to use joinWith for the related model.

Since you are trying to filter by projects_legs.EmpId and other attributes of projectLegs, you have to use joinWith for it.

And also you have to join the related table when you want to sort by an attribute of it.

Please check the following wiki article:

Relational Query - Lazy Loading and Eager Loading in Yii 2.0

2 Likes