MySql Pivot table in to Yii CActiveDataProvider

I have a table structure as following.


  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;



The data is loaded with this query.


    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;



How can i form this in to a CDbCriteria object and CActiveDataProvider ? I need to load these data in to a cgridview and allow custom columns to be searched with cgridview filters.

currently this is what i have done.


 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;

                //}

            }


    **output of 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] => 

            )



Appreciate any prompt reply.

bump please