CakePHP LEFT JOIN sorting issue

I’m using cakePhp 2.x version and following is my view and controller for a datagrid, I want Area and Boxes both to be sorted by user. Box can be sorted, but Area first sorting is working, after that when click on sorting, page is refreshed but it doesn’t sort. What could be the reason?

<table class="index">
<thead>
    <tr>
        <th><?php echo $this->Paginator->sort('A.title', 'Area')?></th>
        <th><?php echo $this->Paginator->sort('B.title', 'Boxes')?></th>
        <th>Internal</th>
        <th>External</th>
    </tr>
</thead>

$this->paginate['contain'] = array();
$this->paginate['joins'] = array(
    array(
        'table' => 'a_table',
        'alias' => 'A',
        'type' => 'LEFT',
        'conditions' => array(
            'A.key = B.id',
            'A.status' => 'Active'
        )
    )
);

$conditions = array();
$this->paginate['fields'] = array('B.*', 'A.title');
$this->paginate['order'] = array('A.title', 'B.title');

if(!empty($this->request->data['SignalBox']))
{
    if($this->request->data['SignalBox']['box'])
        $conditions['B.title LIKE'] = '%'.$this->request->data['SignalBox']['box'].'%';

    if($this->request->data['SignalBox']['area'])
        $conditions['A.title'] = $this->request->data['SignalBox']['area'];
}

if($conditions)
    $this->paginate['conditions'] = $conditions;

$data = $this->paginate('B');

$this->set('data', $data);

Answer

Final solution was, change the Model based on the submitted sorting column,

$model ='A';

if($this->request->param('named.sort')){
    $submittedModel = explode('.',$this->request->param('named.sort'));
    $model =$submittedModel [0];
}

Then change the JOIN query and $data = $this->paginate('B');

if(A)
{
   A JOIN query
}
else
{
   B JOIN query
}

Advertisement

Answer