Why would contain fail to create a join?

Need Help
1

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.

2

What is the error message you’re getting?

3

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.

4

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

5

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 it will create query with INNER JOIN and give you expected results

1 Like
6

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.

7

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

1 Like
8

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).