I have two tables an item group table and an item table. I the AR for the group and I can eager load it’s relations, items can also have a parent/child relationship to each other. Is it possible to eager load the child items, ideally I’d like to go to the DB only once for these?
Group::find()->with(['parentRelationName'])...
I know how to eager load the group’s related items. I’m asking if the items children can also be eager loaded
Group
+------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| label | varchar(45) | NO | | NULL | |
| created_on | datetime | NO | | CURRENT_TIMESTAMP | |
| updated_on | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+-------------+------+-----+-------------------+-----------------------------+
Group Item reference table.
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| group_id | int(11) | NO | PRI | NULL | |
| item_id | int(11) | NO | PRI | NULL | |
+----------+---------+------+-----+---------+-------+
Group Item
+------------------+-----------------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| label | varchar(45) | NO | | NULL | |
| parent_id | int(11) | YES | MUL | NULL | |
| created_on | datetime | NO | | CURRENT_TIMESTAMP | |
| last_updated_on | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+-----------------------+------+-----+-------------------+-----------------------------+
There’s a Group Item table that links groups to items, but items are self-referencing to each other.
The groups and items cannot be in the same table because items have other fields on them.
I already eager load the items
$group = Group::find()->with('items')->where(['label' => 'Main'])->one();
However, I would like to eager load the items’ items as well.
Right now I’m doing something like this
<?php foreach($this->params['group']->items as $item): ?>
<div>
<?= $item->label ?>
<?php foreach($item->children as $child_item): ?>
<div>
<?= $child_item->label ?>
</div>
<?php endforeach ?>
</div>
<?php endforeach ?>
This means that it has to go back to the database because it’s lazy loading. Ideally, I’d like to load all of that at once instead of going to the DB multiple times.
This sounds like a many-to-many relation. Yii1.1 had a ->through() query thing. I haven’t gotten that deep into Yii2. You might also look at a modification to the code for retrieving the relation (or make a new method) to pull the child/child table info.
You will need to map the id with the right table attribute but this is the basics of what you need to do
public function getItems() {
return $this->hasMany(Items::className(), ['id' => 'id'])->viaTable('grout_item', ['group_id' => 'id'])->with('parentRelationInYourGroupModel');
}
Working with active record wiki/docs . That link covers everything that you are trying to achieve and gives examples.