CODEWITHSUNDEEP
phpyii2query-builder
gipsy
gipsy

Reputation: 85

yii2 leftjoin query builder

I have a relation/hierarchy between User and Staff: a Staff is always an User but an User can also be a Student or an Admin. Now, through a query I want to get just the staff records that has is_disabled field in User equals to false. I tried with the following but gives me the error:

staff hasn't is_disabled field

That is true, that field is only in User table.

User

 * @property int $id
 * @property string $username
 * @property string password

Staff

 * @property int $id
 * @property string $cellphone
 * @property string $phone
 * @property string $link
 * @property User $id0

SearchStaff

public function search($params)
{
    $query = Staff::find()
    ->leftJoin('user', '`user`.`id` = `staff`.`id`')
    ->where(['user.is_disabled' => false);
}

Upvotes: 0

Views: 236

Answers (1)

anfen
anfen

Reputation: 388

The join is wrong, the foreign key should be id0.

public function search($params)
{
    $query = Staff::find()
    ->leftJoin('user', 'user.id = staff.id0')
    ->where(['user.is_disabled' => false);
}

You can also create a relation in Staff model:

public function getUser(){
    return $this->hasOne(User::className(), ['id' => 'id0']);
}

And edit the query:

$query = Staff::find()
  ->joinWith('user')
  ->where(['user.is_disabled' => false)];

Upvotes: 1

Related Questions