In an app that uses CakePHP 2.9.2, I have the goal of using custom finders along with pagination with a condition.
First point: I need to filter the records based on a condition related to the business logic. So, I understand that the correct way to do this is creating a custom finder. Note that the condition involves two models: Alfa & Beta.
class Alfa extends AppModel
}
// (...)
protected function _findWithoutPending($state, $query, $results = array())
{
if ($state === 'before') {
$query = array_merge($query, array(
'conditions' => array(
array(
'NOT' => array(
'Beta.done' => null
)
)
)
));
$query['joins'] = array(
array(
'alias' => 'Beta',
'table' => 'betas',
'type' => 'LEFT',
'conditions' => 'Alfa.id = Beta.alfa_id'
)
);
return $query;
}
return $results;
}
Second point: I want to only retrieve records from the logged in user. Because the login info is on the Controller layer, I understand that this condition must be passed to PaginatorComponent. So, in the Controller, I do:
// Apply filter from a custom finder
$this->paginate = array(
'findType' => 'withoutPending'
);
// Retrieve only records from the logged in user
$this->Paginator->settings = array(
$this->modelClass => array(
'conditions' => array(
$this->modelClass . '.user_id' => (int) $this->Auth->user('id')
)
)
);
// Paginate
$alfas = $this->paginate();
$this->set(compact('alfas'));
The issue is that with this code, Cake is only filtering by the Auth criteria:
WHERE `Alfa `.`user_id` = 4 ORDER BY //...
And, just removing the $this->Paginator->settings block, then Cake applies this condition:
WHERE NOT (`Beta `.`done` IS NULL) ORDER BY //...
I want this query:
WHERE `Alfa `.`user_id` = 4
AND NOT (`Beta `.`done` IS NULL)
ORDER BY //...
How can I achieve this with CakePHP2?
Thank you very much.