I'm having some problems with creating pagination with a HABTM relationship. First, the tables and relationships:
requests (id, to_location_id, from_location_id)
locations (id, name)
items_locations (id, item_id, location_id)
items (id, name)
So, a Request has a Location the request is coming from and a Location the Request is going to. For this question, I'm only concerned about the "to" location.
Request --belongsTo--> Location* --hasAndBelongsToMany--> Item
(* as "ToLocation")
In my RequestController, I want to paginate all the Items in a Request's ToLocation.
// RequestsController
var $paginate = array(
'Item' => array(
'limit' => 5,
'contain' => array(
"Location"
)
)
);
// RequestController::add()
$locationId = 21;
$items = $this->paginate('Item', array(
"Location.id" => $locationId
));
And this is failing, because it is generating this SQL:
SELECT COUNT(*) AS count FROM items Item WHERE Location.id = 21
I can't figure out how to make it actually use the "contain" argument of $paginate...
Any ideas?
