php – MySql将表转入Yii CActiveDataProvider

我有一个表结构如下：

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小提琴. 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 ','''','''')
  ) 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过滤器搜索自定义列.

目前这就是我所做的：

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

为什么不从此创建sql视图,然后创建新模型？

sql不包含动态值,应该以更紧凑的形式编写.

原文链接：https://www.f2er.com/php/137196.html

php – MySql将表转入Yii CActiveDataProvider

猜你在找的PHP相关文章