Asked  2 Years ago    Answers:  5   Viewed   81 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

1

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
        . ")"
    )
);
Sunday, October 23, 2022
 
2

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

Sunday, October 23, 2022
3

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, November 26, 2022
 
2

Try

public function index() {
    $this->Paginator->settings = array(
        'limit' => 1,
        'order' => array(
            'Recipe.title' => 'asc'
        )
    );
    $this->set('recipes', $this->Paginator->paginate());
}
Thursday, August 4, 2022
4

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.

Sunday, October 16, 2022
 
pnuts
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages