# Issue with using innerJoinWith/matching with where

**URL:** <https://discourse.cakephp.org/t/issue-with-using-innerjoinwith-matching-with-where/7234>\
**Category:** Need Help\
**Created:** [February 5, 2020, 9:30am UTC](https://discourse.cakephp.org/t/issue-with-using-innerjoinwith-matching-with-where/7234 "2020-02-05T09:30:38Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![hoge](https://avatars.discourse-cdn.com/v4/letter/h/b487fb/32.png) [@hoge](https://discourse.cakephp.org/u/hoge)\
**Post date:** [February 5, 2020, 9:30am UTC](https://discourse.cakephp.org/t/issue-with-using-innerjoinwith-matching-with-where/7234/1 "2020-02-05T09:30:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![berarma](https://avatars.discourse-cdn.com/v4/letter/b/c5a1d2/32.png) [@berarma](https://discourse.cakephp.org/u/berarma)\
**Post date:** [February 5, 2020, 9:47am UTC](https://discourse.cakephp.org/t/issue-with-using-innerjoinwith-matching-with-where/7234/2 "2020-02-05T09:47:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![hoge](https://avatars.discourse-cdn.com/v4/letter/h/b487fb/32.png) [@hoge](https://discourse.cakephp.org/u/hoge)\
**Post date:** [February 6, 2020, 9:46am UTC](https://discourse.cakephp.org/t/issue-with-using-innerjoinwith-matching-with-where/7234/3 "2020-02-06T09:46:42Z")

</div>

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.