我有一个表结构如下:
- 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] =>
- )