How To Get Dataprovider With Self Joining Tables [Solved]

Yii 1.1 General Discussion
1

I can’t seem to figure out how to create a dataprovider object in my case. I have multiple join tables and one of these tables I join to itself multiple time and than I join those values to another table (Yeah I know its rough). Now I got the query working perfect with command = Yii::app()->db->createCommand($sql) but now I am trying to get a dataProvider via CActiveDataProvider instead so I can use this data in CGridview. Can anyone point me in the right direction or just let me know that it is not possible.

This is my query: using createCommand that works fine:


SELECT email AS "Email",reg_event.id,reg_event .cid AS "Campaign ID",date as "Date",classification as "Classification",type as "Type",score as "Score", f11.value AS "First Name", f17.value AS "Address 2", f43.value AS "Albert's Field", f22.value AS "Phone number", f21.value AS "Country", f20.value AS "State", f15.value AS "Title", f16.value AS "Address 1", f19.value AS "Zipcode", f18.value AS "City", f14.value AS "Company", f12.value AS "Last Name", aa1.name AS "Ad 1", aa2.name AS "Ad 2", aa3.name AS "Ad 3" 

FROM reg_event 

LEFT JOIN reg_event_fields f11 ON f11.field_id = 11 AND f11.reg_id = reg_event.id 

LEFT JOIN reg_event_fields f17 ON f17.field_id = 17 AND f17.reg_id = reg_event.id 

LEFT JOIN reg_event_fields f43 ON f43.field_id = 43 AND f43.reg_id = reg_event.id 

LEFT JOIN reg_event_fields f22 ON f22.field_id = 22 AND f22.reg_id = reg_event.id 

LEFT JOIN reg_event_fields f21 ON f21.field_id = 21 AND f21.reg_id = reg_event.id 

LEFT JOIN reg_event_fields f20 ON f20.field_id = 20 AND f20.reg_id = reg_event.id

LEFT JOIN reg_event_fields f15 ON f15.field_id = 15 AND f15.reg_id = reg_event.id

LEFT JOIN reg_event_fields f16 ON f16.field_id = 16 AND f16.reg_id = reg_event.id 

LEFT JOIN reg_event_fields f19 ON f19.field_id = 19 AND f19.reg_id = reg_event.id 

LEFT JOIN reg_event_fields f18 ON f18.field_id = 18 AND f18.reg_id = reg_event.id 

LEFT JOIN reg_event_fields f14 ON f14.field_id = 14 AND f14.reg_id = reg_event.id

LEFT JOIN reg_event_fields f12 ON f12.field_id = 12 AND f12.reg_id = reg_event.id 

LEFT JOIN reg_event_fields a1 ON a1.field_id = 1 AND a1.reg_id = reg_event.id 

LEFT JOIN campaign_field_instance cf1 ON cf1.field_id = a1.field_id AND cf1.field_id = 1 AND cf1.cid = :cid 

LEFT JOIN ads aa1 ON aa1.ad_id = cf1.ad_id 

LEFT JOIN reg_event_fields a2 ON a2.field_id = 2 AND a2.reg_id = reg_event.id 

LEFT JOIN campaign_field_instance cf2 ON cf2.field_id = a2.field_id AND cf2.field_id = 2 AND cf2.cid = :cid 

LEFT JOIN ads aa2 ON aa2.ad_id = cf2.ad_id 

LEFT JOIN reg_event_fields a3 ON a3.field_id = 3 AND a3.reg_id = reg_event.id 

LEFT JOIN campaign_field_instance cf3 ON cf3.field_id = a3.field_id AND cf3.field_id = 3 AND cf3.cid = :cid 

LEFT JOIN ads aa3 ON aa3.ad_id = cf3.ad_id WHERE reg_event.cid = :cid

Here is my attempt on creating the CActiveDataProvider. I get no errors but not sure now to get all the dynamic columns in my CGridview widget:


$criteria=new CDbCriteria;

$sql = 't.email,

        t.id,

        t.cid,

        date,

        classification,

        type,

        score';


// Select all columns (self join columns) from the reg_event_fields table

foreach($field_ids as $key => $field) {

  $sql .= ', f' . $key . '.value AS "' . $field . '"';

  $with[] = array('name' => array('alias' => 'f'  . $key . '.value'));

}


// Select Ad Fields

if($ad_fields) {

  foreach($ad_fields as $key => $ad) {

    $sql .= ', aa' . $key . '.name AS "' . $ad . '"';

  }

}

$criteria->select = $sql;   


$join = '';


// Self join the reg_event_fields

foreach($field_ids as $key => $field) {

  $join .= ' LEFT JOIN reg_event_fields f' . $key . ' ON f' . $key . '.field_id = ' . $key . ' AND f' . $key . '.reg_id = t.id';

}

// Select join Ads

if($ad_fields) {

  foreach($ad_fields as $key => $ad) {

    $join .= ' LEFT JOIN reg_event_fields a' . $key . ' ON a' . $key . '.field_id = ' . $key . ' AND a' . $key . '.reg_id = t.id';

    $join .= ' LEFT JOIN campaign_field_instance cf' . $key . ' ON cf' . $key . '.field_id = a' . $key . '.field_id AND cf' . $key . '.field_id = ' . $key . ' AND cf' . $key . '.cid = :cid';

    $join .= ' LEFT JOIN ads aa' . $key . ' ON aa' . $key . '.ad_id = cf' . $key . '.ad_id';

  }

}  

$criteria->join = $join; 

    

$criteria->condition = 't.cid = :cid';

$criteria->params = array(':cid' => $cid);


$dataProvider = new CActiveDataProvider($this, array(

		'criteria'=>$criteria,

));
2

Why don’t you create a VIEW directly on your DB?

3

How can get the view to go with dataProvide. I need to use CGridView because I need the pager and be able to sort by column. Is it possible to do this with a database view?

4

I was able to get this to work using CSqlDataProvider.




      $count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM reg_event WHERE cid = :cid');

      $count->bindParam(':cid', $cid, PDO::PARAM_STR);

      $count = $count->queryScalar();

      $dataProvider=new CSqlDataProvider($sql, array(

      'totalItemCount'=>$count,

      'sort'=>$sort,

      'params' => $params,

      'pagination'=>array(

        'pageSize'=>$this->pageSize,

      ),

      'keyField' => 'Email',

    ));