yiisoft
Automaic aliases
What do you think of using automatic aliases on joins, but only as option (need to decide where and how to put this as option), so it will not break backward compatibility, and it will solve ambiguous column problems. Basically it will be like this:
class Employee extends ActiveRecord
{
public function getEmployeeToProject()
{
return $this->hasMany(EmployeeToProject::class, ['employee_id' => 'id'])
->andWhere(['enabled' => 1]);
}
public function getProjects()
{
return $this->hasMany(Project::class, ['id' => 'project_id'])
->via('employeeToProject')
->andWhere(['deal' => 1]);
}
}
class Company extends ActiveRecord
{
public function getProjects()
{
return $this->hasMany(Project::class, ['company_id' => 'id'])->orderBy('[[id]]');
}
}
class Project extends ActiveRecord
{
public function getCompany()
{
return $this->hasOne(Company::class, ['id' => 'company_id']);
}
public function getEmployees()
{
return $this->hasMany(EmployeeToProject::class, ['project_id' => 'id'])->andWhere(['enabled' => 1]);
}
}
and doing:
Employee::find()->joinWith(['company', 'projects'])->andWhere(['id' => [1,2,3]])->orderBy(['id' => SORT_DESC])->all();
Will produce query:
SELECT `employee`.* FROM `employee` LEFT JOIN `company` `Employee<company><company>` ON `employee`.`company_id` = `Employee<company><company>`.`id` LEFT JOIN `employee_to_project` `Employee<employeeToProject><employee_to_project>` ON `employee`.`id` = `Employee<employeeToProject><employee_to_project>`.`employee_id` LEFT JOIN `project` `Employee<projects><project>` ON `Employee<employeeToProject><employee_to_project>`.`project_id` = `Employee<projects><project>`.`id` WHERE (`employee`.`id` IN (1, 2, 3)) AND (`Employee<employeeToProject><employee_to_project>`.`enabled`=1) AND (`Employee<projects><project>`.`deal`=1) ORDER BY `employee`.`id` DESC, `Employee<company><company>`.`id`
Employee::find()->joinWith(['company.projects'])->andWhere(['id' => [1,2,3]])->orderBy(['id' => SORT_DESC])->all();
will produce:
SELECT `employee`.* FROM `employee` LEFT JOIN `company` `Employee<company><company>` ON `employee`.`company_id` = `Employee<company><company>`.`id` LEFT JOIN `project` `Company<projects><project>` ON `Employee<company><company>`.`id` = `Company<projects><project>`.`company_id` WHERE `employee`.`id` IN (1, 2, 3) ORDER BY `employee`.`id` DESC, `Employee<company><company>`.`id`, `Company<projects><project>`.`id`
Also, it will be possible to easily get relation column, prefixed with automatic alias, for using in filters:
$query = Employee::find()->joinWith(['company.projects'])->andWhere(['id' => [1,2,3]])->orderBy(['id' => SORT_DESC]);
$query = $query->andWhere([$query->getRelationColumn('projects', 'status') => 4])->orderBy([$query->getRelationColumn('company', 'visible') => SORT_DESC])->all();
will produce query:
SELECT `employee`.* FROM `employee` LEFT JOIN `company` `Employee<company><company>` ON `employee`.`company_id` = `Employee<company><company>`.`id` LEFT JOIN `project` `Company<projects><project>` ON `Employee<company><company>`.`id` = `Company<projects><project>`.`company_id` WHERE (`employee`.`id` IN (1, 2, 3)) AND (`Company<projects><project>`.`status`=4) ORDER BY `Employee<company><company>`.`visible` DESC, `Employee<company><company>`.`id`, `Company<projects><project>`.`id`
This way you will never need to pay attention to aliasing relations because of ambiguous column problems. You can even use same relation model twice thru different relations, like:
public function getCompany()
{
return $this->hasOne(Company::class, ['id' => 'company_id'])
->orderBy('[[id]]');
}
public function getEnabledCompany()
{
return $this->hasOne(Company::class, ['id' => 'company_id'])
->andWhere(['enabled' => 1])
->orderBy('[[id]]');
}
$query = Employee::find()->joinWith(['company', 'enabledCompany'])->andWhere(['id' => [1,2,3]])->orderBy(['id' => SORT_DESC]);
$query = $query->andWhere([$query->getRelationColumn('company', 'status') => 4, $query->getRelationColumn('enabledCompany', 'status') => 8])->all();
will produce query:
SELECT `employee`.* FROM `employee` LEFT JOIN `company` `Employee<company><company>` ON `employee`.`company_id` = `Employee<company><company>`.`id` LEFT JOIN `company` `Employee<enabledCompany><company>` ON `employee`.`company_id` = `Employee<enabledCompany><company>`.`id` WHERE (`employee`.`id` IN (1, 2, 3)) AND ((`Employee<company><company>`.`status`=4) AND (`Employee<enabledCompany><company>`.`status`=8)) AND (`Employee<enabledCompany><company>`.`enabled`=1) ORDER BY `employee`.`id` DESC, `Employee<company><company>`.`id`, `Employee<enabledCompany><company>`.`id`
Alias is done by: calling_class_name<relation_name><table_of_relation_model> and this way it will be always unique. If you by any chance use your own alias for relation, it will use that name for prefixing columns, like
$query = Employee::find()->joinWith(['company' => function ($q) {
$q->alias('custom_company_relation');
return $q;
}, 'enabledCompany'])->andWhere(['id' => [1,2,3]])->orderBy(['id' => SORT_DESC]);
$query = $query->andWhere([$query->getRelationColumn('company', 'status') => 4, $query->getRelationColumn('enabledCompany', 'status') => 8])->all();
query:
SELECT `employee`.* FROM `employee` LEFT JOIN `company` `custom_company_relation` ON `employee`.`company_id` = `custom_company_relation`.`id` LEFT JOIN `company` `Employee<enabledCompany><company>` ON `employee`.`company_id` = `Employee<enabledCompany><company>`.`id` WHERE (`employee`.`id` IN (1, 2, 3)) AND ((`custom_company_relation`.`status`=4) AND (`Employee<enabledCompany><company>`.`status`=8)) AND (`Employee<enabledCompany><company>`.`enabled`=1) ORDER BY `employee`.`id` DESC, `custom_company_relation`.`id`, `Employee<enabledCompany><company>`.`id`
Another example, pay attention that we joining relation 'projects' two times, thru different models, and they are auto aliased different:
$query = Employee::find()->joinWith(['company' => function ($q) {
$q->joinWith(['projects']);
$q->andWhere([$q->getRelationColumn('projects', 'enabled') => 1]);
return $q;
}, 'enabledCompany', 'projects'])->andWhere(['id' => [1,2,3]])->orderBy(['id' => SORT_DESC]);
$query = $query->andWhere([
$query->getRelationColumn('company', 'status') => 4,
$query->getRelationColumn('enabledCompany', 'status') => 8,
$query->getRelationColumn('projects', 'enabled') => 0,
])->all();
query:
SELECT `employee`.* FROM `employee` LEFT JOIN `company` `Employee<company><company>` ON `employee`.`company_id` = `Employee<company><company>`.`id` LEFT JOIN `project` `Company<projects><project>` ON `Employee<company><company>`.`id` = `Company<projects><project>`.`company_id` LEFT JOIN `company` `Employee<enabledCompany><company>` ON `employee`.`company_id` = `Employee<enabledCompany><company>`.`id` LEFT JOIN `employee_to_project` `Employee<employeeToProject><employee_to_project>` ON `employee`.`id` = `Employee<employeeToProject><employee_to_project>`.`employee_id` LEFT JOIN `project` `Employee<projects><project>` ON `Employee<employeeToProject><employee_to_project>`.`project_id` = `Employee<projects><project>`.`id` WHERE (`employee`.`id` IN (1, 2, 3)) AND ((`Employee<company><company>`.`status`=4) AND (`Employee<enabledCompany><company>`.`status`=8) AND (`Employee<projects><project>`.`enabled`=0)) AND (`Company<projects><project>`.`enabled`=1) AND (`Employee<projects><project>`.`deal`=1) ORDER BY `employee`.`id` DESC, `Employee<company><company>`.`id`, `Company<projects><project>`.`id`
I have ready made solution for this, and it is totally optional for using, just need to decide where to put that option for enabling, and it passing all test (also for YII3 Active Record package).
I am aware of all previous issues regarding this matter. Just need to sort some things out, and will make PR. So far, with this changes, which make mention queries, all YII2 and YII3-AR tests pass
Cool. If this is done already, a pull request would be very handy to check if it is a good change for the framework.
@ntesic I think this will not work properly. In your example you have 3 tables with an id property. If you do
Employee::find()->joinWith(['company.projects'])->andWhere(['id' => [1,2,3]])->orderBy(['id' => SORT_DESC])->all();
where should PHP know from which id is meant? If you take the employee one (because it's the initial AR), but would like to sort by projects one, how will you achieve that? I think it would be more confusing...
@simialbi in that case it would be:
$query = Employee::find()->joinWith(['company.projects']);
$query->andWhere(['id' => [1,2,3]])->orderBy([$query->getRelationColumn('projects', 'id') => SORT_DESC])->all();
Query:
SELECT `employee`.* FROM `employee` LEFT JOIN `company` `Employee<company><company>` ON `employee`.`company_id` = `Employee<company><company>`.`id` LEFT JOIN `project` `Company<projects><project>` ON `Employee<company><company>`.`id` = `Company<projects><project>`.`company_id` WHERE `employee`.`id` IN (1, 2, 3) ORDER BY `Company<projects><project>`.`id` DESC
🤔 IMHO it's confusing. If you don't exactly know this behavior, you do not expect it. And if you still have to get relation columns manually, it may be to slow to have a real benefit.
yes, of course you need to get relation columns manually, but not need to worry about alias, since even now you need to set alias manually and also to include it in relation column manually, to achieve something like above