yii2中具有活动记录的复杂数据库查询 [英] Complex Database Queries in yii2 with Active Record

问题描述

TL; DR
我有一个可以在RAW SQL中运行的查询，但是用查询生成器或活动记录重新创建它的效果很小。

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.

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

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.

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' => 'yii\grid\SerialColumn'],
        'idvideo',
        'event_type',
        'event_timestamp',
        'filelocation',
        //['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>

请让我知道我是否需要更具体或包括任何其他信息。

Please let me know if i need to be more specific or include any additional information.

谢谢

推荐答案

i会根据您在此处提出的问题假设您喜欢的问题，在您提供了整个查询的评论中 >
（没有其他字段，您只是为了显示示例代码而将其取出）

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)

因此，如果您只需要<$ c $中指定的字段c> SELECT 语句，您可以优化查询：

therefore, if you only need only the fields specified in SELECT statement, you can optimize your query quite a bit:

首先，您要加入 host_machines 仅用于链接摄像机和事件，但具有相同的键 host_machines_idhost_machines 都不需要，您可以直接：

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))

其次，使用 ispy.staff ，唯一使用的字段是 WHERE 子句中的 idreceptionist ，该字段存在于事件，因此我们可以将其完全删除

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

此处的最终查询：

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

现在到yii方面，

您必须定义一些关系在视频模型上

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

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'，但您可以在视频模型中声明一些吸气剂以显示该信息：

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

和视图使用：

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

---

编辑：< br>
如果要保留视频重复项，请在 Video 模型

public $event_type, $event_timestamp;

保留原始的 GridView 设置，并添加在 VideoController 内将 select 和 indexBy 放入查询中：

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

但是，您可以通过将 staff 表链接到 Event 模型

however, you add the staff table by linking it to the Event model,

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

这样可以查询：

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

过滤将需要对控制器，视图和进行一些小的更新SarchModel

这是一个最小的实现：

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

控制器：

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

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

和视图

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

这篇关于yii2中具有活动记录的复杂数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！