column in where clause is ambiguous

Hi all,

I have started to using Yii2 and have some question.

I have created a model with some relations one mentioned below.

class ProjectReport extends \frontend\models\base\ProjectReportBase

{

public static function tableName()

    {

        return 'data_project_report';

    }


public function getProjectStructure()

    {

        // each Report has_one ProjectStructure via ProjectStructure.structure_id -> structure_id

        return $this->hasOne(ProjectStructure::className(), ['structure_id' => 'structure_id'])

            ->from(['projectStructure' => ProjectStructure::tableName()]);

    }

}

In my ProjectReportSearch model in the search function i have this code

public function search($params)

    {

        $query = ProjectReport::find();


        $query->joinWith(['projectCompany', 'projectStructure', 'project']);

        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);


        $dataProvider->sort->attributes['projectCompany'] = [

            'asc' => ['projectCompany.project_company_short_id' => SORT_ASC],

            'desc' => ['projectCompany.project_company_short_id' => SORT_DESC],

        ];


        if (!($this->load($params) && $this->validate())) {

            return $dataProvider;

        }


        $query->andFilterWhere([

            'project_report_id' => $this->project_report_id,

            'p_id' => $this->p_id,

            'structure_id' => $this->structure_id,

            'project_company_id' => $this->project_company_id,

            'reporting_month' => $this->reporting_month,

            'reporting_year' => $this->reporting_year,

            'reporting_period_month' => $this->reporting_period_month,

            'comments_level' => $this->comments_level,

            'report_state' => $this->report_state,

            'created' => $this->created,

            'created_by' => $this->created_by,

            'updated' => $this->updated,

            'updated_by' => $this->updated_by,

            'approved' => $this->approved,

            'approved_by' => $this->approved_by,

        ]);


        $query->andFilterWhere(['like', 'comments', $this->comments])

            ->andFilterWhere(['like', 'compiler_name', $this->compiler_name])

            ->andFilterWhere(['like', 'compiler_email', $this->compiler_email])

            ->andFilterWhere(['like', 'locked', $this->locked])


            ->andFilterWhere(['like', 'projectCompany.project_company_short_id', $this->projectCompany])

        ;


        return $dataProvider;

    }

Now if i search in my gridview after the structure_id column for some value i get the error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'structure_id' in where clause is ambiguous

The SQL being executed was: SELECT COUNT(*) FROM `data_project_report` LEFT JOIN `data_project_company` `projectCompany` ON `data_project_report`.`project_company_id` = `projectCompany`.`project_company_id` LEFT JOIN `data_project_structure` `projectStructure` ON `data_project_report`.`structure_id` = `projectStructure`.`structure_id` LEFT JOIN `data_project` `project` ON `data_project_report`.`p_id` = `project`.`p_id` WHERE `structure_id`='2438'

Which is correct because the column structure_id is available in my data_project_report table as well as in my data_project_structure table.

I have currently generated my models with the gii code (made some modifications to it) therefore the code is standard.

In the relations i have added some alias so that i can use the alias instead of the tablename. (hopefully this issue is fixed in some time https://github.com/yiisoft/yii2/issues/2377)

However what is the “best way” to fix this ambiguous problems. In yii1 the main table always have the t. alias which isn’t the case any more in yii2.0.

One fix could be to use the tablename (which i don’t like)

 $query->andFilterWhere([

...

'data_project_report.structure_id' => $this->structure_id,

...

Another possible fix would be to add an alias also to the main table e.g. “t.” and use it in the where conditions. But there must be some good case why this was removed from yii2, so i don’t know if this is really a “good” solution.

$query = ProjectReport::find()->from('data_project_report t');

$query->andFilterWhere([

...

't.structure_id' => $this->structure_id,

...

I also found some wiki example where they have added a function which adds the tablename to the condition. (which i also doesn’t like)

http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0/#hh17

Is there some "better" or "correct" way to handle this ambiguous columns problems in yii2.0?

best regards Horizons

no suggestion anyone?

btw happy new year.

An option to specify a table alias could appear in v2.0.2 looking at the PR’s on git.

your right

i haven’t seen the pull request link (btw here https://github.com/yiisoft/yii2/pull/4972)

in the issue https://github.com/yiisoft/yii2/issues/2377 which i also linked above.

So i guess there is currently no better alternative to write the code

$activeQuery->from( ( new $activeQuery->modelClass )->tableName() . ' t' )

or 

$query = ProjectReport::find()->from('data_project_report t');

as written by me.

The correct one would be in my case

$query = ProjectReport::find()->from($this->tableName().' t');

or 

$query = ProjectReport::find()->from(ProjectReport::tableName().' t');

so I was right, that there is no better solution.

thx, hopefully this will be fixed soon, I still doesn’t understand why this was removed.

regards Horizons

If an alias option does get implemented in v2.0.2 it’s scheduled for the 11th so it’s only 4 days.

I’d just do it like you have above leaving a //TODO to revisit it later.

if its only 4 days i think i can wait easily

thx for this information.

edit: wasn’t released with 2.02

$query->andFilterWhere([

    // previous filters

    self::tableName() . '.structure_id' => $this->structure_id,

    // next filters

]);

Hi, im new to Yii 2.

I just experience the same error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous

The SQL being executed was: SELECT COUNT(*) FROM status_log LEFT JOIN user ON status_log.updated_by = user.id WHERE created_at=‘1’

this is my code:

$query->andFilterWhere([

            'id' => $this->id,

            'status_id' => $this->status_id,

            'updated_by' => $this->updated_by,

            'created_at' => $this->created_at,

        ])

        ->andFilterWhere(['like', 'user.username', $this->updatedBy]);

        return $dataProvider;

so i solve it by manually add table name ‘status_log’ before column name ‘created_at’ as below

$query->andFilterWhere([

            'id' => $this->id,

            'status_id' => $this->status_id,

            'updated_by' => $this->updated_by,

            'status_log.created_at' => $this->created_at,

        ])

        ->andFilterWhere(['like', 'user.username', $this->updatedBy]);

        return $dataProvider;

But the discussion above suggest a different solution which is sound like not confirm to be the best way.

So my question is:

1)Is my method reliable and wont cause any other problem?

2)If my method is not how Yii 2 framework is intended to be use, then can someone tell me the proper solution or link to any guide that i can read?

Ultimately you did the same thing because if your StatusLog model table name is ‘status_log’ and you add StatusLog::tableName() . ‘.created_at’ the query code is status_log.created_at.

It’s better to use static method call here because if you will ever change the table name or do some additional actions with it you only need to change the tableName() and not every single piece of code in your application.

Understood, thx a lot. I did feel uncomfortable using hardcode table name, but i just dont know from where to get the table name automatically.