Yii Framework 2.0 Relational Database Active Record

Question

I am working with Yii framework 2.0 and have two database tables (A and B). It is a relational database 1:n. A has only one B but B has many of A. My database looks similar to this.

A table:
id = 1, name = yes
id = 2, name = no
id = 3, name = ok
id = 4, name = good

B table:
id = 1, a_id = 1
id = 2, a_id = 1
id = 3, a_id = 2
id = 4, a_id = 2
id = 5, a_id = 3

In my controller I use the following code to retrieve all data of A and B:

$bModel = B::find()->all();

In my view I use the following for-each to echo all the data.

foreach($bModel as $b) {
   echo $b->a->name . ' - ' $b->id. '<br>;
}

The result is:

 yes - 1
 yes - 2
 no  - 3
 no  - 4

What I want is:

I want to merge the a_id and show all the b's id of each merged a_id. I don't want to use the normal way by firstly get all a's id, use for-each to loop each a's id and then query b one by one by a's id. How can I approach the second result as I wanted with Yii framework 2.0?

Caleb Caleb · Accepted Answer · 2014-11-07T02:50:50

You can use the joinWith() method to eagerly load related models. Eager Loading will fetch all B relations with one query, so that you aren't querying the database each time in the loop. First, ensure that you have this relation defined in model A:

/**
 * @return \yii\db\ActiveQuery
 */
public function getBs()
{
    return $this->hasMany(A::className(), ['a_id' => 'id']);
}

Next, query all of the records together by using the joinWith() method:

foreach (A::find()->joinWith('bs')->all() as $a){
    echo $a->name."\n<br />\n";
    foreach ($a->bs as $relatedB){
        echo $relatedB->name.' - '.$relatedB->id."\n<br />\n";
    }
    echo "\n<br />\n";
}

joinWith() defaults to Eager Loading joined models using LEFT JOIN. If you run this query and enable database logging, you should only see one query to fetch the A models and one query to fetch the B models.

More about joining with relations: http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#joining-with-relations

Yii Framework 2.0 Relational Database Active Record

1 Answers