Yii CSqlDataProvider with relations

Question

I have an complex query with joins and conditions
and I get data with CSqlDataProvider
But I also need to join relational table records.

Lets say, we have table A (products) and table B (product_modifications)
I need to list products along with their modifications..

I get data from table A, and i need also to get some records from table B
for each record in table A, query should get an array from table B

Basic code:

class Product extends CActiveRecord
{
       //some code
       public function relations()
       {
           return array(
              'modifications' => array(self::HAS_MANY, 'Modification', 'modification_product_id'),
           );
       }
      //some code
}

my query

$sql = Yii::app()->db->createCommand();
...//different joins and conditions
$this->dataProvider = new CSqlDataProvider($sql->text, array(
    'keyField' => 'product_id',         
    'pagination' => array('pageSize' => 20),            
));

How can i join records from table B (product_modifications)? In CActiveDataProvider its like:

$this->dataProvider = new CActiveDataProvider ('products', array(
    'pagination' => array('pageSize' => 20), 
    'criteria' => array(
        'with' => array(
            'modifications' => array('condition' => 'some condition',),
        ),
    ),  
));

But i dont know how to do this with CSqlDataProvider

UPD: solved by converting query to corresponding CActiveDataProvider query

Answer 1

You can't use AR-relation with sql commands, because it is absolutely different tools for work with Db. In ActiveRecord you are using relation, in Sql commands you are using sql-joins. That's mean you should add you condition there with join:

$sql = Yii::app()->db->createCommand();
...//different joins and conditions + your condition