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;
}

@InsaneSkulll if I use "agent" as column name (now I use "father", the name of the relation) the search filters the data with the first relation (Agent) :/ — garsim
Do you have a line $query->with(['agent', 'father']) or anything like that? Can you show the full source of the query? — robsch
@robsch i have "$query->joinWith(['agent']);" .... if i use "$query->joinWith(['agent','father']);" yii 2 returns the PDOException: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'agent' — garsim
Can you provide more code and show the table definiton? Here is a similar problem. Not sure if this is the same as your problem. But have a look, please. And wouldn't the use of with(...) be appropriate? And I would assume that you have to use two joins. — robsch

Fido XLNC Fido XLNC · Accepted Answer · 2015-09-29T06:45:07

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']);

Yii 2: multiple relation with same table

2 Answers