Issue with using innerJoinWith/matching with where

Need Help
1

Hello,
I’m having trouble with adding innerJoinWith/matching with my code to filter select results.
When I add innerJoinWith to a $query, an ambiguous clause error occurred.

Following code is a simplified sample that happens same error.

Model relationships: Articles hasMany Authors / Authors belongsTo Articles.
and both tables have “id” column.

$query = TableRegistry::get(‘Articles’)->find()->where([“id” => 1]); # <- original code
$query->innerJoinWith(“Authors”, # <- added for filtering
function ($q) {
return $q->where([
‘Authors.id’ => 9999
]);
}
);
echo $query->first();

This code makes a SQL below. (tried on CakePHP 3.4 and 3.8)

SELECT Articles.id AS Articles_id, … , Articles.modified AS Articles__modified
FROM articles Articles INNER JOIN authors Authors
ON ( Authors.id = 9999 AND Articles.id = (Authors.article_id))
WHERE id = 1
LIMIT 1

and MySQL error “Column ‘id’ in where clause is ambiguous” occurs.

Of course, it works fine if I add the table name to the first where clause like “Articles.id” => 1,
but I think it is not a smart solution as there are a lot of existing codes to be fixed.

Did anyone know simple solution to this issue?

Thanks

Postscript:
Adding contain([‘Authors’]) didn’t solve this issue.

2

There’s no other solution that I know, you have to resolve the ambiguity. Another way to do it would be using “TableRegistry::get(‘Articles’)->findById(1)” instead.

My way to be smart would be to put all query code inside methods in the table objects and DRY. This way changing the queries would be easier. And also writing explicit code that avoids potential ambiguities so it’s more friendly to future modifications.

3

Thank you very much for your reply.
Columns and conditions on where clauses are not fixed, so I couldn’t use findById or table methods.
I will remove ambiguity from my code.