Find all with contain in cakePhp 2.5.6

Need Help
1

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:

$users = $this->User->find('all',array('recursive' =>2,'contain'=>array('UserxGroup'),'conditions'=>array('UserxGroup.group_id' => $this->controllerUser['group_id'],'UserxGroup.net_id' => $this->controllerUser['net_id'],'UserxGroup.main_gate' => $this->controllerUser['main_gate'],'User.profile_id IN' => array(3,4,5,6))));
$this->set('model',$users);
2

Let me clarify that UserxGroup is a hasmany relation to User, that is User has many UserxGroup.
This is the code for User:

<?php
     App::uses('BlowfishPasswordHasher', 'Controller/Component/Auth');
     class User extends AppModel {
         var $name = 'User';

         public $virtualFields = array(
            'full_name' => "CONCAT(User.firstname, ' ',User.lastname)"
        );
         
        public $displayField = 'full_name';

        public $order = array('User.profile_id', 'User.lastname');
         
        public $belongsTo = array(
            'Profile' => array(
                'className' => 'Profile'
            ),
            'Referrer' => array(
                'className' => 'User',
                'foreignKey' => 'referrer_id'
            )
        );

        public $hasMany = array(
            'UserxGroup' => array(
                'className' => 'UserxGroup',
                'foreignKey' => 'user_id'
            ),
            'Pacient' => array(
                'className' => 'Pacient'
            )
        );

         public function beforeSave($options = array()) {
             if (isset($this->data[$this->alias]['password'])) {
                 $passwordHasher = new BlowfishPasswordHasher();
                 $this->data[$this->alias]['password'] = $passwordHasher->hash(
                 $this->data[$this->alias]['password']
                 );
             }
             return true;
         }

         var $actsAs = array(
            'UploadPack.Upload' => array(
                'image' => array(
                    'styles' => array(
                        'thumb' => '200x200',
                        'detail' => '512x512'
                    )
                )
            )
        );
     }
?>

And this is the code for UserxGroup:

<?php
     class UserxGroup extends AppModel {
         var $name = 'UserxGroup';
         
        public $belongsTo = array(
            'User' => array(
                'className' => 'User',
                'foreignKey' => 'user_id'
            ),
            'Net' => array(
                'className' => 'Net',
                'foreignKey' => 'net_id'
            ),
            'Group' => array(
                'className' => 'Group',
                'foreignKey' => 'group_id'
            )
        );
     }
?>
3

You can try to print the $user and check your query is correct or not.

4

I can’t, when I try to enter to the page if gives me this error:

error-users
error-users1920×1080 284 KB

5

I changed the code of the controller to this:

$users = $this->User->find('all',array('recursive'=>2,'contain'=>array('UserxGroup'=>array('conditions'=>array('User.profile_id IN' => array(3,4,5,6),'UserxGroup.group_id' => $this->controllerUser['group_id'],'UserxGroup.net_id' => $this->controllerUser['net_id'],'UserxGroup.main_gate' => $this->controllerUser['main_gate'])))));
$this->set('model',$users);

Can you tell me where is the error?

6

Please answer me this question as soon as possible, I need with urgency to solve this issue.
Thank you very much.

9

User is the model I use to authenticate in login action, maybe I have to define somewhere else the hasmany relation with UserxGroup, if so, where should I do it?

10

Can you please answer me this question? I have to solve this issue with urgency, my boss is pressing me.

11

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.

12

No, the problem is that when I put recursive = 2 the model only retrieves the belongsto relations and not the hasmany, how should I do to have the hasmany relations included in the query?

13

Did you resolve that error page that you were seeing? That’s what my comment was addressing.

14

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.

15

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”?

16

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

17

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.

18

Unfortunately the Referrer relation has to be included, so how can I do a more targeted contain-based approach?

19

https://book.cakephp.org/2/en/core-libraries/behaviors/containable.html

I’m not sure why it references Cake 1.2; it’s from the v2 cookbook.

20

How can I retrieve the data from the belongsto relations with joins?
This one:

$options['joins'] = array(array('table' => 'userx_groups','alias' => 'UserxGroup','type' => 'LEFT','foreignKey' => false,'conditions' => array('User.id = UserxGroup.user_id')),array('table' => 'groups','alias' => 'Group','type' => 'LEFT','foreignKey' => false,'conditions' => array('Group.id = UserxGroup.group_id')),array('table' => 'nets','alias' => 'Net','type' => 'LEFT','foreignKey' => false,'conditions' => array('Net.id = UserxGroup.net_id')));
21

I don’t think you should need to use joins at all in order to get what you want. Hopefully, someone that knows Cake 2 will be able to help you more, if the documentation isn’t sufficient.

22

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?