use via with many to many relationship in yii framework

Question

I'm struggling a bit with yii. I currently trying to show data with many-to-many relationships.

I have:

table set
name
description

table item 
name
description

table subinventory
name
description

and table setDetail who link them all
set_id
item_id
subinventory_id

I generated a crud for Set and added a Gridview to show all the items present in the set (saved in the setDetail table) the dataprovider is this one

$dataProvider= new ActiveDataProvider(
          [ 'query' => $this->hasMany(SetDetail::className(), ['set_id' => 'id'])
        ]

Its work well but of course it show the ID of the item and subinventory. I can retrieve the data in the gridview but it'll make a request for each one which is not ideal I think.

I wanted to do a viaTable like that:

$dataProvider= new ActiveDataProvider(
              [ 'query' => $this->hasMany(SetDetail::className(), ['set_id' => 'id'])->viaTable('item',['id => 'item_id'])
            ]

But it don't work obviously because item_id is not in set table but in setDetail table.

So my question: Is there a way to properly (I mean using the yii framework) using viaTable with data provided by query ? I'm surely not really clear so don't hesitate to correct me

Here the generated relation inside setDetail model.

  /**
     * @return \yii\db\ActiveQuery
     */
    public function getItem()
    {
        return $this->hasOne(Item::className(), ['id' => 'item_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getReason()
    {
        return $this->hasOne(Reason::className(), ['id' => 'reason_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getReference()
    {
        return $this->hasOne(Reference::className(), ['id' => 'reference_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getSet()
    {
        return $this->hasOne(Set::className(), ['id' => 'set_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getSubinventory()
    {
        return $this->hasOne(Subinventory::className(), ['id' => 'subinventory_id']);
    }

Answer 1

You can try something like this:

$dataProvider= new ActiveDataProvider([
    'query' => Set::find()->with(['setDetails.item', 'setDetails.subinventory'])->where(['id' => $id]);
]);

Here, setDetails is whatever your hasMany relation name in Set model.

For View

$model = Set::find()
   ->with(['setDetails.item', 'setDetails.subinventory'])
   ->where(['id' => $id])
   ->one();

Use foreach on setDetails to display every SetDetail model.

Answer 2

I answer my own question, mainly because I ask before really searching…

I wanted to use via but a leftJoin was what I needed. Plus I tested simply put database.column and its working. So my code is like that.

  $dataProvider= new ActiveDataProvider(
      [ 'query' => $this->hasMany(SetDetail::className(), ['set_id' => 'id'])
    ->leftJoin('item','`item`.`id` = `setDetail`.`item_id`')
    ->leftJoin('subinventory','`subinventory`.`id` = `setDetail`.`subinventory_id`')
    ->select([
      "`setDetail`.*",
      "`item`.name AS item_name",
      "`subinventory`.name AS subinventory_name",
    ])
      ]
    );

and I also need to add the subinventory_name and item_name in SetDetail model :

class SetDetail extends \yii\db\ActiveRecord
{
  public $item_name;
  public $subinventory_name;
…