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

There's a condition field when you define relations and it's no problem to have multiple relations for the same model with different conditions. Why don't you just make a separate relation with your special condition? For example, I might have a category table and a products table and the category model will have both a products and an activeProducts relation, both of which I can use as required. — thexacre, Apr 29 '14 at 11:25

score 0 · Answer 1 · answered Apr 29 '14 at 11:34

0

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

answered Apr 29 '14 at 11:34

Alex

8,055
7
39
61

Yes, but i need not just join one row. I need to join several rows from table B to one row from table A... thats the problem. I need distinct query. So i think about some mechnizm that will return me array of modifications for each product. And i dont what to do separate query for each product in loop for that. – johnode Apr 29 '14 at 11:47

Yii CSqlDataProvider with relations

1 Answers1