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, Sep 29 '15 at 05:50
Do you have a line $query->with(['agent', 'father']) or anything like that? Can you show the full source of the query? — robsch, Sep 29 '15 at 05:50
@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, Sep 29 '15 at 05:54
Can you provide more code and show the table definiton? [Here](https://github.com/yiisoft/yii2/issues/5137) 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, Sep 29 '15 at 06:04

score 16 · Accepted Answer · answered Sep 29 '15 at 06:45

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

score 1 · Answer 2 · answered Sep 29 '15 at 07:03

An alternative to the answer of @FidoXLNC could be to define the alias when you're doing the join:

$query->joinWith([
    'seminar', 
    'agent'  => function ($q) { $q->from(Agent::tableName() . ' agent' ); },
    'father' => function ($q) { $q->from(Agent::tableName() . ' father'); }
]);

But AFAIK you have to specify both relations, not just only one.

Yii 2: multiple relation with same table

2 Answers2