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)
Is there some "better" or "correct" way to handle this ambiguous columns problems in yii2.0?
best regards Horizons