# Why would contain fail to create a join?

**URL:** <https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053>\
**Category:** Need Help\
**Created:** [August 26, 2016, 3:34am UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053 "2016-08-26T03:34:18Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![calzone](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/calzone/32/305_2.png) [@calzone](https://discourse.cakephp.org/u/calzone)\
**Post date:** [August 26, 2016, 3:34am UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053/1 "2016-08-26T03:34:18Z")

</div>

So I’m using contain in a query and my where clause depends on a condition matching a field in the joined table – the one listed in -\>contain() – and the query is failing because it can’t find the field. No surprise it can’t find the field though, it’s not even trying to join any other table.

Anyone bump against something like this before? What would cause contain to just be ignored wholesale?

I’m always encountering these bizarre head-scratchers in CakePHP that always turn out be due to some small, overlooked, detail somewhere, yet the error thrown is totally a red herring.

---

<div class="post-metadata">

**Author:** ![markstory](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/markstory/32/182_2.png) [@markstory](https://discourse.cakephp.org/u/markstory)\
**Post date:** [August 26, 2016, 2:14pm UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053/2 "2016-08-26T14:14:18Z")

</div>

What is the error message you’re getting?

---

<div class="post-metadata">

**Author:** ![calzone](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/calzone/32/305_2.png) [@calzone](https://discourse.cakephp.org/u/calzone)\
**Post date:** [August 26, 2016, 3:43pm UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053/3 "2016-08-26T15:43:11Z")

</div>

> Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘user\_id’ in ‘where clause’

> If you are using SQL keywords as table column names, you can enable identifier quoting for your database connection in config/app.php.

In this case:

Plan belongsTo Fiscalyear, and Fiscalyear hasMany Plans.

**user\_id** is a field in the plans table. I am running a query to return all Plans for all Fiscalyears for the user who is logged in ( **user\_id** ). To keep things simple, I’ve just hardcoded **-\>where([user\_id =\> 1])** for now.

The query looks like this:

```
        $fiscalyears = $this->Plans->Fiscalyears->find('all')
    	->contain(['Plans'])
    	->where(['Plans.user_id'=>1]);

```

Yes, I’m running this query from the Plans controller. Maybe that has something to do with the problem?

The outputted query looks like this:

> SELECT Fiscalyears.id AS `Fiscalyears __id`, Fiscalyears.name AS `Fiscalyears__ name` FROM fiscalyears Fiscalyears WHERE Plans.user\_id = :c0

FWIW, I’ve tried editing my **-\>where** to use **Plans.user\_id** , **Plan.user\_id** , and simply **user\_id** …but as you can see, the real problem is that I don’t have the Plans table joined in the generated query.

---

<div class="post-metadata">

**Author:** ![calzone](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/calzone/32/305_2.png) [@calzone](https://discourse.cakephp.org/u/calzone)\
**Post date:** [August 29, 2016, 3:45pm UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053/4 "2016-08-29T15:45:27Z")

</div>

Can someone at least tell me whether I’m doing something wrong or if this supposed to work and there is a bug somewhere?

---

<div class="post-metadata">

**Author:** ![Graziel](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@Graziel](https://discourse.cakephp.org/u/Graziel)\
**Post date:** [August 29, 2016, 4:16pm UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053/5 "2016-08-29T16:16:22Z")

</div>

by default when you have `hasMany` association cake grabs the data by splitting it into 2 queries, so as you can see  
`SELECT Fiscalyears.id AS Fiscalyears __id, Fiscalyears.name AS Fiscalyears__ name FROM fiscalyears Fiscalyears WHERE Plans.user_id = :c0`  
doesnt contain any fields from `Plans` what you want is [http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#filtering-by-associated-data](http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#filtering-by-associated-data) it will create query with `INNER JOIN` and give you expected results

---

<div class="post-metadata">

**Author:** ![calzone](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/calzone/32/305_2.png) [@calzone](https://discourse.cakephp.org/u/calzone)\
**Post date:** [August 29, 2016, 7:51pm UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053/6 "2016-08-29T19:51:15Z")

</div>

I tried using -\>matching() and it almost works, but what I was really looking for is a complete listing of Fiscalyears and only those associated plans that belong to the user. But I still want all Fiscalyears, even if the user hasn’t yet created a plan for any of them.

I’m looking for an outer join then. I’m pretty sure cake \< 3.x supported this transparently through recursion. Hence I was trying to use -\>contain thinking it would achieve the same thing except with me choosing which tables to include.

---

<div class="post-metadata">

**Author:** ![Graziel](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@Graziel](https://discourse.cakephp.org/u/Graziel)\
**Post date:** [August 29, 2016, 9:28pm UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053/7 "2016-08-29T21:28:35Z")

</div>

oh in that case you just need to pass  
`$fiscalyears = $this->Plans->Fiscalyears->find('all') ->contain(['Plans' => ['conditions' => ['Plans.user_id' => 1]]]);`

i got mislead by : P

> [@calzone](#):
>
> condition matching a field

---

<div class="post-metadata">

**Author:** ![calzone](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/calzone/32/305_2.png) [@calzone](https://discourse.cakephp.org/u/calzone)\
**Post date:** [August 30, 2016, 2:22am UTC](https://discourse.cakephp.org/t/why-would-contain-fail-to-create-a-join/1053/8 "2016-08-30T02:22:56Z")

</div>

> [@Graziel](#):
>
> $fiscalyears = $this-\>Plans-\>Fiscalyears-\>find(‘all’) -\>contain([‘Plans’ =\> [‘conditions’ =\> [‘Plans.user\_id’ =\> 1]]]);

I wish I could like your post over and over again. Thank you!!! It is as elegant a solution as can be. I have renewed confidence for the future of Cake now (though some 3.x changes still seem a bit arcane).