将MySql透视表转换为Yii的CActiveDataProvider

Question

我有一个如下所示的表结构：

CREATE TABLE IF NOT EXISTS `CustomValue` (
  `id` int(11) NOT NULL,
  `customFieldId` int(11) NOT NULL,
  `relatedId` int(11) NOT NULL,
  `fieldValue` text COLLATE utf8_unicode_ci,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `CustomField` (
  `id` int(11) NOT NULL,
  `customTypeId` int(11) NOT NULL,
  `fieldName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `relatedTable` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `defaultValue` text COLLATE utf8_unicode_ci,
  `sortOrder` int(11) NOT NULL DEFAULT '0',
  `enabled` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `listItemTag` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `required` char(1) COLLATE utf8_unicode_ci DEFAULT '0',
  `onCreate` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `onEdit` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `onView` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `listValues` text COLLATE utf8_unicode_ci,
  `label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `htmlOptions` text COLLATE utf8_unicode_ci
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `User` (
  `id` bigint(20) NOT NULL,
  `address1` text COLLATE utf8_unicode_ci,
  `mobile` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=4034 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

请查看SQL Fiddle。http://sqlfiddle.com/#!9/1a579/3 数据是使用此查询加载的。

SET @Colvalues = NULL;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(f.fieldName = ''',
      f.fieldName, ''', COALESCE(v.fieldValue, f.defaultValue) , NULL)) AS ', '''', f.fieldName , '''')
  ) INTO @Colvalues
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId;


SET @sql = CONCAT('SELECT 
    u.*, v.relatedId, v.CreatedAt, ', @Colvalues , '
FROM customField AS f
INNER JOIN Customvalue AS v ON f.Id = v.customFieldId RIGHT JOIN User u on u.id = v.relatedId
GROUP BY   v.relatedId, v.CreatedAt;');

PREPARE stmt 
FROM @sql;

EXECUTE stmt;

我该如何将这个内容转换为CDbCriteria和CActiveDataProvider对象，并加载到cgridview中，以便能够使用cgridview filters自定义搜索列的数据。

目前，我已经完成了以下步骤：

public function searchPeople($customFields)
    {
        $criteria = new CDbCriteria;
        $criteria->together = true;

        $criteria->compare('address1', $this->address1, true);
        $criteria->compare('mobile', $this->mobile, true);
        $criteria->compare('t.firstName', $this->firstName, true);
        $criteria->compare('t.lastName', $this->lastName, true);


        if (!empty($customFields)) {
            $criteria->join .= ' LEFT OUTER JOIN CustomValue cv ON cv.relatedId=t.id';
            //foreach ($customFields as $k => $customField) {
            //print_r($customField); exit;
            //}
        }

print_r($customField) 的输出结果：

CustomValue Object
(
    [fieldStyle] => 
    [fieldName] => ALTERNATEEMAIL
    [fieldLabel] => Alternate Email
    [fieldType] => text
    [fieldTag] => 
    [fieldIsRequired] => 1
    [fieldDefaultValue] => 
    [listValues] => 
    [_new:CActiveRecord:private] => 1
    [_attributes:CActiveRecord:private] => Array
        (
            [customFieldId] => 14
            [fieldValue] => 
        )

- dev1234

---

可能有助于解释您目前的问题在哪里出现了……或者更具体地说明您在实现它时遇到的问题。 - Stefan Rogin

@clickstefan 这是我的查询。http://sqlfiddle.com/#!9/1a579/3 我需要根据动态列筛选记录。我正在尝试首先将这个格式设置为一个 CDbCriteria。 - dev1234

Answer 1

为什么不创建一个 SQL 视图，然后从中创建一个新模型呢？

此 SQL 不包含动态值，应以更紧凑的形式编写。

- Fabrizio Caldarelli

---

在MYSQL中，为动态查询创建视图是不可能的。 - dev1234