Consultas de bases de datos complejas en yii2 con registro activo

TL; DR Tengo una consulta que funciona en SQL RAW pero he tenido poco éxito recreándolo con el generador de consultas o el registro activo.


Estoy trabajando en una aplicación web basada en la plantilla de aplicación avanzada yii2. Escribí una consulta en la base de datos y la implementé con findbysql () devolviendo los registros correctos, pero tengo problemas para traducir esto en un registro activo.

Originalmente quería permitir al usuario modificar (filtrar) los resultados por medio de un formulario de búsqueda (usuario y fecha), sin embargo, desde entonces me he dado cuenta de que la implementación de filtros en la vista de cuadrícula con registros activos sería más fluida.

He obtenido consultas simples para trabajar, pero no estoy seguro de cómo implementar una con tantas combinaciones. Muchos ejemplos utilizaron subconsultas, pero mis bashs no dieron ningún registro. Pensé que antes de intentar filtros necesito transcribir esta consulta primero.

videoController.php

public function actionIndex() { $sql = 'SELECT videos.idvideo, videos.filelocation, events.event_type, events.event_timestamp FROM (((ispy.videos videos INNER JOIN ispy.cameras cameras ON (videos.cameras_idcameras = cameras.idcameras)) INNER JOIN ispy.host_machines host_machines ON (cameras.host_machines_idhost_machines = host_machines.idhost_machines)) INNER JOIN ispy.events events ON (events.host_machines_idhost_machines = host_machines.idhost_machines)) INNER JOIN ispy.staff staff ON (events.staff_idreceptionist = staff.idreceptionist) WHERE (staff.idreceptionist = 182) AND (events.event_type IN (23, 24)) AND (events.event_timestamp BETWEEN videos.start_time AND videos.end_time)'; $query = Videos::findBySql($sql); $dataProvider = new ActiveDataProvider([ 'query' => $query, ]); return $this->render('index', [ 'dataProvider' => $dataProvider, ]); } 

Intento fallido

 public function actionIndex() { $query = Videos::find() ->innerJoin('cameras', 'videos.cameras_idcameras = cameras.idcameras') ->innerJoin('host_machines', 'cameras.host_machines_idhost_machines = host_machines.idhost_machines') ->innerJoin('events', 'events.host_machines_idhost_machines = host_machines.idhost_machines') ->innerJoin('staff', 'events.staff_idreceptionist = staff.idreceptionist') ->where('staff.idreceptionist = 182') ->andWhere(['events.event_type' => [23,24]]) ->andwhere(['between', 'events.event_timestamp', 'videos.start_time', 'videos.end_time']); $dataProvider = new ActiveDataProvider([ 'query' => $query, ]); return $this->render('index', [ 'dataProvider' => $dataProvider, ]); } 

Porción de vista

  $dataProvider, 'columns' => [ ['class' => 'yii\grid\SerialColumn'], 'idvideo', 'event_type', 'event_timestamp', 'filelocation', //['class' => 'yii\grid\ActionColumn'], ], ]); ?> 

Avíseme si necesito ser más específico o incluir información adicional.


Gracias por delante

Asumiré, basado en la pregunta que hizo aquí, que le gustó en los comentarios que proporcionó la consulta completa (no hay otros campos, que sacó solo para mostrar el código de muestra)

por lo tanto, si solo necesita los campos especificados en la instrucción SELECT , puede optimizar su consulta bastante:

En primer lugar, se está uniendo con host_machines solo para enlazar cameras y events , pero tiene la misma clave host_machines_idhost_machines en ambos, por lo que no es necesario, puede hacerlo directamente:

  INNER JOIN events events ON (events.host_machines_idhost_machines = cameras.host_machines_idhost_machines)) 

en segundo lugar, la unión con ispy.staff , el único campo utilizado es idreceptionist en la cláusula WHERE , ese campo también existe en events para que podamos descartarlo completamente

la consulta final aquí:

 SELECT videos.idvideo, videos.filelocation, events.event_type, events.event_timestamp FROM videos videos INNER JOIN cameras cameras ON videos.cameras_idcameras = cameras.idcameras INNER JOIN events events ON events.host_machines_idhost_machines = cameras.host_machines_idhost_machines WHERE (events.staff_idreceptionist = 182) AND (events.event_type IN (23, 24)) AND (events.event_timestamp BETWEEN videos.start_time AND videos.end_time) 

debería generar los mismos registros que el de su pregunta, sin filas de identificación
algunos videos duplicados todavía existirán debido a una relación de uno a muchos entre cameras y events


ahora al lado yii de las cosas,
tienes que definir algunas relaciones en el modelo de Videos

 // this is pretty straight forward, `videos`.`cameras_idcameras` links to a // single camera (one-to-one) public function getCamera(){ return $this->hasOne(Camera::className(), ['idcameras' => 'cameras_idcameras']); } // link the events table using `cameras` as a pivot table (one-to-many) public function getEvents(){ return $this->hasMany(Event::className(), [ // host machine of event => host machine of camera (from via call) 'host_machines_idhost_machines' => 'host_machines_idhost_machines' ])->via('camera'); } 

el VideoController y la función de búsqueda misma

 public function actionIndex() { // this will be the query used to create the ActiveDataProvider $query =Video::find() ->joinWith(['camera', 'events'], true, 'INNER JOIN') ->where(['event_type' => [23, 24], 'staff_idreceptionist' => 182]) ->andWhere('event_timestamp BETWEEN videos.start_time AND videos.end_time'); $dataProvider = new ActiveDataProvider([ 'query' => $query, ]); return $this->render('index', [ 'dataProvider' => $dataProvider, ]); } 

yii tratará cada video como un registro único (basado en pk), lo que significa que todos los duplicados de video se eliminan. 'event_type' videos únicos, cada uno con múltiples eventos, por lo que no podrás usar 'event_type' y 'event_timestamp' en la vista, pero puedes declarar algunos captadores dentro del modelo de video para mostrar esa información:

 public function getEventTypes(){ return implode(', ', ArrayHelper::getColumn($this->events, 'event_type')); } public function getEventTimestamps(){ return implode(', ', ArrayHelper::getColumn($this->events, 'event_timestamp')); } 

y el uso de vista:

  $dataProvider, 'columns' => [ ['class' => 'yii\grid\SerialColumn'], 'idvideo', 'eventTypes', 'eventTimestamps', 'filelocation', //['class' => 'yii\grid\ActionColumn'], ], ]); ?> 

editar :
si desea mantener el video duplicado, declare las dos columnas de los events dentro del modelo de video

 public $event_type, $event_timestamp; 

mantenga la configuración original de GridView y agregue una select e indexBy esto a la consulta dentro de VideoController :

 $q = Video::find() // spcify fields ->addSelect(['videos.idvideo', 'videos.filelocation', 'events.event_type', 'events.event_timestamp']) ->joinWith(['camera', 'events'], true, 'INNER JOIN') ->where(['event_type' => [23, 24], 'staff_idreceptionist' => 182]) ->andWhere('event_timestamp BETWEEN videos.start_time AND videos.end_time') // force yii to treat each row as distinct ->indexBy(function () { static $count; return ($count++); }); 

actualizar

una relación staff directa con el Video es actualmente un tanto problemática ya que está a más de una tabla de distancia. hay un problema al respecto aquí

sin embargo, agrega la tabla de staff vinculándola al modelo de evento ,

 public function getStaff() { return $this->hasOne(Staff::className(), ['idreceptionist' => 'staff_idreceptionist']); } 

eso te permitirá consultar de esta manera:

 ->joinWith(['camera', 'events', 'events.staff'], true, 'INNER JOIN') 

El filtrado requerirá algunas actualizaciones pequeñas en el controlador, la vista y un SarchModel
aquí hay una implementación mínima:

 class VideoSearch extends Video { public $eventType; public $eventTimestamp; public $username; public function rules() { return array_merge(parent::rules(), [ [['eventType', 'eventTimestamp', 'username'], 'safe'] ]); } public function search($params) { // add/adjust only conditions that ALWAYS apply here: $q = parent::find() ->joinWith(['camera', 'events', 'events.staff'], true, 'INNER JOIN') ->where([ 'event_type' => [23, 24], // 'staff_idreceptionist' => 182 // im guessing this would be the username we want to filter by ]) ->andWhere('event_timestamp BETWEEN videos.start_time AND videos.end_time'); $dataProvider = new ActiveDataProvider(['query' => $q]); if (!$this->validate()) return $dataProvider; $this->load($params); $q->andFilterWhere([ 'idvideo' => $this->idvideo, 'events.event_type' => $this->eventType, 'events.event_timestamp' => $this->eventTimestamp, 'staff.username' => $this->username, ]); return $dataProvider; } } 

controlador:

 public function actionIndex() { $searchModel = new VideoSearch(); $dataProvider = $searchModel->search(Yii::$app->request->queryParams); return $this->render('test', [ 'searchModel' => $searchModel, 'dataProvider' => $dataProvider, ]); } 

y la vista

 use yii\grid\GridView; use yii\helpers\ArrayHelper; echo GridView::widget([ 'dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'columns' => [ ['class' => 'yii\grid\SerialColumn'], 'idvideo', 'filelocation', [ 'attribute' => 'eventType', // from VideoSearch::$eventType (this is the one you filter by) 'value' => 'eventTypes' // from Video::getEventTypes() that i suggested yesterday // in hindsight, this could have been named better, like Video::formatEventTypes or smth ], [ 'attribute' => 'eventTimestamp', 'value' => 'eventTimestamps' ], [ 'attribute' => 'username', 'value' => function($video){ return implode(', ', ArrayHelper::map($video->events, 'idevent', 'staff.username')); } ], //['class' => 'yii\grid\ActionColumn'], ], ]); 

Mi recomendación sería tener 2 consultas. El primero en obtener los identificadores de los videos que se ajustan a su búsqueda, el segundo consulta a quien usa esos identificadores y alimenta su $dataProvider .

 use yii\helpers\ArrayHelper; ... public function actionIndex() { // This is basically the same query you had before $searchResults = Videos::find() // change 'id' for the name of your primary key ->select('id') // we don't really need ActiveRecord instances, better use array ->asArray() ->innerJoin('cameras', 'videos.cameras_idcameras = cameras.idcameras') ->innerJoin('host_machines', 'cameras.host_machines_idhost_machines = host_machines.idhost_machines') ->innerJoin('events', 'events.host_machines_idhost_machines = host_machines.idhost_machines') ->innerJoin('staff', 'events.staff_idreceptionist = staff.idreceptionist') ->where('staff.idreceptionist = 182') ->andWhere(['events.event_type' => [23,24]]) ->andwhere(['between', 'events.event_timestamp', 'videos.start_time', 'videos.end_time']) // query the results ->all(); // this will be the query used to create the ActiveDataProvider $query = Videos::find() // and we use the results of the previous query to filter this one ->where(['id' => ArrayHelper::getColumn($searchResults, 'id')]); $dataProvider = new ActiveDataProvider([ 'query' => $query, ]); return $this->render('index', [ 'dataProvider' => $dataProvider, ]); }