Yii2 Gridview Popup Search Form

Hello all. I’m new to Yii2 and have a question. I have a Gridview in my view page and I want to be able to add a button on the page to popup a modal dialog with additional search filters. Some filters are based on fields from the current model and some on relations to other models and a from/to date picker. Can someone help point me in the right direction. An example would be great.

1 Like

So the idea here is we have our GridView with a prepared instance of a yii\data\ActiveDataProvider inserted into the ‘dataProvider’ property of the yii\grid\GridView instance. The first thing that makes sense here is to separate our query out from the action in your controller and into a search model class that extends from the model your querying. So, for example, if we had a ‘Post’ model then we’ll create a ‘PostSearch’ model. Similar to your parent model, you’ll also want to define the rules of your data. If you have defined scenarios in your parent model, you may also want to override the scenarios method in your search model like so:

    public function scenarios()
    {
        return Model::scenarios();
    }

The last thing to add to your search model is a search method; this contains your query and (usually) accepts a $params argument which is often an array of your search input. The query is where you’ll be using joins to bring all your data together in the query object which you can then pass to your $dataProvider’s ‘query’ property. The search method then returns your $dataProvider.

The idea here is to have a default query which you may define in your controller if it’s simple enough, or if you find it’s getting a bit complex then I’d recommend moving it into your search model, then to have a check on if a get request has been made and pass the $params of the request to your search model like so:

  if ($searchModel->load(Yii::$app->request->get())) {
      $dataProvider = $searchModel->search(Yii::$app->request->queryParams);
  }

As for the modal, that’s just a case of having a form within a modal and showing it based on a button; you should take a look through these docs: Modal, yii\bootstrap\Modal | API Documentation | yiisoft/yii2-bootstrap | Yii PHP Framework.

Also here’s a quick example of search model:

class PostSearch extends Post
{
  public $id;
  public $name;
  public $author_id;
  public $created_at;
  public $author_name
  public $start;
  public $end;

  public function rules() 
  {
      return [
        [['id', 'name', 'author_id', 'created_at', 'author_name', 'start_date', 'end_date'], 'safe']
      ];
   }

  public function scenarios()
  {
    return Model::scenarios();
  }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $this->load($params);

        $query = Post::find()->select([
            Post::tableName() . ".id",
            Post::tableName() . ".name",
            Post::tableName() . ".author_id",
            Post::tableName() . ".created_at",
            Author::tableName() . ".name as author_name"
        ])
        ->join(
            'LEFT JOIN',
            Author::tableName(),
            Author::tableName() . '.id = ' . Post::tableName() . '.author_id'
        );

        if($this->start) {
            $query->andWhere(Post::tableName() . '.created_at >= :start',
                [':start' => $this->start]);
        }
        if ($this->end) {
            $query->andWhere(Post::tableName() . '.created_at <= :end',
                [':end' => $this->end]);
        }    
        if ($this->name) {
            $query->andWhere(Post::tableName() . '.name = :name', ['name' => $this->name]);
        }
      
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 10,
            ]
        ]);

        return $dataProvider;
    }  
}

Hopefully I illustrated the idea on how to achieve your issue / pointed you in the right direction. If there’s anything you’re confused about just pop me a message :slight_smile:

1 Like

Thank you for that Cuji12. I’ve been on holiday and just read this. I’ll give it a try. A couple followup questions.

  1. In this example, would th PostSearch.php model page also live in the models subdirectory?
  2. If I did create a search filter modal popup, how would I refresh the parent page with the new dataProvider?

Hi hnguyen,

To answer your questions:

  1. You could put it under a search folder in your models directory to differentiate them from your ‘normal’ models.
  2. The dataProvider is updated whenever the user uses the filter because you’re making a get request on that page which is getting handled in your action, passed off to your search model which prepares your query and then that query is passed into an ActiveDataProvider which executes the query and displays the data to the client.

Hi Cu,

Thank you for your replies and patience. I’v been tasked with learning PHP and the Yii2 framework, taking over an application that someone else wrote. With that said, I’m tried some things but am just getting frustated. Can I get your feedback? I hope I can I lean on you a bit without outstaying my welcome?

In short, the application displays a page that allows the user to enter a value (index.php and ResultController.php) and then redirects to another page with a gridview on it (serialnumber.php and serialnumberController.php). On the index page, I allow the user to add a “wildcard” character to the value they are searching for and I have my code creating the sql with a “like” in the where clause. This involves multiple models and all works fine. So, where I’m getting hung up is after the user submits their request and the results page (serialnumber.php) displays with the returned data in a gridview. This is where I would like to add an advanced search button to display the modal dialogbox with the search options. This would include text boxes, dropdown lists and date pickers. The textboxes can be freeform (autocomplete would be great) and the dropdown lists would be prepopulated with data from tables that have fk relationships. The one thing that would help in the search modal is if I can reuse the original query and just add to the where clause in the search controller. That would help as I would not need to check for wildcards and not have to recreate the “like” statement.

Database tables look like this:

SerialNumberTable → Table1 → Table2 → RunTable

Dropdown Lists would get data from 2 tables that have fk to the RunTable
linux_version_table → RunTable ← phyton_version_table

So here is my code :

index.php

<?php

/* @var $this yii\web\View */

use yii\bootstrap4\ActiveForm;
use yii\bootstrap4\Html;

$this->title = 'Test Results';
?>
<div class="site-result">
	<div class="body-content">
		<div class="row">
			<div class="col-lg-5">
				<h2>Serial Number</h2>
				<p>Search for test results related to a serial number.</p>
				<?php $form_serial = ActiveForm::begin(); ?>
				<?= $form_serial->field($model_serial, 'number')->textInput()->input('hint', ['placeholder'=>"Enter Serial Number (e.g., S0002E4AF)"])->label(false) ?>
				<div class="form-group">
					<?= Html::submitButton('Search', ['class' => 'btn btn-primary']) ?>
				</div>
				<?php ActiveForm::end(); ?>
			</div>
			<div class="col-lg-2"></div>
			<div class="col-lg-5">
				<h2>Production Order</h2>
				<p>Search for meta data related to a production order.</p>
				<?php $form_order = ActiveForm::begin(); ?>
				<?= $form_order->field($model_order, 'order')->textInput()->input('hint', ['placeholder'=>"Enter Production Order (ex: 12345)"])->label(false) ?>
				<div class="form-group">
					<?= Html::submitButton('Search', ['class' => 'btn btn-primary']) ?>
				</div>
				<?php ActiveForm::end(); ?>
			</div>
		</div>
	</div>
</div>

serialNumber.php (view)

<?php

/*
	When searching, be able to further limit test results by searching (extended search):
		for the value of a test result (in the value table) - dropdown
		for Core Commit
		for Library Commit
		for Linux Version
		for Python Version
		for Duration range
		for Environment Type (normal/debug) - dropdown
*/

use yii\bootstrap4\Html;
use yii\bootstrap4\Progress;
use yii\jui\DatePicker;
use yii\bootstrap4\ActiveForm;

$this->title = 'Test Results';
?>
<div class="site-result">
	<h3>Devices for Serial Number <?php echo $serialnumber ?></h3>
	<div class="body-content">
		<div class="row">
			<div class="col-lg-5">
				<table class="table table-striped">
					<thead>
					<tr class="odd">
						<td width="15">Total</td>
						<td width="15"><?php echo $stats['count']; ?></td>
					</tr>
					</thead>
					<tbody>
					<?php
					foreach($stats['result'] as $item) {
						?>
						<tr class="odd">
							<td width="15"><?php echo $item['name']; ?></td>
							<td width="15"><?php echo $item['raw']." (".$item['percentage']."%)"; ?></td>
						</tr>
						<?
					}
					?>
					</tbody>
				</table>
			</div>
			<div class="col-lg-5">
				<!-- Button to trigger search modal -->
				<button type="button" class="btn btn-primary btn-sm" data-toggle="modal" data-target="#searchModal">
				Additional Search Filters
				</button>

				<!-- Modal -->
				<div class="modal fade" id="searchModal" tabindex="-1" role="dialog" aria-labelledby="searchModalLabel">
					<div class="modal-dialog" role="document">
						<div class="modal-content">
							<div class="modal-header">
								<h5 class="modal-title" id="searchModalLabel">Add Search Filters</h5>
							</div>
							<div class="modal-body">
								<?php $form_advance_search = ActiveForm::begin(); ?>
									<?= $form_advance_search->field($model_run, 'core_commit')->textInput() ?>
									<?= $form_advance_search->field($model_run, 'library_commit')->textInput() ?>

									<!-- Need to add dropdown listbox for the linux version and python version fields -->

									<p>Start Date
										<?= DatePicker::widget([
											'model' => $model,
											'attribute' => 'from_date',
											'language' => 'en',
											'dateFormat' => 'php:m-d-Y',
										]) ?>
									</p>
									<p>To Date
										<?= DatePicker::widget([
											'model' => $model,
											'attribute' => 'to_date',
											'language' => 'en',
											'dateFormat' => 'php:m-d-Y',
										]) ?>
									</p>
								<?php $form_advance_search = ActiveForm::end(); ?>
							</div>
							<div class="modal-footer">
								<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
								<?= Html::submitButton('Search', ['class' => 'btn btn-primary']) ?>
							</div>
						</div>
					</div>
				</div>
			</div>
		</div>
		<div class="row">
			<div class="col-lg-7">
				<h4>Success Rate</h4>
				<?php
				$bars = [];
				if ($stats['result']['passed']) {
					$passed = $stats['result']['passed'];
					array_push($bars, [
						'percent' => $passed['percentage'],
						'label' => $passed['percentage'].' %',
						'options' => ['class' => 'bg-success']
					]);
				}
				if ($stats['result']['failed']) {
					$failed = $stats['result']['failed'];
					array_push($bars, [
						'percent' => $failed['percentage'],
						'label' => $failed['percentage'].' %',
						'options' => ['class' => 'bg-danger']
					]);
				}
				echo Progress::widget(['bars' => $bars]);
				?>
			</div>
		</div>
		<div class="row">
			<?php
			echo yii\grid\GridView::widget([
				'dataProvider' => $provider,
				'filterModel' => $serialNumberSearch,
				'rowOptions' => function($provider, $key, $index, $column) {
					$status = $provider->resultStatus->status;
					if ($status == "passed")
						return ['class' => 'text-success'];
					if ($status == "failed")
						return ['class' => 'text-danger'];
					if ($status == "skipped")
						return ['class' => 'text-warning'];
				},
				'columns' => [
					'device_id',
					[
						'class' => 'yii\grid\DataColumn',
						'label' => 'Active',
						'value' => function($provider) {
							return $provider->device->active ? 'Yes' : 'No';
						},
					],
					[
						'label' => 'Result',
						'value' => function($provider) {
							return $provider->resultStatus->status;
						},
					],
					[
						'label' => 'Production Order',
						'format' => 'raw',
						'value' => function($provider) {
							$id = $provider->device->production_order_id;
							return Html::a($id, ['result/productionorder/'.$id], ['class' => 'profile-link']);
						},
					],
					[
						'label' => 'Bucksheet Number',
						'format' => 'raw',
						'value' => function($provider) {
							$id = $provider->device->buck_sheet_number;
							return Html::a($id, ['result/bucksheet/'.$id], ['class' => 'profile-link']);
						},
					],
					[
						'label' => 'Run',
						'format' => 'raw',
						'value' => function($provider) {
							$id = $provider->run_id;
							return Html::a($id, ['result/run/'.$id], ['class' => 'profile-link']);
						},
					],
					[
						'attribute' => 'creation_date',
						'format' => ['datetime', Yii::$app->params['dateControlDisplay']['datetime']]
					],
					[
						'attribute' => 'alteration_date',
						'format' => ['datetime', Yii::$app->params['dateControlDisplay']['datetime']]
					],
				],
			]);
			?>
		</div>
	</div>
</div>

resultController.php (controller for index page)

<?php

namespace api\modules\rhea\controllers;

use api\modules\rhea\models\BucksheetForm;
use api\modules\rhea\models\Device;
use api\modules\rhea\models\Part;
use api\modules\rhea\models\PartNumberForm;
use api\modules\rhea\models\ProductionCode;
use api\modules\rhea\models\ProductionCodeForm;
use api\modules\rhea\models\ProductionOrderForm;
use api\modules\rhea\models\ResultStatus;
use api\modules\rhea\models\Run;
use api\modules\rhea\models\SerialNumber;
use api\modules\rhea\models\SerialNumberForm;
use api\modules\rhea\models\TestStep;
use Yii;
use yii\data\ActiveDataProvider;
use yii\filters\AccessControl;
use yii\filters\VerbFilter;
use yii\web\BadRequestHttpException;
use yii\web\Controller;
use yii\bootstrap\Modal;
use yii\helpers\URL;

$wildcard_count = 0;

/**
 * Site controller
 */
class ResultController extends Controller
{
	const WILDCARD = "*";

	/**
	 * {@inheritdoc}
	 */
	public function behaviors()
	{
		return [
			'access' => [
				'class' => AccessControl::className(),
				'only' => ['logout', 'signup'],
				'rules' => [
					[
						'actions' => ['signup'],
						'allow' => true,
						'roles' => ['?'],
					],
					[
						'actions' => ['logout'],
						'allow' => true,
						'roles' => ['@'],
					],
				],
			],
			'verbs' => [
				'class' => VerbFilter::className(),
				'actions' => [
					'logout' => ['post'],
				],
			],
		];
	}

	/**
	 * {@inheritdoc}
	 */
	public function actions()
	{
		return [
			'error' => [
				'class' => 'yii\web\ErrorAction',
			],
			'captcha' => [
				'class' => 'yii\captcha\CaptchaAction',
				'fixedVerifyCode' => YII_ENV_TEST ? 'testme' : null,
			],
		];
	}

	/**
	 * Displays homepage.
	 *
	 * @return mixed
	 */
	public function actionIndex()
	{
		$model_serial = new SerialNumberForm();
		$model_code = new ProductionCodeForm();
		$model_part = new PartNumberForm();
		$model_order = new ProductionOrderForm();
		$model_bucksheet = new BucksheetForm();
		$payload = [
			'model_serial' => $model_serial,
			'model_code' => $model_code,
			'model_part' => $model_part,
			'model_order' => $model_order,
			'model_bucksheet' => $model_bucksheet,
		];

		if ($model_serial->load(Yii::$app->request->post()) && $model_serial->validate()) {
			return $this->redirect(['result/serialnumber/'.$model_serial->number]);
		} else if ($model_code->load(Yii::$app->request->post()) && $model_code->validate()) {
			return $this->redirect(['result/productioncode/'.$model_code->code]);
		} else if ($model_part->load(Yii::$app->request->post()) && $model_part->validate()) {
			return $this->redirect(['result/partnumber/'.$model_part->part]);
		} else if ($model_order->load(Yii::$app->request->post()) && $model_order->validate()) {
			return $this->redirect(['result/productionorder/'.$model_order->order]);
		} else if ($model_bucksheet->load(Yii::$app->request->post()) && $model_bucksheet->validate()) {
			return $this->redirect(['result/bucksheet/'.$model_bucksheet->number]);
		} else {
			return $this->render('index', $payload);
		}
	}


	/**
	 * Displays homepage.
	 *
	 * @return mixed
	 */
	public function actionSerialnumber()
	{
		$request = Yii::$app->request;
		if (!$request->queryParams['id']) throw new BadRequestHttpException("No serial number given.");
		$payload = ['serialnumber' => $request->queryParams['id']];

		$deviceRootIds = SerialNumber::find()
			->select('device_root_id')
			->Where($this->createQueryConditions($request->queryParams['id'], 'serial_number', $wildcard_count));

		$devices = Device::find()
			->innerJoinWith('deviceRoot')
			->where(['in', 'device.device_root_id', $deviceRootIds])
			->andWhere(['active' => 1]);

		$filter = ['passed', 'failed'];
		$payload['stats'] = $this->getStatsForDevices($devices, $filter);

		$query = Run::find()
		 	->joinWith('serialNumber')
			->Where($this->createQueryConditions($request->queryParams['id'], 'serial_number', $wildcard_count))
			->andWhere(['device.active' => 1]);

		if (!$query)
			throw new BadRequestHttpException("No device found for this serial number.");

		$provider = new ActiveDataProvider([
			'query' => $query,
			'pagination' => [
				'pageSize' => 20,
			],
		]);
		$payload['provider'] = $provider;

		return $this->render('serialnumber', $payload);
	}

	/**
	 * Displays homepage.
	 *
	 * @return mixed
	 */
	public function actionProductioncode()
	{
		$request = Yii::$app->request;
		if (!$request->queryParams['id']) throw new BadRequestHttpException("No production code given.");
		$payload = ['productioncode' => $request->queryParams['id']];

		$deviceRootIds = ProductionCode::find()
			->select('device_root_id')
			->Where($this->createQueryConditions($request->queryParams['id'], 'production_code', $wildcard_count));

		$devices = Device::find()
			->innerJoinWith('deviceRoot')
			->where(['in', 'device.device_root_id', $deviceRootIds])
			->andWhere(['active' => 1]);

		$filter = ['passed', 'failed'];
		$payload['stats'] = $this->getStatsForDevices($devices, $filter);

		$query = Run::find()
		 	->innerJoinWith('productionCode')
			->Where($this->createQueryConditions($request->queryParams['id'], 'production_code', $wildcard_count))
			->andWhere(['device.active' => 1]);

		if (!$query) throw new BadRequestHttpException("No device found for this production code.");

		$provider = new ActiveDataProvider([
			'query' => $query,
			'pagination' => [
				'pageSize' => 20,
			],
		]);
		$payload['provider'] = $provider;

		return $this->render('productioncode', $payload);
	}

	/**
	 * Displays homepage.
	 *
	 * @return mixed
	 */
	public function actionProductionorder()
	{
		$request = Yii::$app->request;
		if (!$request->queryParams['id']) throw new BadRequestHttpException("No production order given.");
		$payload = ['order' => $request->queryParams['id']];

		$devices = Device::find()
			->Where($this->createQueryConditions($request->queryParams['id'], 'production_order_id', $wildcard_count))
			->andWhere(['active' => 1]);

		$filter = ['passed', 'failed'];
		$payload['stats'] = $this->getStatsForDevices($devices, $filter);

		$runs = Run::find()->joinWith('device')
			->Where($this->createQueryConditions($request->queryParams['id'], 'device.production_order_id', $wildcard_count))
			->andWhere(['device.active' => 1]);

		$provider = new ActiveDataProvider([
			'query' => $runs,
			'pagination' => [
				'pageSize' => 20,
			],
		]);
		$payload['provider'] = $provider;

		return $this->render('productionorder', $payload);
	}


	/**
	 * Displays homepage.
	 *
	 * @return mixed
	 */
	public function actionInspection()
	{
		$request = Yii::$app->request;
		$model_code = new ProductionCodeForm();

		if ($model_code->load($request->post()) && $model_code->validate())
			return $this->redirect($model_code->code);

		$payload = [
			'code' => $request->queryParams['id'],
			'model_code' => $model_code,
			'devices' => [],
		];

		if (strcmp($request->queryParams['id'], 'P0') == 0)
			$payload['code'] = 'Production Code';

		$query = ProductionCode::findOne([
			'production_code' => $request->queryParams['id']
		])->deviceRoot;
		if (!$query)
			return $this->render('inspection', $payload);

		$devices = [];
		foreach($query->devices as $device) {
			foreach($device->runs as $run) {

				$output = [
					"device_id" => $device->device_id,
					"run_id" => $run->run_id,
					"result" => $run->resultStatus->status,
					"failed" => []
				];
				$provider = new ActiveDataProvider([
					'query' => $run->getFailedTestSteps(),
					'pagination' => [
						'pageSize' => 20,
					],
				]);
				$output["failed"] = $provider;

				array_push($devices, $output);
			}
		}
		$payload['devices'] = $devices;

		return $this->render('inspection', $payload);
	}


	/**
	 * Displays homepage.
	 *
	 * @return mixed
	 */
	public function actionBucksheet()
	{
		$request = Yii::$app->request;
		if (!$request->queryParams['id']) throw new BadRequestHttpException("No bucksheet number given.");
		$payload = ['number' => $request->queryParams['id']];

		$devices = Device::find()
			->Where($this->createQueryConditions($request->queryParams['id'], 'buck_sheet_number', $wildcard_count))
			->andWhere(['active' => 1]);

		$filter = ['passed', 'failed'];
		$payload['stats'] = $this->getStatsForDevices($devices, $filter);

		$runs = Run::find()->joinWith('device')
			->Where($this->createQueryConditions($request->queryParams['id'], 'device.buck_sheet_number', $wildcard_count))
			->andWhere(['device.active' => 1]);

		$provider = new ActiveDataProvider([
			'query' => $runs,
			'pagination' => [
				'pageSize' => 20,
			],
		]);
		$payload['provider'] = $provider;

		return $this->render('bucksheet', $payload);
	}


	/**
	 * Displays homepage.
	 *
	 * @return mixed
	 */
	public function actionPartnumber()
	{
		$request = Yii::$app->request;
		if (!$request->queryParams['id']) throw new BadRequestHttpException("No part number given.");
		$payload = ['part' => $request->queryParams['id']];

		$devices = Device::find()
			->joinWith('part')
			->Where($this->createQueryConditions($request->queryParams['id'], 'part.part_name', $wildcard_count))
			->andWhere(['active' => 1]);

		$filter = ['passed', 'failed'];
		$payload['stats'] = $this->getStatsForDevices($devices, $filter);

		$partIdList = Part::getAllIdByName($request->queryParams['id']);

		$runs = Run::find()
			->joinWith('device')
			->where(['in', 'device.part_id', $partIdList])
			->andWhere(['device.active' => 1]);

		$provider = new ActiveDataProvider([
			'query' => $runs,
			'pagination' => [
				'pageSize' => 20,
			],
		]);
		$payload['provider'] = $provider;

		return $this->render('partnumber', $payload);
	}


	public function actionRun()
	{
		$request = Yii::$app->request;
		if (!$request->queryParams['id']) throw new BadRequestHttpException("No run id given.");
		$payload = ['run' => $request->queryParams['id']];

		$run = Run::findOne([
			'run_id' => $request->queryParams['id']
		]);
		if (!$run)
			throw new BadRequestHttpException("No device found for this run id.");

		$payload['prod_id'] = $run->device->production_order_id;
		$payload['buck_id'] = $run->device->buck_sheet_number;
		$payload['serial'] = $run->device->deviceRoot->serialNumber->serial_number;
		$payload['codes'] = [];
		foreach($run->device->deviceRoot->productionCodes as $code) {
			array_push($payload['codes'], $code->production_code);
		}
		$payload['meta']['Part Number'] = $run->device->part->part_name;
		$payload['meta']['Result'] = $run->resultStatus->status;
		$payload['meta']['Duration'] = round($run->duration, 2)." s";
		$payload['meta']['Waiting'] = round($run->waiting, 2)." s";
		$payload['meta']['Run Type'] = $run->runType->name;
		$payload['meta']['Environment Type'] = $run->envType->name;
		$payload['meta']['Core Commit'] = $run->core_commit;
		$payload['meta']['Libary Commit'] = $run->lib_commit;
		$payload['meta']['Linux Version'] = $run->linuxVersion->version;
		$payload['meta']['Python Version'] = $run->pythonVersion->version;


		$provider = new ActiveDataProvider([
			'query' => $run->getTestSteps(),
			'pagination' => [
				'pageSize' => 20,
			],
		]);
		$payload['provider'] = $provider;

		return $this->render('run', $payload);
	}


	/**
	 * Displays homepage.
	 *
	 * @return mixed
	 */
	public function actionStep()
	{
		$request = Yii::$app->request;
		$payload = ['step' => $request->queryParams['id']];

		$step = TestStep::findOne([
			'test_step_id' => $request->queryParams['id']
		]);
		$payload['run_id'] = $step->run_id;
		$payload['meta']['Result'] = $step->resultStatus->status;
		$payload['meta']['Name'] = $step->testName->name;
		$payload['meta']['Test Case'] = $step->testCase->name;
		$payload['meta']['Duration'] = round($step->duration, 2)." s";
		$payload['meta']['Waiting'] = round($step->waiting, 2)." s";
		$payload['exit_reason'] = $step->exit_reason;

		$provider = new ActiveDataProvider([
			'query' => !empty($step->values) ? $step->getValues() : $step->getV1Values(),
			'pagination' => [
				'pageSize' => 20,
			],
		]);
		$payload['result_visible'] = !empty($step->values);
		$payload['provider'] = $provider;
		return $this->render('step', $payload);
	}

	/**
	 * Given a value to search and the common name for the search value,
	 * create and return the search condition for the where clause of the sql statement
	 * to be executed
	 *
	 */
	private function createQueryConditions($searchValue, $searchtypeName)
	{
		$conditions = [];

		$serial_number = str_replace(ResultController::WILDCARD, "", $searchValue, $wildcard_count);
		// Check to see if there is a wildcard character entered by the user.
		//
		if ($wildcard_count > 0 ) {

			if (str_starts_with($searchValue , ResultController::WILDCARD) && ($wildcard_count <= 1)) {
				$conditions = ['like', $searchtypeName, '%' . $serial_number, false];
			}
			else if (str_ends_with($searchValue , ResultController::WILDCARD) && ($wildcard_count <= 1)){
				$conditions = ['like', $searchtypeName, $serial_number . '%', false];
			}
			else
			{
				$conditions = ['like', $searchtypeName, $serial_number];
			}

		} else {
			$conditions = [$searchtypeName => $serial_number];
		}

		return $conditions;
	}

	public function getStatsForDevices($devices, $filter = null)
	{
		$data = [];
		$count = $devices->count();

		$results = ResultStatus::find()->all();

		$names = [];
		foreach($results as $result)
			if ($filter) {
				if (in_array($result->status, $filter))
					array_push($names, $result->status);
			} else {
				array_push($names, $result->status);
			}

		foreach($names as $status)
			$data[$status] = ['name' => $status, 'raw' => 0, 'percentage' => 0];

		$device_ids = [];
		foreach($devices->all() as $device) {
			array_push($device_ids, $device->device_id."");
		}
		$device_ids = implode(", ", $device_ids);
		$status = Device::getLastRunStatusResult($device_ids);
		foreach($status as $stat)
			if ($data[$stat['status']])
				$data[$stat['status']]['raw']++;

		foreach($names as $status) {
			$tmp = &$data[$status];
			if ($tmp['raw'] > 0)
				$tmp['percentage'] = round(($tmp['raw'] / $count) * 100, 2);
		}

		return ['count' => $count, 'result' => $data];
	}
}

serialNumberController

<?php

namespace api\modules\rhea\controllers;

use api\modules\rhea\models\SerialNumber;
use api\modules\rhea\models\Run;
use api\modules\rhea\models\SerialNumberForm;

use rhea\facility\CodePool;
use rhea\facility\Device;
use yii;
use yii\rest\Controller;
use yii\web\BadRequestHttpException;
use yii\web\NotFoundHttpException;
use yii\web\Response;

class SerialnumberController extends Controller
{
	public function actionIndex()
	{
		$model_serial = new SerialNumberForm();
		$model_run = new Run();
    }

	/**
	 * Rest Description: Show details about a specific serial number; If none found in rhea, fallback to module.
	 * Rest Fields: [].
	 * Rest Filters: [].
	 * Rest Expand: [].
	 */
	public function actionView()
	{
		Yii::$app->response->format = Response::FORMAT_JSON;

		$request = Yii::$app->request;
		$serial_number = str_replace("*", "", $request->queryParams['id']);
		$verbose = $request->get('verbose');
		$details = $request->get('details');
		$all = $request->get('all');

		$serial = SerialNumber::getBySerialnumber($serial_number);
		if ($serial) {
			$serial = $serial->formatData(isset($verbose), isset($all), isset($details));
			$serial['source'] = 'Rhea';    //indicates that the data comes from rhea
		} else {
			$serial = Device::getDeviceIdBySerialnumber($serial_number, $all, $verbose);
		}
		if (!$serial) {
			throw new NotFoundHttpException('No serial number found for '.$serial_number.'.');
		}
		return $serial;
	}

	/**
	 * Rest Description: Checks if the given Serialnumber is valid and exists.
	 * Rest Fields: [].
	 * Rest Filters: [].
	 * Rest Expand: [].
	 */
	public function actionSerialnumberValidAndExist()
	{
		Yii::$app->response->format = Response::FORMAT_JSON;
		$request = Yii::$app->request;

		//$serial_number = $request->queryParams['id'];
		$serial_number = str_replace("*", "", $request->queryParams['id']);
		$poolid = $request->queryParams['pool'];
		$serial = SerialNumber::find()->where([
			'serial_number' => $serial_number,
		])->one();

		if ($serial) {
			if (!CodePool::checkCode($serial_number, $poolid)) {
				throw new BadRequestHttpException('Serialnumber is not valid');
			}
			return ['exist' => true];
		} else {
			return ['exist' => false];
		}
	}

}

serialNumber.php (model)

<?php

namespace api\modules\rhea\models;

class SerialNumber extends RheaRecord
{
	public static function tableName()
	{
		return 'serial_number';
	}

	public function rules()
	{
		return [
			[['device_root_id', 'serial_number'], 'required'],
			[['serial_number'], 'string', 'max' => 190],
			[['serial_number'], 'unique', 'message' => 'Serialnumber {value} does already exist!'],
		];
	}

	public function attributeLabels()
	{
		return [
			'serial_number_id' => 'Serial Number ID',
			'device_root_id' => 'Device Root Link',
			'serial_number' => 'Serial Number',
			'creation_date' => 'Creation date',
		];
	}

	public function formatData($details = false, $allDevices = true, $deviceDetails = true)
	{
		$output = [
			'device_root_id' => $this->device_root_id,
			'serial_number' => $this->serial_number,
		];

		if ($details) {
			$devices = [];
			foreach($this->deviceRoot->devices as $device) {
				if ($allDevices) {
					array_push($devices, $device->formatData($deviceDetails));
				} else if (!$allDevices && $device->active == 1) {
					array_push($devices, $device->formatData($deviceDetails));
				}
			}
			$output['devices'] = $devices;
		}

		return $output;
	}

	public static function getBySerialnumber($serial_number)
	{
		$serial_number_scrubbed = str_replace("*", "", $serial_number);
		return self::find()->where([
			'serial_number' => $serial_number_scrubbed
		])->one();
	}

	public static function getBySerialnumbers($serial_number)
	{
		$serial_number_scrubbed = str_replace("*", "", $serial_number);

		return self::hasMany()->where([
			'serial_number' => $serial_number_scrubbed
		]);
	}

	public function getDeviceRoot()
	{
		return $this->hasOne(DeviceRoot::className(), ['device_root_id' => 'device_root_id']);
	}

	public function getDevice()
	{
		return $this->hasMany(Device::className(), ['device_root_id' => 'device_root_id'])->via('deviceRoot');
	}

	public function getRun()
	{
		return $this->hasMany(Run::className(), ['device_id' => 'device_id'])->via('device');
	}
}

serialNumberForm.php

<?php

namespace api\modules\rhea\models;

use yii\base\Model;

class SerialNumberForm extends Model
{
	public $number;

	public function rules()
	{
		return [
			[['number'], 'required'],
			[['number'], 'string', 'max' => 190],
			[['number'], 'string', 'min' => 3],
		];
	}
}

serialNumberSearch.php

`<?php

namespace api\modules\rhea\models;

class SerialNumberSearch extends SerialNumber
{
    /*
	When searching, be able to further limit test results by searching (extended search):
		for the value of a test result (in the value table) - dropdown
		for Core Commit
		for Library Commit
		for Linux Version
		for Python Version
		for Duration range
		for Environment Type (normal/debug) - dropdown
        for Date Range - date picker control
    */

    public $device_root_id;
    public $serial_number;
    public $core_commit;
    public $library_commit;
    public $linux_version_id;
    public $python_version_id;
    public $duration_range;
    public $env_type_id;
    public $start;
    public $end;


    public function scenarios()
    {
      return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {

        $this->load($params);
		
		/* If possible, reuse original query and add to the where clause instead of recreating */
        $query = SerialNumber::findAll()->select([
                 SerialNumber::tableName() . ".*",
                 Run::tableName() . ".*"
          ])
          ->join(
              'LEFT JOIN',
              DeviceRoot::tableName(),
              DeviceRoot::tableName() . '.device_root_id = ' . SerialNumber::tableName() . '.device_root_id'
          )
          ->join(
            'LEFT JOIN',
            Device::tableName(),
            Device::tableName() . '.device_id = ' . DeviceRoot::tableName() . '.device_id'
          )
          ->join(
            'LEFT JOIN',
            Run::tableName(),
            Run::tableName() . '.device_id = ' . Device::tableName() . '.device_id'
          );

        if ($this->core_commit) {
            $query->andWhere(Run::tableName() . '.core_commit = :core_commit', ['core_commit' => $this->core_commit]);
        }
        if ($this->library_commit) {
            $query->andWhere(Run::tableName() . '.library_commit = :library_commit', ['library_commit' => $this->library_commit]);
        }
        if ($this->linux_version_id) {
            $query->andWhere(Run::tableName() . '.linux_version_id = :linux_version_id', ['linux_version_id' => $this->linux_version_id]);
        }
        if ($this->python_version_id) {
            $query->andWhere(Run::tableName() . '.python_version_id = :python_version_id', ['python_version_id' => $this->python_version_id]);
        }
        if ($this->duration_range) {
            $query->andWhere(Run::tableName() . '.duration >= :duration_range', ['duration_range' => $this->duration_range]);
        }
        if ($this->duration_range) {
            $query->andWhere(Run::tableName() . '.duration <= :duration_range', ['duration_range' => $this->duration_range]);
        }
        if ($this->env_type_id) {
            $query->andWhere(Run::tableName() . '.env_type_id <= :env_type_id', ['env_type_id' => $this->env_type_id]);
        }
        if ($this->start) {
            $query->andWhere(Run::tableName() . '.creation_date >= :start', [':start' => $this->start]);
        }
        if ($this->end) {
            $query->andWhere(Run::tableName() . '.creation_date <= :end', [':end' => $this->end]);
        }

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

        return $dataProvider;
    }
}

Hi hnguyen,

It sounds like you’re most of the way there and you’re successfully conducting the initial query and redirecting the user to your grid view page with the data. My original post would handle what you’re attempting to do with respect to querying in your grid view page (you’ll be making GET requests to the same page, rather than redirecting). Why not just return your query alongside the payload? That way you have access to the existing query and you can just append to it as needed.

So just doing the following, and then implementing my suggestion:

public function actionSerialnumber()
	{
		$request = Yii::$app->request;
		if (!$request->queryParams['id']) throw new BadRequestHttpException("No serial number given.");
		$payload = ['serialnumber' => $request->queryParams['id']];

		$deviceRootIds = SerialNumber::find()
			->select('device_root_id')
			->Where($this->createQueryConditions($request->queryParams['id'], 'serial_number', $wildcard_count));

		$devices = Device::find()
			->innerJoinWith('deviceRoot')
			->where(['in', 'device.device_root_id', $deviceRootIds])
			->andWhere(['active' => 1]);

		$filter = ['passed', 'failed'];
		$payload['stats'] = $this->getStatsForDevices($devices, $filter);

		$query = Run::find()
		 	->joinWith('serialNumber')
			->Where($this->createQueryConditions($request->queryParams['id'], 'serial_number', $wildcard_count))
			->andWhere(['device.active' => 1]);

		if (!$query)
			throw new BadRequestHttpException("No device found for this serial number.");

		$provider = new ActiveDataProvider([
			'query' => $query,
			'pagination' => [
				'pageSize' => 20,
			],
		]);
		$payload['provider'] = $provider;

		return $this->render('serialnumber', ['payload' => $payload, 'query' => $query]);
	}

On a side note, the code base you’re working with looks well structured so you can count yourself lucky there :slight_smile: