Asked  7 Months ago    Answers:  5   Viewed   33 times

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?

 Answers

42

I've been able to get it working somewhat, but the solution doesn't feel very cakey at all.

$items = $this->paginate(
    $this->Request->ToLocation->Item,
    array(
        "Item.id IN ("
        . "SELECT item_id FROM items_locations "
        . "WHERE location_id = " . $locationId
        . ")"
    )
);
Wednesday, March 31, 2021
 
jerrygarciuh
answered 7 Months ago
82

The problem was in the Command prompt, when I run my tests in Windows PowerShell everything was OK.

Saturday, May 29, 2021
 
John_BSDthos
answered 5 Months ago
12

Since it's a hasMany relationship, that means Cake will need to make 2 separate queries: 1 on the users table, and one on the histories table to retrieve all the associations. Since the History data isn't being retrieved until the 2nd query, then your 1st query cannot be filtered via WHERE conditions for fields found in the History model.

To resolve this, you can do one of two things:

  1. Perform pagination on History using Containable (since History belongsTo User, meaning only 1 query will be performed).

  2. Perform pagination on User the way you're already doing, except perform an ad-hoc join to History such that it's no longer a hasMany relationship.

e.g.:

$this->User->bindModel(array('hasOne' => array('History')));
$this->paginate['User']['contain'][] = 'History';
$this->paginate('User', array('History.some_field' => 'some_value'));
Saturday, May 29, 2021
 
Fredy
answered 5 Months ago
14

Try

public function index() {
    $this->Paginator->settings = array(
        'limit' => 1,
        'order' => array(
            'Recipe.title' => 'asc'
        )
    );
    $this->set('recipes', $this->Paginator->paginate());
}
Saturday, May 29, 2021
 
devo
answered 5 Months ago
84

This fails because Cake is actually using 2 different queries to generate your result set. As you've noticed, the first query doesn't even contain a reference to Cuisine.

As @vindia explained here, using the Containable behavior will usually fix this problem, but it doesn't work with Paginate.

Basically, you need a way to force Cake to look at Cuisine during the first query. This is not the way the framework usually does things, so it does, unfortunately, require constructing the join manually . paginate takes the same options as Model->find('all'). Here, we need to use the joins option.

var $joins = array(
    array(
        'table' => '(SELECT cuisines.id, cuisines.name, cuisines_restaurants.restaurant_id
                 FROM cuisines_restaurants 
                 JOIN cuisines ON cuisines_restaurants.cuisines_id = cuisines.id)',
        'alias' => 'Cuisine',
        'conditions' => array(
            'Cuisine.restaurant_id = Restaurant.id',
            'Cuisine.name = "italian"'
        )
    )
);

$this->paginate = array(
    'conditions' => $opts,
    'limit' => 10,
    'joins' => $joins
);

This solution is a lot clunkier than the others, but has the advantage of working.

Monday, August 2, 2021
 
Rafal
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :