CODEWITHSUNDEEP
phpmysqlactiverecordyii2
JDStar
JDStar

Reputation: 33

Yii2 with relation + join 2 tables

I'm new - beginner in Yii2 framework and need help. I have tables:

e_rooms:

id | name

e_items:

id|name|id_unit|id_department|price|quantity

e_units:

id|name

e_departments:

id|name

In model Rooms.php

public function relations()
{
    return array(
        'items_in_room' => array(self::HAS_MANY, 'Items', 'id_room'),
    );
}
public function getItems()
{
   return $this->hasMany(Items::className(), ['id_room' => 'id'])
}

In RoomsController.php:

public function actionView($id)
{
    return $this->render('view', [
        'model' => $this->findModel($id),
        'room_items' => $this->findModel($id)->items,
        ]);
}

In view.php:

GridView::widget([
        'dataProvider' => new ArrayDataProvider([
            'allModels' => $room_items,
            'pagination' => [
                'pageSize' => 100,
            ],
            'sort' => [
                'attributes' => ['number_reference', 'name', 'price'],
            ],
        ]),

        //'filterModel' => $searchModel,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
[.......]

This show all Items in this Room - it's OK, but how i can join table e_units and e_departments to get name unit and name department instead od ID's ?

Upvotes: 2

Views: 7320

Answers (2)

JDStar
JDStar

Reputation: 33

Same process not work:(

 table e_items (id, name, id_room, id_previous_room, id_unit,  id_department,....)
 table e_rooms (id, name)
 table e_units (id, name)
 table e_departments (id, name)

In view items/view.php I need like this: Item: 

id => id
name => name
id_room => name_of_room
id_previous_room => name_of_room
id_unit => name_unit
id_department => name_department

I don't know how make like above.... How in ItemsController make relations without SQL query on each id of unit,room, etc.

Upvotes: 0

csminb
csminb

Reputation: 2382

in your Item model define the relations to Unit and and Department

public function getUnit(){
    return $this->hasOne(Unit::className(), ['id' => 'id_unit']);
}
public function getDepartment(){
    return $this->hasOne(Department::className(), ['id' => 'id_department']);
}

you can use them in gridview as columns like so:

GridView::widget([
    /* ... */
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        'id',             // item id
        'name',           // item name
        'department.name' 
        'unit.name'
    ],
    /* ... */

to avoid excesive queries in the view you can load these relations when building the room_items array 

 'room_items' => $this->findModel($id)->getItems()->with(['unit', 'department'])->all()

on a side note, do checkout yii2 guidelines for schema design, (old yii documentation, but the same guides apply)
you should have a good understanding on when and why you should use single/plural naming for tables, models and relations

Upvotes: 2

Related Questions