I have a controller where I have a find all with a contain clause and in the conditions I want to add a condition with the contain, but the controller doesn’t add me the join for the contain, please tell me where is the error.
This is the code of the controller:
In your error message, it indicates that the query causing problems is trying to reference the column User.firstname, and indeed one of the fields is CONCAT(User.firstname, User.lastname). But it’s selecting FROM users AS Referrer. Your error is in having User hardcoded into your virtual field definition.
I’m not sure how to go about making that field definition work with the table alias in Cake 2, but that’s the problem that you need to solve.
No, I took out the User. in the virtual field and gave me an ambiguous field error when I logged into the site, the virtual field is not the problem, the problem is that the model doesn’t include the hasmany relation in the query, the referrer is a belongsto relation between User with itself.
So, to be clear, you took out the User., made no other changes, and now it’s giving an “ambiguous field” error instead of “unknown column” when you run your code? And that error is the problem which you are characterizing as “the model doesn’t include the hasmany relation in the query”?
Yes, I didn’t make the definition of the virtual field, another person was the developer of that part and never had a problem, it worked perfectly well in the site, I added the functionality of having a hasmany between User and UserxGroup and began to have problems
Okay. I’ll try to explain it more. That virtual field definition is most definitely the cause of the problem that you are having. When you added the new relation, or perhaps when you expanded recursive, the virtual field started to get used in a way that it never had before.
When Cake tries to load the Referrer relation, it includes the virtual field definition in the query, because that’s how virtual fields work. But the way it was defined referenced the table as “User”, and in the new query that happens for the hasMany, the users table is given a different name (aka alias), Referrer. That caused the “Unknown column” SQL error.
You removed the User part of the definition, but that also breaks things because now it doesn’t know which first_name out of several that might apply in the tables referenced by a query somewhere. That’s the source of your “Ambiguous field” SQL error.
So, as I said way back when, one way to fix this is to change the virtual field definition so that it includes whatever alias is being used, instead of User. Unfortunately, I don’t know how to do that in Cake 2. I expect this sort of thing is part of why things have changed very dramatically in Cake 3. Maybe you can use beforeFind in that table to change the definition of virtualFields on every call? That might work, or it might just break something else.
Alternately, I can’t say, based on the code shown, why Referrer is coming into it at all. If you don’t need the Referrer relation to be included in the function you’re working on, then you might change from using recursive = 2 (which will indiscriminately pull in all possible relations, including Referrer) to a more targeted contain-based approach.
Maybe you could tell me how to migrate from version 2 to version 3, and also tell me which version of version 3 you recommend me, is there an easy and automatic way to migrate to version 3 and do you think that migrating to version 3 will solve this issue?