Ambiguous column names when using ActiveDataProvider with relations

Yii 2.0 General Discussions
1

I have faced an issue with ambiguous column names when using ActiveDataProvider with model relations.

Let’s assume I have the scenario described in Docs:

http://www.yiiframework.com/doc-2.0/guide-output-data-widgets.html#working-with-model-relations

The search() method would contain following:


$query = Post::find();

$dataProvider = new ActiveDataProvider([

    'query' => $query,

]);


$query->joinWith(['author' => function($query) { $query->from(['author' => 'users']); }]);


// grid filtering conditions

$query->andFilterWhere([

    'id' => $this->id,

    'status' => $this->status,

]);


$query->andFilterWhere(['LIKE', 'author.name', $this->getAttribute('author.name')]);

It usually works well, but the problem arises when the tables contain a column with the same name.

Let’s say there is a “status” column in both the Post and User table. In such a case the default grid filtering conditions won’t work because the “status” column of the Post table appears in the WHERE condition without any alias.

I’ve solved the issue by adding the table name to the where conditions like this:


$tableName = static::tableName();


// grid filtering conditions

$query->andFilterWhere([

    $tableName . '.id' => $this->id,

    $tableName . '.status' => $this->status,

]);

Nevertheless I don’t like this solution too much. If the column with the same name is added after the Search Model exists it will break the ActiveDataProvider filtering.

Do you have any ideas how to solve it in a more elegant way?

I thought about extending the ActiveQuery class but it seems to me as not so easy task to do. Maybe I’m just missing something obvious.

2

Hi,

try this




   $query->joinWith(['author', 'user']);
3

Hello, Thank you for your response, yet I’m not sure how exactly you meant your suggestion.

This would create two join queries as the array contains two elements (http://www.yiiframework.com/doc-2.0/yii-db-activequery.html#joinWith()-detail).

In the example I used the "author" is meant to be an alias of the "user" table. I have copied the code from Docs, however the same can be achieved by


$query->joinWith('author AS author');

in the current version of Yii2 (this example is mentioned in the comments).

The generated SQL in this scenario would looks like this:


SELECT `post`.* FROM `post` LEFT JOIN `user` `author` ON `post`.`author_user_id` = `user`.`id` LIMIT 20

If I would like to filter the "status" of the joined "user" table ("author" relation) the WHERE condition would use alias:


SELECT `post`.* FROM `post` LEFT JOIN `user` `author` ON `post`.`author_user_id` = `user`.`id` WHERE `author`.`status`=1 LIMIT 20

This works well.

The problem is when I would like to filter the "status" of the primary "post" table. In such a case the column name is used without an alias by default:


SELECT `post`.* FROM `post` LEFT JOIN `user` `author` ON `post`.`author_user_id` = `user`.`id` WHERE `status`=1 LIMIT 20

This would cause the error since the "status" column is in both tables.

As I have shown in the previous post the table name could be prepended to the condition definition however it could be a little tricky.