使用 Yii2 ActiveRecord 在 SELECT 中进行子查询 [英] Subquery in SELECT using Yii2 ActiveRecord

问题描述

是否可以在Yii2中将这种SQL转换成ActiveRecord查询:

Is it possible to convert this kind of SQL into ActiveRecord query in Yii2:

SELECT 
*,
(select count(*) from pendaftar where pendaftar.prodi_pilihan_1 =  a.id_prodi_penerima)as jum1,
(select count(*) from pendaftar where pendaftar.prodi_pilihan_2 = a.id_prodi_penerima)as jum2 
FROM prodi_penerima as a

我有两个关系模型，分别是 Pendaftar 和 ProdiPenerima.

I have two relational models that are Pendaftar and ProdiPenerima.

这是Pendaftar模型:

...
* @property ProdiPenerima $prodiPilihan1
* @property ProdiPenerima $prodiPilihan2
...
/**
 * @return \yii\db\ActiveQuery
 */
public function getPekerjaanIdPekerjaan()
{
    return $this->hasOne(Pekerjaan::className(), ['id_pekerjaan' => 'pekerjaan_id_pekerjaan']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getUserPendaftar()
{
    return $this->hasOne(User::className(), ['id' => 'id_user_pendaftar']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getProdiPilihan1()
{
    return $this->hasOne(ProdiPenerima::className(), ['id_prodi_penerima' => 'prodi_pilihan_1']);
}
/**
 * @return \yii\db\ActiveQuery
 */
public function getProdiPilihan2()
{
    return $this->hasOne(ProdiPenerima::className(), ['id_prodi_penerima' => 'prodi_pilihan_2']);
}

这是 ProdiPenerima 模型:

...
* @property Pendaftar[] $pendaftars
* @property Pendaftar[] $pendaftars0
...
/**
 * @return \yii\db\ActiveQuery
 */
public function getPendaftars()
{
    return $this->hasMany(Pendaftar::className(), ['prodi_pilihan_1' => 'id_prodi_penerima']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getPendaftars0()
{
    return $this->hasMany(Pendaftar::className(), ['prodi_pilihan_2' => 'id_prodi_penerima']);
}

prodi_pilihan_1 和 prody_pilihan_2 是 pendaftar 表中的外键，该键是从 ProdiPenerima 表中引用的.

prodi_pilihan_1 and prody_pilihan_2 are foreign keys in pendaftar table, that key was referenced from ProdiPenerima table.

推荐答案

$result = ProdiPenerima::find()
    ->select([
        '*',
        'jum1' => Pendaftar::find()
            ->select(['COUNT(*)'])
            ->where('pendaftar.prodi_pilihan_1 = a.id_prodi_penerima'),
        'jum2' => Pendaftar::find()
            ->select(['COUNT(*)'])
            ->where('pendaftar.prodi_pilihan_2 = a.id_prodi_penerima')
    ])
    ->alias('a')
    ->asArray()
    ->all();

可以通过以下方式访问结果:

Results can be accessed by:

foreach ($result as $row) { 
    echo $row['jum1']; 
}

那是因为 asArray() 被使用，所以查询返回数组数组而不是模型数组.

That because asArray() was used, so query return array of arrays instead of array of models.

如果你需要模型，你应该在你的模型中添加属性来存储子查询的结果:

If you need models, you should add properties into your models to store result of subqueries:

class ProdiPenerima extends ActiveRecord {

    public $jum1;
    public $jum2;

    // ...
}

然后从查询中删除 isArray():

$result = ProdiPenerima::find()
    ->select([
        '*',
        'jum1' => Pendaftar::find()
            ->select(['COUNT(*)'])
            ->where('pendaftar.prodi_pilihan_1 = a.id_prodi_penerima'),
        'jum2' => Pendaftar::find()
            ->select(['COUNT(*)'])
            ->where('pendaftar.prodi_pilihan_2 = a.id_prodi_penerima')
    ])
    ->alias('a')
    // ->asArray()
    ->all();

可以通过以下方式访问结果:

Results can be accessed by:

foreach ($result as $model) { 
    echo $model->jum1; 
}

但请注意，使用 asArray() 会更快，因此除非您需要访问某些模型方法(或依赖于来自 DB 的值的类型转换)，否则我更喜欢数组.

But note that using asArray() will be faster, so unless you need access some model methods (or rely on typecasting of values from DB) I would prefer arrays.

这篇关于使用 Yii2 ActiveRecord 在 SELECT 中进行子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！