How to make dropdown list from two tables in DB in Yii2?

Question

I have two tables: depart and translate. In "depart" I keep departments with title_id, which is in second table "translate" and keeps title of dep in different languages. Now I need to make a dropdown list of departments, but it shows 'title_id's in that list, but I need to take a dep.name from second table and put it in list ordered by department table. I have this at the moment:

<?= $form->field($model, 'departId')->dropDownList(ArrayHelper::map($depart, 'id', 'title_id'), ['prompt' => 'Choose department',] ); ?>

Answer 1

You have to run first a query to get data for the dropdown, something similar as that, assumed that the field of the translated text in translate is text:

$depart = (new \yii\db\Query())
    ->select('d.id, t.text')
    ->from(['d' => 'depart', 't' => 'translate'])
    ->where('d.title_id = t.id')
    ->orderBy('t.text')
    ->all();

Other possibility to get the same data with ActiveRecord, assumed that the association from the model Depart to Translate is defined in model Depart.

$depart = Depart::find()
    ->join('translate')
    ->select('depart.id, translate.text')
    ->orderBy(translate.text)
    ->asArray()
    ->all();

In model Depart:

/**
  * @return \yii\db\ActiveQuery
  */
public function getTranslate()
{
    return $this->hasOne(Translate::class, ['id' => 'title_id']);
}

Than you can use your form field with text insted of title_id:

<?= $form->field($model, 'departId')->dropDownList(ArrayHelper::map($depart, 'id', 'text'), ['prompt' => 'Choose department',] ); ?>