php - yii2 中使用 Active Record 的复杂数据库查询

Question

长话短说我有一个在 RAW SQL 中运行的查询，但我使用查询生成器或事件记录重新创建它时收效甚微。

---

我正在开发基于 yii2 高级应用程序模板的 Web 应用程序。我已经编写了一个数据库查询并使用返回正确记录的 findbysql() 实现了它，但是在将其转换为事件记录时遇到了问题。

我最初想允许用户通过搜索表单(用户和日期)修改(过滤)结果，但后来我意识到在具有事件记录的 gridview 上实现过滤器会更顺畅。

我已经得到了可以工作的简单查询，但是我不确定如何实现具有这么多连接的查询。许多示例使用了子查询，但我的尝试根本没有返回任何记录。我想在我尝试过滤器之前我需要先转录这个查询。

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,
    ]);

}

部分 View

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

如果我需要更具体或包含任何其他信息，请告诉我。

---

提前致谢

Answer 1

我会假设，根据你问的问题here您喜欢您提供了整个查询的评论(没有其他字段，您取出只是为了显示示例代码)

因此，如果您只需要SELECT 语句中指定的字段，您可以大大优化您的查询:

首先，您加入 host_machines 只是为了链接 cameras 和 events，但具有相同的 key host_machines_idhost_machines 在两者上，所以不需要，您可以直接:

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

其次，与ispy.staff的连接，唯一使用的字段是WHERE子句中的idreceptionist，该字段存在于事件，所以我们可以完全放弃它

最后的查询:

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 方面，
你必须在 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');
}

VideoController 和搜索功能本身

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'));
}

和 View 使用:

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

---

编辑:
如果您想保留视频重复项，请在 Video 模型

中声明 events 的两列

public $event_type, $event_timestamp;

保留原来的 GridView 设置，并在 VideoController 内的查询中添加一个 select 和 indexBy :

$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++);
    });

---

更新

与 Video 的直接 staff 关系目前存在一些问题，因为它与它相距不止一张 table 。有一个问题here

但是，您通过将 staff 表链接到 Event 模型来添加它，

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

这将允许您像这样查询:

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

过滤 需要对 Controller 、 View 和 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;
    }
}

Controller :

public function actionIndex() {
    $searchModel = new VideoSearch();
    $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

    return $this->render('test', [
        'searchModel'  => $searchModel,
        'dataProvider' => $dataProvider,
    ]);
}

和 View

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'],
    ],
]);