CODEWITHSUNDEEP
yii
ivo
ivo

Reputation: 587

Yii - databases - active record problem

I am new to Yii php framework. I know python framework - django and managed to create application based on MVC pattern together with database. Now I would like to create some other project/application in php framework - I was trying symfony, but now I want to try the Yii. My problem/question is how to write the queries in Yii's active record language. I am reading a lot of documentation and trying some things using Yii's shell (yiic shell config/main.php), but without getting the results I want. I have three tables - A, B and C where the C table contains only foreign keys from tables A and B (their IDs). I want to have Many-to-Many relationship using just third table (table C). I am puzzled by how can get some items/records from table A (for instance the records which contain some name - in SQL it would be something like ... name LIKE xy), then look for their IDs in table C and find all the matching IDs (pairs) and then finally with IDs of B to extract some more information on specific IDs from table B. Thanks a lot for your time spent on reading this and yet more thank for providing me with some useful information.

P.S.: if you find anything wrong regarding my english, please let me know - I would like to get better with my english.

Upvotes: 4

Views: 2933

Answers (1)

thaddeusmt
thaddeusmt

Reputation: 15600

I assume you've read this page in the manual already (if not, do it now!): http://www.yiiframework.com/doc/guide/1.1/en/database.arr

Anyway, you have two models (AModel and BModel) and a table structure like this:

TableA (table for AModel)
-a_id (primary key)
-otherfieldsA

TableB (table for BModel)
-b_id (primary key)
-otherfieldsB

TableC (relation table)
-a_id (foreign key in TableA)
-b_id (foreign key in TableB)

In AModel, add the following MANY_MANY relationship:

public function relations()
{
  return array(
    'BModels' => array(self::MANY_MANY, 'BModel', 'TableC(a_id, b_id)')
  );
}

And in BModel, add the following MANY_MANY relationship:

public function relations()
{
  return array(
    'AModels' => array(self::MANY_MANY, 'AModel', 'TableC(b_id, a_id)')
  );
}

Now, to get all of the BModels related to AModel, you can do this:

$myAmodel = AModel::model()->findbyPk('1'); // give an id of an A model here
foreach($myAmodel->BModels as $aBmodel) {  // get all of the related B's and loop
  echo $aBmodel->otherfieldsB; // access data from the related B model
}

And vica versa:

$myBmodel = BModel::model()->findbyPk('1'); // give an id of an B model here
foreach($myBmodel->AModels as $anAmodel) { // get all of the related A's and loop
  echo $anAmodel->otherfieldsA; // access data from the related A model
}

Note: This does not solve problems of defining and assigning new relations though, just getting data from existing relationships. To create new MANY_MANY relationships you will need some custom code to make the Form selection widgets, and to save and update the relation table (custom SQL in the afterSave() methods of the models, usually).

There are some extensions in the Yii extension repository that provide Behaviors which make this easier though, like the following:

Upvotes: 7

Related Questions