How can I create a dropdown in a Yii2 form with data from multiple MySQL tables?

Question

Yii2 trying to show a dropdown with info from 2 database tables

Hi, I'm trying to create a dropdown in a form in Yii2 that uses the id of a certain db table and getting the name that belongs to that id from another table. Here's what I've tried:

Creating a model for both tables, Creating a $form->field() and linking the 2 tables. I'm trying to get the id's from one table and getting the names from the other table (the id's are linked).

So the table looks like this:

// table 1 Info_Table                    table 2 Product_Table  
id | product_id | product_type           id | product_name
-----------------------------------------------------------   
1    500          Toy                    500| hot wheels 
2    501          Toy                    501| Lego

I have a model for both tables and I linked them like this in the InfoTable model: (I have just the basic models for every table)

    /**
     * @return ActiveQuery
     */
    public function getProductTable()
    {
        return $this->hasMany(ProductTable::class, ['id' => 'product_id']);
    }

This is how I'm trying to show the names in the dropdown:

<?= $form->field($InfoTable, 'product_id')->dropDownList(ArrayHelper::map(InfoTable::find()->all(), InfoTable->ProductTable->name, InfoTable->ProductTable->name)) ?>

But to be honest I don't really know how I should do it.

Answer 1

Before fix your relation code, is relation not hasMany is hasOne, because this Info_Table->Product_Table is one-to-one. Change your code

public function getProductTable()
{
  return $this->hasOne(ProductTable::class, ['id' => 'product_id']);
}

---

So, Get data with joinWith method from database to dropdownlist

$list = InfoTable::find()
  ->joinWith('productTable')
  ->asArray()
  ->all()

And use in view file

$form->field($InfoTable, 'product_id')
  ->dropDownList(ArrayHelper::map($list, function($item) {
    return $item['productTable']['name'] ?? '-';
  }, function($item) {
    return $item['productTable']['name'] ?? '-';
  })