yii2 中带有 Active Record 的复杂数据库查询
TL;博士我有一个适用于 RAW SQL 的查询，但我用查询构建器或活动记录重新创建它几乎没有成功.
<小时>我正在开发基于 yii2 高级应用程序模板的 Web 应用程序.我已经编写了一个数据库查询并使用 findbysql() 返回正确的记录来实现它，但是在将其转换为活动记录时遇到了麻烦.
我最初希望允许用户通过搜索表单(用户和日期)修改(过滤)结果，但是我后来意识到在具有活动记录的 gridview 上实施过滤器会更顺畅.
>我已经得到了一些简单的查询，但是我不确定如何使用这么多连接来实现一个.许多示例使用了子查询，但我的尝试根本没有返回任何记录.我想在尝试过滤器之前我需要先转录这个查询.
videoController.php
公共函数actionIndex(){$sql = '选择videos.idvideo，videos.filelocation，events.event_type，events.event_timestamp从 (((ispy.videos 视频INNER JOIN ispy.cameras 相机开启(videos.cameras_idcameras = camera.idcameras))内部连接 ispy.host_machines host_machines开启(cameras.host_machines_idhost_machines =host_machines.idhost_machines))INNER JOIN ispy.events 事件开启 (events.host_machines_idhost_machines =host_machines.idhost_machines))INNER JOIN ispy.staff 员工开启(events.staff_idreceptionist = staff.idreceptionist)WHERE (staff.idreceptionist = 182)AND (events.event_type IN (23, 24))AND (events.event_timestamp BETWEEN videos.start_timeAND video.end_time)';$query = Videos::findBySql($sql);$dataProvider = new ActiveDataProvider(['查询' =>$查询，]);返回 $this->render('index', ['数据提供者' =>$数据提供者，]);}尝试失败
公共函数actionIndex(){$query = Videos::find()->innerJoin('cameras', 'videos.cameras_idcameras = camera.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(['查询' =>$查询，]);返回 $this->render('index', ['数据提供者' =>$数据提供者，]);}部分视图
$数据提供者，'列' =>[['类' =>'yiigridSerialColumn'],'idvideo','事件类型'，'事件时间戳'，'文件位置',//['类' =>'yiigridActionColumn'],],]);?>如果我需要更具体或包含任何其他信息，请告诉我.
<小时>先谢谢了
解决方案我将根据您在此处提出的问题来假设
a> 您在评论中喜欢您提供了整个查询(没有其他字段，您取出只是为了显示示例代码)
因此，如果您只需要SELECT 语句中指定的字段，您可以优化您的查询:
首先，您加入 host_machines 只是为了链接 cameras 和 events，但具有相同的密钥 host_machines_idhost_machines 在两者上，所以不需要，你可以直接:
INNER JOIN events 事件开启 (events.host_machines_idhost_machines =camera.host_machines_idhost_machines))其次，与ispy.staff的join，唯一使用的字段是WHERE子句中的idreceptionist，该字段存在于事件，以便我们可以完全删除它
这里的最终查询:
选择videos.idvideo、videos.filelocation、events.event_type、events.event_timestamp从视频视频INNER JOIN 相机 相机ON video.cameras_idcameras = camera.idcamerasINNER JOIN 事件事件ON events.host_machines_idhost_machines =camera.host_machines_idhost_machines哪里(events.staff_idreceptionist = 182)AND (events.event_type IN (23, 24))AND (events.event_timestamp BETWEEN videos.start_time和videos.end_time)应该输出与您问题中的记录相同的记录，没有任何相同的行
由于cameras 和events
现在到 yii 方面，
您必须在视频模型
//这很简单，`videos`.`cameras_idcameras` 链接到//单摄像头(一对一)公共函数 getCamera(){return $this->hasOne(Camera::className(), ['idcameras' => 'cameras_idcameras']);}//使用`cameras` 作为数据透视表链接事件表(一对多)公共函数 getEvents(){返回 $this->hasMany(Event::className(), [//事件主机 =>摄像头主机(来自via call)'host_machines_idhost_machines' =>'host_machines_idhost_machines'])->via('相机');}VideoController 和搜索功能本身
公共函数 actionIndex() {//这将是用于创建 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(['查询' =>$查询，]);返回 $this->render('index', ['数据提供者' =>$数据提供者，]);}yii 会将每个视频视为一个单独的记录(基于 pk)，这意味着所有视频重复移除.您将拥有单个视频，每个视频都有多个事件，因此您将无法使用 'event_type'和 'event_timestamp' 在视图中，但您可以在 Video 模型中声明一些 getter 以显示该信息:
公共函数 getEventTypes(){return implode(', ', ArrayHelper::getColumn($this->events, 'event_type'));}公共函数 getEventTimestamps(){return implode(', ', ArrayHelper::getColumn($this->events, 'event_timestamp'));}和视图使用:
$数据提供者，'列' =>[['类' =>'yiigridSerialColumn'],'idvideo','事件类型','事件时间戳','文件位置',//['类' =>'yiigridActionColumn'],],]);?>编辑:
如果要保留视频重复项，请在 Video 模型
events 中的两列public $event_type, $event_timestamp;保持原来的 GridView 设置，并添加一个 select 和 indexBy 到 VideoController 中的查询:
$q = Video::find()//指定字段->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')//强制 yii 将每一行视为不同的->indexBy(function () {静态 $count;返回($count++)；});更新
staff 与 Video 的直接关系目前有些问题，因为距离它不止一张桌子.有一个关于它的问题这里
但是，您可以通过将 staff 表链接到 Event 模型来添加它，
公共函数 getStaff() {返回 $this->hasOne(Staff::className(), ['idreceptionist' =>'staff_idreceptionist']);}这将允许您像这样查询:
->joinWith(['camera', 'events', 'events.staff'], true, 'INNER JOIN')过滤需要对控制器、视图和SarchModel
进行一些小的更新这是一个最小的实现:
class VideoSearch 扩展视频{公共 $eventType;公共 $eventTimestamp;公共 $ 用户名；公共函数规则(){返回 array_merge(parent::rules(), [[['eventType', 'eventTimestamp', 'username'], 'safe']]);}公共功能搜索($params){//仅添加/调整始终适用于此处的条件:$q = parent::find()->joinWith(['camera', 'events', 'events.staff'], true, 'INNER JOIN')->哪里(['事件类型' =>[23, 24],//'staff_idreceptionist' =>182//我猜这将是我们要过滤的用户名])->andWhere('event_timestamp BETWEEN videos.start_time AND videos.end_time');$dataProvider = new ActiveDataProvider(['query' => $q]);如果 (!$this->validate())返回 $dataProvider;$this->load($params);$q->andFilterWhere(['idvideo' =>$this->idvideo,'events.event_type' =>$this->eventType,'events.event_timestamp' =>$this->eventTimestamp,'员工.用户名' =>$this->用户名，]);返回 $dataProvider;}}控制器:
公共函数 actionIndex() {$searchModel = new VideoSearch();$dataProvider = $searchModel->search(Yii::$app->request->queryParams);返回 $this->render('test', ['搜索模型' =>$搜索模型，'数据提供者' =>$数据提供者，]);}和视图
使用yiigridGridView；使用 yiihelpersArrayHelper;回声 GridView::widget(['数据提供者' =>$数据提供者，'过滤器模型' =>$搜索模型，'列' =>[['类' =>'yiigridSerialColumn'],'idvideo','文件位置',['属性' =>'eventType',//来自 VideoSearch::$eventType(这是您过滤的依据)'价值' =>'eventTypes'//来自我昨天建议的 Video::getEventTypes()//事后看来，这本来可以更好地命名，例如 Video::formatEventTypes 或 smth],['属性' =>'事件时间戳','价值' =>'事件时间戳'],['属性' =>'用户名'，'价值' =>功能($视频){return implode(', ', ArrayHelper::map($video->events, 'ievent', 'staff.username'));}],//['类' =>'yiigridActionColumn'],],]);TL;DR I have a query that works in RAW SQL but i have had little success recreating it with query builder or active record.
I am working on a web application based off of the yii2 advanced application template. I have written a database query and implemented it with findbysql() returning the correct records but am having trouble translating this into active record.
I originally wanted to allow the user to modify (filter) the results by means of a search form(user & date), however i have since realized that implementing filters on the gridview with active records would be smoother.
I have gotten simple queries to work however am unsure of how to implement one with this many joins. Many examples used sub queries but my attempts failed to return any records at all. I figured before I attempt filters i need to transcribe this query first.
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,
]);
}
Failed Attempt
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,
]);
}
Portion of View
<?= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
['class' => 'yiigridSerialColumn'],
'idvideo',
'event_type',
'event_timestamp',
'filelocation',
//['class' => 'yiigridActionColumn'],
],
]); ?>
Please let me know if i need to be more specific or include any additional information.
Thanks ahead
解决方案i will assume, based on the question you asked here you liked in comments that you provided the entire query (no other fields, that you took out just to show sample code)
therefore, if you only need only the fields specified in SELECT statement, you can optimize your query quite a bit:
first off, you're joining with host_machines only to link cameras and events, but have the same key host_machines_idhost_machines on both, so that's not needed, you can directly:
INNER JOIN events events
ON (events.host_machines_idhost_machines =
cameras.host_machines_idhost_machines))
secondly, the join with ispy.staff, the only used field is idreceptionist in WHERE clause, that field exists in events as well so we can drop it completly
the final query here:
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)
should output the same records as the one in your question, without any identitcal rows
some video duplicates will still exists due to one to many relation between cameras and events
now to the yii side of things,
you have to define some relations on the Videos model
// 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');
}
the VideoController and the search function itself
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 will treat each video as a single record (based on pk), that means that all video duplicates are
removed. you will have single videos, each with multiple events so you wont be able to use 'event_type'
and 'event_timestamp' in the view but you can declare some getters inside Video model to show that info:
public function getEventTypes(){
return implode(', ', ArrayHelper::getColumn($this->events, 'event_type'));
}
public function getEventTimestamps(){
return implode(', ', ArrayHelper::getColumn($this->events, 'event_timestamp'));
}
and the view use:
<?= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
['class' => 'yiigridSerialColumn'],
'idvideo',
'eventTypes',
'eventTimestamps',
'filelocation',
//['class' => 'yiigridActionColumn'],
],
]); ?>
edit:
if you want to keep the video duplicates, declare the two columns from events inside Video model
public $event_type, $event_timestamp;
keep the original GridView setup, and add a select and indexBy this to the query inside 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++);
});
update
a direct staff relation to Video is currently somewhat problematic since that is more than one table away from it.
there's an issue about it here
however, you add the staff table by linking it to the Event model,
public function getStaff() {
return $this->hasOne(Staff::className(), ['idreceptionist' => 'staff_idreceptionist']);
}
that will allow you to query like this:
->joinWith(['camera', 'events', 'events.staff'], true, 'INNER JOIN')
Filtering will require some small updates on the controller, view and a SarchModel
here's a minimal implementation:
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;
}
}
controller:
public function actionIndex() {
$searchModel = new VideoSearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);
return $this->render('test', [
'searchModel' => $searchModel,
'dataProvider' => $dataProvider,
]);
}
and the view
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'],
],
]);
相关文章