Yii2: How to do a simple join query?

Question

I am learning how to do simple queries using the Yii2 framework. I use PostgreSQL.

I am trying to join two tables and get the data from both tables with a where condition.

The tables are called Admins and Persons. The join use field called idadm.

The condition is idadm = 33. This works great but the result has data only from the Admins table and I need data from the other table.

Here is my example:

$query = \app\models\Admins::find()
    ->select('*')
    ->leftJoin('persons', 'persons.idadm = admins.idadm')
    ->where(['admins.idadm' => 33])
    ->with('persons')
    ->all();

I am following the Yii2 official guide: http://www.yiiframework.com/doc-2.0/guide-db-active-record.html

Update: Here I show the updated code that doesn't solve de problem:

Answer 1

You need to write all column name in select().

$query = \app\models\Admins::find()
    ->select('admin.*,persons.*')  // make sure same column name not there in both table
    ->leftJoin('persons', 'persons.idadm = admins.idadm')
    ->where(['admins.idadm' => 33])
    ->with('persons')
    ->all();

And also you need to define person table attributes in Admin model.

Second way is get records as array,so you dont need to define attributes in Admin model.

$query = \app\models\Admins::find()
    ->select('admin.*,persons.*')  // make sure same column name not there in both table
    ->leftJoin('persons', 'persons.idadm = admins.idadm')
    ->where(['admins.idadm' => 33])
    ->with('persons')
    ->asArray()
    ->all();

Answer 2

Ensure that active record has required relations, e.g. something like follows:

class Admins extends \yii\db\ActiveRecord {
    public function table() { 
        return "admins";
    }
    public function getPersons() 
    {
        return $this->hasMany(Person::className(), ['idadm' => 'idadm']);
    }    
}

class Person  extends \yii\db\ActiveRecord { 
    public function table() {
        return "persons";
    }    
} 

Then use joinWith to build query:

$query  = Admins::find()
           ->joinWith('persons')
           ->limit(1);

$result = $query->createCommand()->getSql();
echo $result;

Here is produced query:

SELECT `admins`.* FROM `admins` 
LEFT JOIN `person` ON `admins`.`idadm` = `person`.`idadm` LIMIT 1