如何在 yii CDbCriteria 中转换 mysql 子查询?

2022-01-04 00:00:00 mysql yii2 yii

如何将mysql子查询转换为yii CDbCriteria
表列表:
1) 用户组
2) user_group_data
3) user_group_data_revision

how to convert mysql subquery to yii CDbCriteria
table list:
1) user_group
2) user_group_data
3) user_group_data_revision

select * from (
         select
         ugd.group_id as group_id,
         ug.user_id as user_id,
         ugd.id as group_data_id,
         ugd.group_name as group_name, 
         ugd.group_description as group_description,
         ugd.email as group_admin_email,
         ugd.image as group_image,
         ugd.group_type as group_type,
         ugd.request_type as group_request_type,
         ugdr.revision_version as revision_version,
         ugdr.admin_approve as revision_approve_status,
         ugdr.publish as revision_publish,
         ugdr.created_at as revision_created_at
         from user_group_data_revision ugdr
         left join user_group_data ugd on ugdr.group_data_id = ugd.id
         left join user_group ug on ugdr.group_id = ug.id
         order by `revision_version` desc
        ) rgd group by rgd.group_id

推荐答案

这里有一个小例子(如果我正确理解了结构/关系):

Here a small example(if I correctly understood structure/relations):

//Model UserGroupDataRevision
class UserGroupDataRevision extends CActiveRecord 
{
     public function relations()
     {
        return array(
            'userGroup' => array(self::BELONGS_TO, 'UserGroup', 'group_id'),
            'userGroupData' => array(self::BELONGS_TO, 'UserGroupData', 'group_data_id'),
        );
     }
     //....
}

//model UserGroup
class UserGroup extends CActiveRecord {...}
//model UserGroupData
class UserGroupData extends CActiveRecord {...}

标准:

$criteria = new CDbCriteria();
$criteria->select = 'ugdr.revision_version'; // other fields
$criteria->alias = 'ugdr';
$criteria->with = array(
        'userGroup' => array(
        'alias' => 'ug',
        'together' => true,
        'select' => array('ug.user_id'), // other fields
    ),
    'userGroupData' => array(
        'alias'=> 'ugd',
        'together' => true,
        'select'=>array('ugd.group_id, ugd.group_name'), // other fields
    ),
);
$criteria->group = 'rgd.group_id';
$criteria->order = 'rgd.group_id'; // or something else

数据检查:

$dataProvider = new CActiveDataProvider('UserGroupDataRevision', array(
      'criteria' => $criteria,
   )
);
var_dump($dataProvider->getData());
die();

相关文章