php – MySql将表转入Yii CActiveDataProvider

前端之家收集整理的这篇文章主要介绍了php – MySql将表转入Yii CActiveDataProvider前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个表结构如下:
  1. CREATE TABLE IF NOT EXISTS `CustomValue` (
  2. `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
  3. ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  4.  
  5. CREATE TABLE IF NOT EXISTS `CustomField` (
  6. `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
  7. ) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  8.  
  9.  
  10. CREATE TABLE IF NOT EXISTS `User` (
  11. `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
  12. ) ENGINE=MyISAM AUTO_INCREMENT=4034 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

请检查sql小提琴. http://sqlfiddle.com/#!9/1a579/3

数据随此查询一起加载.

  1. SET @Colvalues = NULL;
  2. SET @sql = NULL;
  3.  
  4. SELECT
  5. GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(f.fieldName = ''',f.fieldName,''',COALESCE(v.fieldValue,f.defaultValue),NULL)) AS ','''','''')
  6. ) INTO @Colvalues
  7. FROM customField AS f
  8. INNER JOIN Customvalue AS v ON f.Id = v.customFieldId;
  9.  
  10.  
  11. SET @sql = CONCAT('SELECT
  12. u.*,v.relatedId,v.CreatedAt,',@Colvalues,'
  13. FROM customField AS f
  14. INNER JOIN Customvalue AS v ON f.Id = v.customFieldId RIGHT JOIN User u on u.id = v.relatedId
  15. GROUP BY v.relatedId,v.CreatedAt;');
  16.  
  17. PREPARE stmt
  18. FROM @sql;
  19.  
  20. EXECUTE stmt;

如何将其形成为CDbCriteria对象和CActiveDataProvider?我需要将这些数据加载到cgridview中,并允许使用cgridview过滤器搜索自定义列.

目前这就是我所做的:

  1. public function searchPeople($customFields)
  2. {
  3. $criteria = new CDbCriteria;
  4. $criteria->together = true;
  5.  
  6. $criteria->compare('address1',$this->address1,true);
  7. $criteria->compare('mobile',$this->mobile,true);
  8. $criteria->compare('t.firstName',$this->firstName,true);
  9. $criteria->compare('t.lastName',$this->lastName,true);
  10.  
  11.  
  12. if (!empty($customFields)) {
  13. $criteria->join .= ' LEFT OUTER JOIN CustomValue cv ON cv.relatedId=t.id';
  14. //foreach ($customFields as $k => $customField) {
  15. //print_r($customField); exit;
  16. //}
  17. }

print_r的输出($customField):

  1. CustomValue Object
  2. (
  3. [fieldStyle] =>
  4. [fieldName] => ALTERNATEEMAIL
  5. [fieldLabel] => Alternate Email
  6. [fieldType] => text
  7. [fieldTag] =>
  8. [fieldIsrequired] => 1
  9. [fieldDefaultValue] =>
  10. [listValues] =>
  11. [_new:CActiveRecord:private] => 1
  12. [_attributes:CActiveRecord:private] => Array
  13. (
  14. [customFieldId] => 14
  15. [fieldValue] =>
  16. )
为什么不从此创建sql视图,然后创建新模型?

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

猜你在找的PHP相关文章