TL;DR我有一个在RAW SQL中工作的查询,但我用查询生成器或活动记录重新创建它几乎没有成功。
我正在开发一个基于yii2高级应用程序模板的web应用程序。我已经编写了一个数据库查询,并用findbysql()实现了它,返回了正确的记录,但在将其转换为活动记录时遇到了问题。
我最初想允许用户通过搜索表单(user&date)修改(过滤)结果,但后来我意识到,在具有活动记录的网格视图上实现过滤器会更顺利。
我已经得到了一些简单的查询,但我不确定如何使用这么多联接来实现一个查询。许多示例使用了子查询,但我的尝试根本没有返回任何记录。我想在尝试过滤器之前,我需要先转录这个查询。
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,
]);
}
失败的尝试
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,
]);
}
视图的一部分
<?= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
['class' => 'yiigridSerialColumn'],
'idvideo',
'event_type',
'event_timestamp',
'filelocation',
//['class' => 'yiigridActionColumn'],
],
]); ?>
请让我知道,如果我需要更具体或包括任何额外的信息。
感谢
我假设,根据您在评论中提出的问题,您提供了整个查询(没有其他字段,只是为了显示示例代码)
因此,如果您只需要SELECT
语句中指定的字段,则可以对查询进行相当程度的优化:
首先,你加入host_machines
只是为了链接cameras
和events
,但两者上都有相同的密钥host_machines_idhost_machines
,所以不需要,你可以直接:
INNER JOIN events events
ON (events.host_machines_idhost_machines =
cameras.host_machines_idhost_machines))
其次,与ispy.staff
的联接,唯一使用的字段是WHERE
子句中的idreceptionist
,该字段也存在于events
中,因此我们可以完全删除它
这里的最后一个查询:
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)
应该输出与问题中的记录相同的记录,没有任何相同的行
由于cameras
和events
之间的一对多关系,一些视频重复仍然存在
现在到了yii方面,
您必须在视频模型上定义一些关系
// 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');
}
视频控制器和搜索功能本身
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将把每个视频视为一个单独的记录(基于pk),这意味着所有的视频副本已删除。您将有单个视频,每个视频都有多个事件,因此您将无法使用'event_type'
和'event_timestamp'
,但您可以在Video模型中声明一些getter来显示该信息:
public function getEventTypes(){
return implode(', ', ArrayHelper::getColumn($this->events, 'event_type'));
}
public function getEventTimestamps(){
return implode(', ', ArrayHelper::getColumn($this->events, 'event_timestamp'));
}
和视图使用:
<?= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
['class' => 'yiigridSerialColumn'],
'idvideo',
'eventTypes',
'eventTimestamps',
'filelocation',
//['class' => 'yiigridActionColumn'],
],
]); ?>
编辑:
如果要保留视频副本,请在视频型号内声明events
中的两列
public $event_type, $event_timestamp;
保留原始的GridView
设置,并将select
和indexBy
添加到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++);
});
更新
与CCD_ 19的直接CCD_。这里有一个问题
但是,您可以通过将staff
表链接到Event模型来添加它
public function getStaff() {
return $this->hasOne(Staff::className(), ['idreceptionist' => 'staff_idreceptionist']);
}
这将允许您这样查询:
->joinWith(['camera', 'events', 'events.staff'], true, 'INNER JOIN')
筛选将需要对控制器、视图和SarchModel
进行一些小的更新
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;
}
}
控制器:
public function actionIndex() {
$searchModel = new VideoSearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);
return $this->render('test', [
'searchModel' => $searchModel,
'dataProvider' => $dataProvider,
]);
}
和视图
use yiigridGridView;
use yiihelpersArrayHelper;
echo GridView::widget([
'dataProvider' => $dataProvider,
'filterModel' => $searchModel,
'columns' => [
['class' => 'yiigridSerialColumn'],
'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' => 'yiigridActionColumn'],
],
]);
我的建议是有两个查询。第一个是获取适合您搜索的视频的ID,第二个是使用这些ID并提供$dataProvider
的查询。
use yiihelpersArrayHelper;
...
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,
]);
}