Yii 2: multiple relation with same table

Question

I've got a problem with Yii 2 Relation Tables. My work has many relations, but only in this case return me an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'father.name' in 'where clause'

I think the problem is the double relation with the same Table "Agent". See the piece of code in the model:

public function getAgent()
{
    return $this->hasOne(Agent::className(), ['id' => 'id_agent']);
}
public function getFather()
{
    return $this->hasOne(Agent::className(), ['id' => 'id_father']);
}

In my GridView I see the correct values, but when I try to filter with ORDER or with "andWhere", Yii2 returns the error.

Below you can find the piece of code for the searchModel:

$dataProvider->sort->attributes['agentName'] = [ 
        'asc' => ['agent.name' => SORT_ASC],
        'desc' => ['agent.name' => SORT_DESC],
        'default' => SORT_ASC
    ];

$dataProvider->sort->attributes['fatherName'] = [
        //'asc' => ['father.name' => SORT_ASC],
        //'desc' => ['father.name' => SORT_DESC],
        'default' => SORT_ASC
    ];
//.......
$query->andFilterWhere(['like', 'agent.name', $this->agentName]);
$query->andFilterWhere(['like', 'father.name', $this->fatherName]);

The agentName attributes works fine. Any suggestion? Thank you!

-------UPDATE: more code--------- searchModel:

public function search($params)
{
    $agent_aux = new Agent();
    $agent_id= $agent_aux->getAgentIdFromUser();

    if (Yii::$app->user->can('admin')){
        $query = Contract::find();
    }
    else{

        $query = Contract::find()->where(['contract.agent_id' => $agent_id]);            
    }

    $query->joinWith(['agent','seminar']);

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);
    $this->load($params);
    $dataProvider->sort->attributes['seminar_location'] = [
        'asc' => ['seminar.location' => SORT_ASC],
        'desc' => ['seminar.location' => SORT_DESC],
    ];
    $dataProvider->sort->attributes['agentName'] = [ 
        'asc' => ['agent.name' => SORT_ASC],
        'desc' => ['agent.name' => SORT_DESC],
        'default' => SORT_ASC
    ];

    $dataProvider->sort->attributes['fatherName'] = [
        //'asc' => ['father.name' => SORT_ASC],
        //'desc' => ['father.name' => SORT_DESC],
        'default' => SORT_ASC
    ];
    if (!$this->validate()) {
        return $dataProvider;
    }
    $query->andFilterWhere([
        'id' => $this->id,
        'data' => $this->data,
        'id_agent' => $this->id_agent,
        'id_father' => $this->id_father,
        'id_seminar' => $this->id_seminar,
    ]);
    $query->andFilterWhere(['like', 'agent.name', $this->agentName]);
    $query->andFilterWhere(['like', 'father.name', $this->fatherName]);
    return $dataProvider;
}

Answer 1

You need to do following changes in your model. from clause is actually creating an alias. agent and father relation will be picked in seperate join clauses. Use "agent" and "father" alias in your filter criteria with column names.

public function getAgent()
{
    return $this->hasOne(Agent::className(), ['id' => 'id_agent'])->from(['agent' => Agent::tableName()]);
}

public function getFather()
{
    return $this->hasOne(Agent::className(), ['id' => 'id_father'])->from(['father' => Agent::tableName()])
}

Another thing to change is

$query->joinWith(['agent','seminar', 'father']);