How do i implement search on third level using containable behavior

Question

Hi,

I have developed a cake php application.

In this there are tables like students,placements,batches,companies

In placements table there is student_id,company_id and in students table there is batch_id column.

In placements index page i have applied jq grid. Here is the screen shot.

I want to give searching on student,company and batch. For this i have used containable behavior inside placements controller index function.

            if( $this->params['url']['_search'] == 'true' ) /* For Searching*/
        {
            //pr($this->params);

            $searchconditions = array();

            if( isset($this->params['url']['studentname']) && !empty($this->params['url']['studentname']) )
            {
                array_push(&$searchconditions, array('Student.fullname LIKE' => $this->params['url']['studentname'] . '%'));
            }
             if( isset($this->params['url']['companyname']) && !empty($this->params['url']['companyname']) )
            {
                array_push(&$searchconditions, array('Company.name LIKE' => $this->params['url']['companyname'] . '%'));
            }
             if( isset($this->params['url']['batchname']) && !empty($this->params['url']['batchname']) )
            {
                array_push(&$searchconditions, array('Batch.name LIKE' => $this->params['url']['batchname'] . '%'));
            }

            $result = $this->Placement->find('all', array(
                'fields' => array('id','student_id','company_id'),
                'contain' => array(
                    'Student' => array(
                        'fields' => array('id','fullname','batch_id'),
                        'Batch' => array(
                            'fields'=> array('id','name')
                        )       
                    ),
                    'Company' => array(
                        'fields' => array('id','name')
                    )
                ),
                'conditions' => $searchconditions,
                'order' => $sort_range,
                'limit' => $limit_range
            ));
        }
        else /* Default display*/
        {
            $result = $this->Placement->find('all', array(
                'fields' => array('id','student_id','company_id'),
                'contain' => array(
                    'Student' => array(
                        'fields' => array('id','fullname','batch_id'),
                        'Batch' => array(
                            'fields'=> array('id','name')
                        )       
                    ),
                    'Company' => array(
                        'fields' => array('id','name')
                    )
                ),
                'order' => $sort_range,
                'limit' => $limit_range
            ));
        }

The grid is populated fine with student name , company name and batch name (if case). Searching is also working fine in case i searched by student name & company name but when i tried to search by batch name i get the following error:

Warning (512): SQL Error: 1054: Unknown column 'Batch.name' in 'where clause' [APP\vendors\cakephp\cake\libs\model\datasources\dbo_source.php, line 681]

Query: SELECT `Placement`.`id`, `Placement`.`student_id`, `Placement`.`company_id`, `Student`.`id`, `Student`.`fullname`, `Student`.`batch_id`, `Company`.`id`, `Company`.`name` FROM `placements` AS `Placement` LEFT JOIN `students` AS `Student` ON (`Placement`.`student_id` = `Student`.`id`) LEFT JOIN `companies` AS `Company` ON (`Placement`.`company_id` = `Company`.`id`)  WHERE `Batch`.`name` LIKE 'df%'   ORDER BY `Placement`.`id` asc  LIMIT 0,10 

I think the relation between student & batch is not working as can be seen from the query.

I am unable to figure out why it is behaving as such.

Please help me on this.

Thanks

Answer 1

The Containable behavior doesn't make a join, but makes separate queries for each table.

Use the linkable plugin. It's an excellent plugin. It will definitely solve your problem.

Answer posted by Mr. sytzeloor on cakeqs site.

Using this plugin you will be able to easily do a find operation in the placement model based on a batch name.

Do like this in your placements controller and I bet you will get results as expected.

$result = $this->Placement->find('all', array(
                'fields' => array('id','student_id','company_id'),
                'link' => array(
                    'Student' => array(
                        'Batch'
                    ),
                    'Company'
                ),
                'conditions' => $searchconditions,
                'order' => $sort_range,
                'limit' => $limit_range
            ));