Searching data from associated models in CakePHP

Question

I am using CakePHP 2.3.6. In my project, I have to search users using some values/data/parameter, which are in several tables, and the tables are associated.

The associations are :

in the model User.php :

public $hasMany=array('Education'=>array(
                        'className'=>'Education
                        'foreignKey'=>'user_id'
                  ),
                  'Experience'=>array(
                        'className'=>'Experience',
                        'foreignKey'=>'user_id'
                  ),
                  'Employment'=>array(
                        'className'=>'Employment',
                        'foreignKey'=>'user_id'
                  ),
                  'ProfessionalQualification'=>array(
                        'className'=>'ProfessionalQualification',
                        'foreignKey'=>'user_id'
                  )
)

in the model Education.php :

public $belongsTo=array('User'=>array(
                        'className'=>'User',
                        'foreignKey'=>'user_id'
                    )
)

in the model Experience.php :

public $belongsTo=array('User'=>array(
                        'className'=>'User',
                        'foreignKey'=>'user_id'
                    )
)

in the model Employment.php :

public $belongsTo=array('User'=>array(
                            'className'=>'User',
                            'foreignKey'=>'user_id'
                        )
)

in the model ProfessionalQualification.php :

public $belongsTo=array('User'=>array(
                        'className'=>'User',
                        'foreignKey'=>'user_id'
                    )
)

Now, in the Search form (View/Users/search.ctp) :

echo $this->Form->create('User');
echo $this->Form->input('name',array('type'=>'text'));
echo $this->Form->input('username',array('type'=>'text'));
echo $this->Form->input('address',array('type'=>'textarea'));
echo $this->Form->input('phone',array('type'=>'tel'));
echo $this->Form->input('degree',array('type'=>'text'));
echo $this->Form->input('passing_year',array('type'=>'text'));
echo $this->Form->input('title',array('type'=>'text'));
echo $this->Form->input('title.description',array('type'=>'textarea'));
echo $this->Form->input('company',array('type'=>'text'));
echo $this->Form->input('company.description',array('type'=>'textarea'));
echo $this->Form->input('certificate',array('type'=>'text'));
echo $this->Form->input('certificate.description',array('type'=>'textarea'));
echo $this->Form->submit('Search');
echo $this->Form->end();

in the UsersController.php controller :

public function search(){
    $this->set('title_for_layout','Search User');

    if($this->request->is('post')){
       $conditions=array();
       $data=$this->request->data;
   if(!empty($data['name']))
      $conditions['name']="%".$data['name']."%";
   if(!empty($data['username']))
      $conditions['username']=$data['username'];
   if(!empty($data['address']))
      $conditions['address']='%'.$data['address'].'%';
   if(!empty($data['phone']))
      $conditions['phone']=$data['phone'];
   if(!empty($data['degree']))
      $conditions['degree']="%".$data['degree']."%";
       .
       .
       .
   if(!empty($data)){
      $users=$this->User->find('all',array('conditions'=>$conditions));
          if(!empty($users)){
             $this->Session->setFlash("Searching Users Successful");
             $this->set('users',$users);
          }else
             $this->Session->setFlash("No user found");
   }else
      $this->Session->setFlash("You didn't give any info to search for");
    }
}

I thought it'd retrieve all data I am looking for, from all the associated tables, in an array. But, it gives an error :

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'degree' in 'where clause'

SQL Query: SELECT `User`.`id`, `User`.`name`, `User`.`username`, `User`.`phone`, `User`.`address`, `User`.`created`, `User`.`modified` FROM `db_name`.`users` AS `User` WHERE `degree` = 'M.Sc.'

Here, "degree" is from the table "Education".

Then, I used ContainableBehavior. In the User.php model :

public $actsAs=array('Containable');

In the UsersController.php :

$this->User->find('all',array('contain'=>array('Education'=>array(
                                                'conditions'=>array('Education.institution ='=>"Institute 1")
                                               ),
                                               'Employment'=>array(
                                                'conditions'=>array('Employment.employer ='=>"Employer 1")
                                               ),
                                               'ProfessionalQualification'=>array(
                                'conditions'=>array('ProfessionalQualification.certificate ='=>"Certificate 1")
                                               ),
                                               'Experience'=>array(
                                'conditions'=>array('Experience.title ='=>"Title 1")
                                               )
                         )
                 )
);

When I use this, the result array is :

array(
(int) 0 => array(
    'User' => array(
        'name' => 'Name 3',
        'email' => 'user3@email.com',
        'mobile' => '9325028505',
        'id' => '4'
    ),
    'Education' => array(),
    'Employment' => array(),
    'ProfessionalQualification' => array(),
    'Experience' => array()
),
(int) 1 => array(
    'User' => array(
        'name' => 'Name 2',
        'email' => 'user2@email.com',
        'mobile' => '9082730572',
        'id' => '3'
    ),
    'Education' => array(),
    'Employment' => array(),
    'ProfessionalQualification' => array(),
    'Experience' => array()
),
(int) 2 => array(
    'User' => array(
        'name' => 'Name 1',
        'email' => 'user1@email.com',
        'mobile' => '715414918934',
        'id' => '2'
    ),
    'Education' => array(
        (int) 0 => array(
            'id' => '1',
            'applicant_id' => '2',
            'level' => 'Secondary',
            'degree_title' => 'S.S.C',
            'passing_year' => '2009',
            'institution' => 'Institute 1',
            'result' => '4',
            'major' => 'Science',
            'created' => '2014-05-18 16:48:08',
            'modified' => '2014-05-18 17:20:12'
        )
    ),
    'Employment' => array(
        (int) 0 => array(
            'id' => '1',
            'applicant_id' => '2',
            'employer' => 'Employer 1',
            'position_held' => 'Position 1',
            'industry' => 'Industry 1',
            'department' => 'Department 1',
            'major_responsibilities' => 'Responsibilities 1',
            'job_location' => 'Local',
            'key_achievement' => 'Achievements 1',
            'served_from' => '2005-03-12',
            'served_till' => '2007-11-26',
            'created' => '2014-05-18 16:48:08',
            'modified' => '2014-05-18 17:20:12'
        )
    ),
    'ProfessionalQualification' => array(
        (int) 0 => array(
            'id' => '1',
            'applicant_id' => '2',
            'name_of_certificate' => 'Certificate 1',
            'institute' => 'Institute 1',
            'from' => '2011-10-11',
            'to' => '2012-09-11',
            'location' => 'Local Ins.',
            'created' => '2014-05-18 16:48:08',
            'modified' => '2014-05-18 17:20:12'
        )
    ),
    'Experience' => array(
        (int) 0 => array(
            'id' => '1',
            'applicant_id' => '2',
            'title' => 'Title 1',
            'institute' => 'Institute 1',
            'training_year' => '2013',
            'location' => 'Local Ins.',
            'created' => '2014-05-18 16:48:08',
            'modified' => '2014-05-18 17:20:12'
        )
    )
),
(int) 3 => array(
    'User' => array(
        'name' => 'Name 4',
        'email' => 'user4@email.com',
        'mobile' => '9082730572',
        'id' => '5'
    ),
    'Education' => array(),
    'Employment' => array(),
    'ProfessionalQualification' => array(),
    'Experience' => array()
)
)

Here, notice, that I only need the 2 no. array, nothing else, but it gives me all data that are not relevant.

Can you please tell me where is the problem ? What should I do ? I guess, I have to explicitly give the table names in the form, on the specific fields, like : ModelName.0.field. What do you think?

Answer 1

try this

  public function global_search() {
        $condition = array(
            'User.role' => array('U', 'P'),
            'User.user_status' => array('active', 'lead', 'inactive')
        );
        if ($this->request->is('post') || $this->request->is('put')) {


            $or = array(
                'PersonalInformation.first_name' => $this->request->data['User']['first_name'],
                'PersonalInformation.last_name' => $this->request->data['User']['last_name'],
                'PersonalInformation.primary_phone' => $this->request->data['User']['primary_phone'],
                'PersonalInformation.dob' => $this->request->data['User']['dob'],
                'User.email' => $this->request->data['User']['email'],
            );
            //==========This Function allow remove blank element from array=========//

            $or = array_filter($or);

            //=====End============//

            $condition = array(
                'User.role' => array('U', 'P'),
                'User.user_status' => array('active', 'lead', 'inactive'),
                'OR' => $or
            );

            $data = $this->User->find('first', array(
                'conditions' => $condition)
            );
            // pr($data); exit;
            $this->set('allusers', $data);
        }

        $data = $this->User->find('all', array(
            'conditions' => $condition)
        );
        //pr($data); exit;
        $this->set('allusers', $data);
        $this->layout = 'admin';
    }