Recently I got asked how you could paginate data from a HABTM (hasAndBelongsToMany) relationship. As the answer could be interesting for others, too, I will show a potential solution here.
Let’s say you have the two models Category and Product, and you want to paginate the Products of a certain Category. Your table definition looks like:
create table categories (
id int(11) not null auto_increment,
name varchar(255) not null,
primary key (id)
);
create table products (
id int(11) not null auto_increment,
name varchar(255) not null,
primary key (id)
);
create table categories_products (
category_id int(11) not null,
product_id int(11) not null
);
And the models are:
// app/models/category.php
class Category extends AppModel {
public $hasAndBelongsToMany = array('Product');
}
respectively
// app/models/product.php
class Product extends AppModel {
public $hasManyAndBelongsToMany = array('Category');
}
Before we move on, let’s add some test data to the tables:
insert into categories (id, name) values (1, 'PHP');
insert into categories (id, name) values (2, 'Framework');
insert into products (id, name) values (1, 'CakePHP');
insert into products (id, name) values (2, 'Zend');
insert into products (id, name) values (3, 'Symfony');
insert into products (id, name) values (4, 'Rails');
insert into products (id, name) values (5, 'Django');
insert into categories_products (category_id, product_id) values (1, 1);
insert into categories_products (category_id, product_id) values (1, 2);
insert into categories_products (category_id, product_id) values (1, 3);
insert into categories_products (category_id, product_id) values (2, 1);
insert into categories_products (category_id, product_id) values (2, 2);
insert into categories_products (category_id, product_id) values (2, 3);
insert into categories_products (category_id, product_id) values (2, 4);
insert into categories_products (category_id, product_id) values (2, 5);
Now we want to paginate all products in the category “PHP”. For this purpose we write the following code:
// app/controllers/categories_controller.php
class CategoriesController extends AppController {
public $paginate = array('limit' => 2);
public function index() {
$data = $this->paginate('Category', array('Category.id' => 1));
debug($data);
exit;
}
}
The output of this code is:
Array
(
[0] => Array
(
[Category] => Array
(
[id] => 1
[name] => PHP
)
[Product] => Array
(
[0] => Array
(
[id] => 1
[name] => CakePHP
)
[1] => Array
(
[id] => 2
[name] => Zend
)
[2] => Array
(
[id] => 3
[name] => Symfony
)
)
)
)
Obviously the wrong approach as we get three products instead of two. Hm, maybe we have to use the Product model in the paginate statement. Let’s try it:
// app/controllers/categories_controller.php
class CategoriesController extends AppController {
public $paginate = array('limit' => 2);
public function index() {
$data = $this->paginate('Product', array('Category.id' => 1));
debug($data);
exit;
}
}
This causes the following error:
SQL Error: 1054: Unknown column 'Category.id' in 'where clause'
Hm. Couldn’t we use somehow the mapping table required for our HABTM association? Sure, we can create a model for it:
// app/model/categories_product.php
class CategoriesProduct extends AppModel {
public $belongsTo = array('Category', 'Product');
}
And use this model in the paginate statement:
// app/controllers/categories_controller.php
class CategoriesController extends AppController {
public $paginate = array('limit' => 2);
public function index() {
$data = $this->paginate('CategoriesProduct', array('Category.id' => 1));
debug($data);
exit;
}
}
This time we get the expected result (at least up to RC4 of CakePHP 1.2):
Array
(
[0] => Array
(
[CategoriesProduct] => Array
(
[category_id] => 1
[product_id] => 1
)
[Category] => Array
(
[id] => 1
[name] => PHP
)
[Product] => Array
(
[id] => 1
[name] => CakePHP
)
)
[1] => Array
(
[CategoriesProduct] => Array
(
[category_id] => 1
[product_id] => 2
)
[Category] => Array
(
[id] => 1
[name] => PHP
)
[Product] => Array
(
[id] => 2
[name] => Zend
)
)
)
However, in CakePHP 1.2.1.8004 the previous solution no longer works. The CategoriesProduct model is no longer needed. And by following the tutorial about ad-hoc joins we have to change the CategoriesController to:
// app/controllers/categories_controller.php
class CategoriesController extends AppController {
public $paginate = array('Product' => array('limit' => 2, 'joins' => array(
array(
'table' => 'categories_products',
'alias' => 'CategoriesProduct',
'type' => 'inner',
'conditions'=> array('CategoriesProduct.product_id = Product.id')
),
array(
'table' => 'categories',
'alias' => 'Category',
'type' => 'inner',
'conditions'=> array(
'Category.id = CategoriesProduct.category_id',
'Category.id' => 1
)
))));
public function index() {
$data = $this->paginate('Product');
debug($data);
exit;
}
}
With those changes the debug output should now look like:
Array
(
[0] => Array
(
[Product] => Array
(
[id] => 1
[name] => CakePHP
)
[Category] => Array
(
[0] => Array
(
[id] => 1
[name] => PHP
)
[1] => Array
(
[id] => 2
[name] => Framework
)
)
)
[1] => Array
(
[Product] => Array
(
[id] => 2
[name] => Zend
)
[Category] => Array
(
[0] => Array
(
[id] => 1
[name] => PHP
)
[1] => Array
(
[id] => 2
[name] => Framework
)
)
)
)
If there is a better solution, please let me know ;-)
Update 2009-02-01: Adding solution for CakePHP 1.2.1.8004.
Man… I can’t thank you enough for the help. I was not sure if I could make a model for the Join table. This helps a ton. Back to bakin….
Thank you so much… that is exactly what I need for my tags. You are great.
Now if you could make this happen for Cake 1.1, that would be peachy keen.
I think you can still solve this using:
$this->paginate(‘Category’, array(‘Category.id’ => 1));
ALSO using a jointable model AND the “with” key supported by CakePHP 1.2 used in HABTM assocations.
Never mind the article, check out any posts mentioning the “with” key.
http://www.thinkingphp.org/2006/10/26/modeling-relationships-in-cakephp-faking-rails-throughassociation/
It comes down to providing the jointable model in the with key for the Category:
var $hasAndBelongsToMany = array(
‘Product’ => array(
‘with’ => ‘CategoriesProduct’,
‘className’ => ‘Product’…
Every Category record returned by any find or a findAll will contain ALL products for THAT Category record, provided that $this->Category->recursive equals 1 or more.
I have not tested it myself if it works for this specific case (with pagination and all), but I think it should work as I have used the with key in my own project. While it’s more Cakelike to use this (sometimes very handy!) feature, your more basic solution works as well for this case, and is probably less confusing to people just starting with CakePHP.
Okay wait, I guess the “with” approach will not work in THIS case because it will limit the number of categories instead of products, but it can be very usefull in other situations obviously. (Maybe paginating lists of posts along with their own tags?)
You don’t need to modelize the join table, you can use CakePHP’s built in one: Modelizing HABTM join tables in CakePHP 1.2: with and auto-with models
@all: Thanks for your comments!
@Daniel, snowdog: I am glad it helps you guys!
@airrob: I have to disappoint you, I don’t know how to do that with CakePHP 1.1.x.x.
@Tim: I tried it with “with”, but without luck…
@Mariano: I tried the approach shown in your article. It works fine with findAll combined with bindModel. But for some reason bindModel does not affect paginate, and so I get only data from CategoriesProduct in my result set.
@cakebaker: yeah, that’s probably because paginate() will issue a findCount first, which would then reset the dynamic models before it does the actual findAll.
@cakebaker
I encountered the same issue of findCount() resetting associations with Controller::paginate() and I found that setting the 2nd param of (un)?bindModel() to false persisted the associations and didn’t reset them.
Model::bindModel($params, $reset = true);
@ Mariano:
IMO, Model::findCount() should be refractored because;
1. The reason mentioned herein. Since you are not returning associative data recursively with findCount(), resetting the associations after it’s execution doesn’t make sense.
2. findCount() sends $results to the afterFind() callback in both the Model and each Behavior. I don’t think I have ever come into a situation where I need to modify the results from a findCount() :)
[...] cakebaker » Pagination of data from a HABTM relationship The trick is making a model for the join table (tags: cakephp habtm pagination) Posted by Richard@Home Filed in 15 [...]
@Brandon: you could define your own method to do the count in the model, which receives two parameters: ($conditions, $recursive). Inside that method you could do your bindModel() right after you have done your own findCount. Call that method paginateCount and thats it:
Model {
function paginateCount($conditons, $recursive) {
$count = $this->findCount($conditions, $recursive);
// My bindModels here, before the findAll()
// paginate executes
return $count;
}
}
@Mariano, Brandon: Thanks for your comments!
@Brandon: Yes, with the second param set to false it works fine (and you can then omit the CategoriesProduct model)
I don’t know about the paginateCount function. It feels like it was put in as a workaround for pagination issues, so I don’t really like it. I think it would take a lot of people (such as myself) a long time before they really know how to use it.
Suppose you’re a beginner and you need to overcome this HABTM paginationproblem.
By the time you have the solution, you’ll know how bindModel works, that findCount is run before pagination, that this call resets the assocations, that you can override the paginateCount method (I know it by digging in the sourcecode for an hour or so, and finding some snippet by phpnut), how to override this paginateCount method etc etc…
That’s just too much if all you want is pagination where a HABTM relation is involved.
@Mariano, I have read your HABTM auto with article but for some reason it does not modelize my jointable automatically. Is the automodelizing feature a recent thing? (I’m using the 1.2 July release from the site)
I agree with Tim that the Model::paginateCount() seems like nothing more than a bandaid that just adds another layer of configuration to make the default functionality of Controller::paginate() work as it should.
Why not just take the time and refractor Model::findCount() to correct these issues?
Also, I think that bringing “pagination” functionality into the Model is bad practice since pagination is Controller specific functionality.
I have 98 more cents to go on this topic :)
@Tim: mm strange I can’t remember when the auto model was added but always try to keep your local 1.2 branch UP TO DATE. For example if you are running on 1.2 alpha from july you are looking at a completely diff 1.2 release in regards to core.php and other issues :) Try updating the branch and test those with models again, they should be working out of the box for hasAndBelongsToMany bindings
@Brandon, yeah, never looked at it that way, but I think you are right that any paginationstuff does not belong in a Model for that reason.
However, I don’t think that findCount should be refactored. Because it is using findAll (like any other non-custom query) it’s functionality can be altered by using callbacks. Even though afterFind is not really usefull, beforeFind might be, since you can add/alter conditions.
I think it is up to the pagination functionality itself to make sure everything works as expected. Maybe the developer should be able to tell how the results should be counted in one (easy) way or another when paginating. For example, count either the number of unique product id’s, or the number of unique category id’s when paginating HABTM…which is actually possible without using findCount or even hitting the database. Also, when the the database is not hit by using findCount, the associations would not be reset…just a thought.
please describe properly
@aditya: What is unclear?
[...] tempdocs.cakephp.org – Pagination http://www.littlehart.net – CakePHP Pagination With A HABTM Relationship cakebaker.42dh.com – Pagination of data from a HABTM relationship [...]
@cakebaker
But what if you have now a Model named ‘Theme’ like in your Categories Model :
var $belongsTo = array (‘Theme’ => array(
‘className’ => ‘Theme’,
‘foreignKey’=>’theme_id’)
);
A ‘theme’ can be for instance “web programming” or “football”, it is just for the example.
And now how do you retreive the datas of the Theme model with your method ?
$data = $this->paginate(‘CategoriesProduct’, array(‘Category.id’ => 1));
debug($data);
$data will never return any datas from theme ?
[apology]I have a very bad english accent, I know that…[/apology]
@Fred: Hm, I’m not sure I understand you correctly. But if you have a “Theme” model instead of the “Category” model I used, you simply have to replace “Category” respectively “Categories” with “Theme” and “Themes”.
Hope that helps!
If not, please describe the scenario you try to implement in more detail (you can also send me an email).
@cakebaker
There is a misunderstanding. The problem is not to change the name Category for Theme.
Keep the structure you gave in example and add a new model named Theme with the $belongsTo I gave.
In your example, a category can be ‘php’ or ‘framework’ or ‘ruby’, ok ?
Now, I add a Theme model where a theme can be ‘web programming’ or ‘football’, because you can also have categories depending to the theme ‘football”.
And if I try to paginate my categories by theme and do a debug($data), I don’t have any results of Theme in my array.
@Fred: Thanks for the clarification!
At the moment I don’t see a way to get the Theme data directly. A workaround could be to extract the theme_id from the result, and then to get the theme data with an extra query.
Hope that helps.
Hello Cakebaker,
For all who wish to be interested to have pagination of data(s) from a HATBM, you can also read this excellent article (in french) from :
http://www.formation-cakephp.com/30/pagination-avec-criteres-de-filtrage-complexes
This method allows to combine multi-params from a post (or anything you want), and you can have pagination with date, or with date + authors, or date + authors + books, etc.
In fact, I used their method, whitch get $this->param['named'] in the URL, and combine it with a personnal “cooking” method (PCM) to get results if a model is like I described in my previous post.
This “PCM” can be performed better with time, because I’m not enought cakemaker to know if there is a better way now. I get the results of Theme data (remember my previous post) in the view by using requestAction method.
I’am sorry if my english is not very clear ; hope you can understand anyway :)
@Fred: Thanks for the link (even though I don’t understand everything there as I’m still a French beginner)!
It’s good to hear you found a solution for your problem :)
Anyway, I’m a new to CakePHP and this helped me a lot. Thanks for the article.
@Keerthi: I’m glad this article was useful for you!
wow.. it great.. thanks for articles :D
Great article. I had been struggling with this exact problem, with categories > products even :)
I did run into an issue on the view side though. Since category and product each have the ‘name’ field I had to be sure to set the paginator->sort() to specifically use the correct field ( Product.name ) but even so this column will not automatically switch between ASC and DESC.
@Corie: Hm, what do you mean with “will not automatically switch between ASC and DESC”? It seems I’m slow on the uptake ;-)
@cakebaker: In your view if you sort by a field like Model.field the helper will always set the direction to ASC.
I found a workaround here: http://bakery.cakephp.org/articles/view/advanced-pagination-1-2 (comment #7) [...] as a workaround I copied paginator.php to my app/views/helpers directory and changed the line
return preg_replace(‘/.*\./’, ”, key($options['order']));
in the function sortKey to
return preg_replace( sprintf( ‘/%s\./’, $this->defaultModel()), ”, key($options['order'])); [...]
This does seem to fix the issue.
@Corie: Ah, thanks for the explanation, now I got it :) And I’m glad you found a fix for the issue!
How are you guys keeping the category id set while you navigate through the paged data? In my example, I have Categories and Items. I want to go to /categories/browse/1 and show all items that belong to category_id 1. This works fine using the example above, but when I click on any of the pagination links (i.e: “prev” or “next”), it doesn’t pass along the category id. So, I’m curious as to how you guys have dealt with this? I am just going in the wrong direction with my thought on how I should browse and display my items within a category?
Also, is it possible to not retrieve the category model with every single product?
Ok, so I was able to figure out the problem where the category_id wasn’t being passed from page to page. The solution I came up with was to use the $options['url'] parameter of the $paginator->prev, $paginator->next and $paginator->numbers methods. I used the following code to make sure it kept passing any parameters I might set in the future as well:
echo $paginator->next(‘next >>’, array(‘url’=>array(implode(‘/’,$this->params['pass']))), null, array(‘class’=>’disabled’));
The same $options array containing the url must also be passed to the $paginator->sort method:
$paginator->sort(‘Name’, ‘Item.name’, array(‘url’=>array(implode(‘/’,$this->params['pass']))));
And keep in mind that you still need Corie’s code above to fix the “stuck sorting ASC” issue.
Hope this helped someone new to Cake like myself!
Currently, for every single product returned, we are getting the CategoriesProduct, Category and Product models… I’m still trying to figure out how to remove the Category model (and ideally the CategoriesProduct model) from the results that are returned. Anyone have any ideas? I’ve tried unbinding the models every which way but that doesn’t seem to be working.
@Doug: Thanks for your comments! I’m not sure whether it really works in your situation, but you might try the bindable behavior to remove the Category model from the results.
This is easy with Cake 1.1 ! Pass on the knowledge.
First off you need the super slick pagination helper and component. Search the bakery, its there. There is no author in the comments so all I can say is search ‘Pagination Helper’ and make sure it has Ajax and good reviews.
Once you get that working with basic models (easy to do) than follow these steps;
Lets say I’m viewing the category view page, an dI want all products with that category id.
In the Controller (Category)
action: ‘View’
After setting ‘category’ to the proper query, set ‘products’ like below
$criteria=null;
list($order,$limit,$page) = $this->Pagination->init($criteria); // Added
$criteria=’`ProductsCategory`.`category_id`=’.$id;
$data = $this->ProductsCategory->findAll($criteria, null, $order, $limit, $page); // Extra parameters added
$this->set(‘products’, $data);
In the view you’ll now have your regular ‘$category’ variable, and a $products variable. Use the $products to show only corresponding results.
<div id=”pagination”>
<?php
if (!empty($products)):
$pagination->setPaging($paging); // Initialize the pagination variables
/*
*Create form to sort results
*/
echo $ajax->form(NULL,NULL,array(“update” => $pagination->_pageDetails['ajaxDivUpdate'],”id”=>’paginationForm’));
echo $pagination->resultsPerPageSelect().” “;
$sorts = Array (
“id::ASC::Product”,
“id::DESC::Product”,
“name::ASC::Product”,
“name::DESC::Product”,
“name::ASC::Company”,
“name::DESC::Company”
);
echo $pagination->sortBySelect($sorts);
echo $ajax->submit(“Submit”,array(“update” => $pagination->_pageDetails['ajaxDivUpdate'],”id”=>’paginationSubmit’));
echo $ajax->observeForm(‘paginationForm’,array(“frequency”=>1,”update” => $pagination->_pageDetails['ajaxDivUpdate']));
echo “<script type=\”text/javascript\”>document.getElementById(‘paginationSubmit’).hide();</script>”;
foreach ($products as $output)
{
$image=$html->image(‘uploads/’.$output['Product']['imageurl'],array(‘width’=>’120′,’alt’=>’Product Image’));
}//end for each
I have it working with no issues, regards!
@Edward: Thanks for your example! Hopefully it will help some cake 1.1 users.
Thanks for the example but how do you extend it with another HABTM relation ??
Imagine that users can subscribe to categories
So: Product HABTM Category HABTM User
If we would like to paginate through the products in the users categories how can this be done ???
The sql would be like
“select products.* from products,categories_products as cp,users_categories as uc WHERE products.id=cp.product_id AND cp.category_id=uc.category_id AND uc.user_id=$User[id]”
But how would one define the model ???
@equanimous: Hm, good question. I would define the methods paginate() and paginateCount() in your model and then use Model::query() to perform the SQL from above (see also the chapter about pagination in the cookbook). But I’m not sure whether this is the best way to do it, so I would also ask in the google group, maybe someone there knows a better approach.
Hope that helps!
With this code:
class CategoriesController extends AppController {
var $paginate = array(‘limit’ => 2);
function index() {
$data = $this->paginate(‘CategoriesProduct’, array(‘Category.id’ => 1));
debug($data);
exit;
}
}
If product hasOne Country, how do i get also the information of Country.name?
Any help is welcome, thanks in advance.
Im using 1.2
@josoroma: With setting the “recursive” option to “3″ you should also get the Country.name:
To reduce the amount of returned data, you might have a look at the Containable behavior.
Hope that helps!
In my case, inside Listingcategories i need to display the listings of a given category $id. Then im paginating from the join table ListingcategoriesListing, Iwas playing with unbind, bind, containable behavior, and im still trying.
I just only need to display the listings of a given category $id, but listings belongsTo a Country, and im stuck trying to get not just only the listing information also its Country.name
Thanks in advance, any help is welcome.
This is what im doing:
paginate = array(
‘fields’ => array(
‘Listing.id’,
‘Listing.user_id’,
‘Listing.name’,
‘Listing.description’,
‘Listing.created’,
‘Country.name’
),
‘order’ => array(
‘Listing.created’ => ‘DESC’
)
);
$listings = $this->paginate(‘ListingcategoriesListing’, array(‘Listingcategory.id’ => $id) );
}
Yesterday the text post was not complete, now im pasting the code at bin.cakephp.org
I have a habtm association:
Listings Listingcategories
The join table is listingcategories_listing.
From the Listingcategories controller I get the listings of a given category id without problem, using the following code:
http://bin.cakephp.org/saved/36634
Now i also need to get the Country.name associated to listing, where each Listing belongsTo a Country.
I was playing with recursivity, bindable behavior, but i cant get the country name.
Thanks in advance.
@josoroma: Hm, I think it is not possible to use Country.name in the fields array due to the way the SQL statements are created by Cake. If you don’t specify the fields, you should get the country names in the result.
Hope that helps!
Hello,
i think it’s more easy to use sort functions on array for sorting HABTM relations views, as this example:
in my view:
foreach ($etapes as $key => $etape) {
$voiture[$key] = $etape['Voiture'][0]['name'];
$date[$key] = $etape['Etape']['enddate'];
}
array_multisort($voiture, SORT_ASC, $date, SORT_ASC, $etapes);
i got two tables etapes and voitures joined by another table etapes_voitures.
@lijko: I’m not sure whether your approach is easier ;-) I think it is a matter of preference, personally I prefer to retrieve the data in the correct order from the database, if possible.
@cakebaker :
for me, (and may be only for me ;-), the order process is in view part (in an MVC architecture, the model doesn’t care about order, it’s just a human behavior), but it’s true that’s a matter of preference.
@lijko: I disagree ;-) The model is the interface to your data and so it has to return the data in the form the clients want the data. If a client wants the data ordered, it has to provide the ordered data. For example, it doesn’t make much sense to send 100′000 product records to the view so that the view can sort them and find the ten newest products to display…
Wow – huge time saver for me too. Thanks so much for posting this!
Hi, I have a problem to pass parameters with URL in paging….
my view code is:
And the action search:
Any help please……I´m new in cakephp and have a big project to terminate soon.
Thanks a lot!!!
@Nelli: Hm, what exactly is the problem? Any error messages?
Any Idea why this would break in the final release of cake. We had it working great in rc4 but when we upgraded it ignores the relationships of the model and throws the following error:
Unknown column ‘Listing.city_id’ in ‘where clause’
any idea of why this would break? thanks in advance.
Hi There,
I’ve been using this technique successfully on a project in development and it’s been great. Unfortunately, today I upgraded from RC3 to the most recent stable version (1.2.1.8004) and this trick has stopped working. Cake doesn’t seem to pickup the relationships anymore and the result is the SQL queries don’t work anymore. I have two Models, Listings and Picks and I get the following SQL that doesn’t work as it’s not joining the Listings or Picks tables anymore:
SELECT COUNT(*) AS `count` FROM `listings_picks` AS `ListingsPick` WHERE `Listing`.`city_id` = ‘1′ AND `pick_id` = 1 AND `Listing`.`pending` = 0
Any thoughts or suggestions on this would be greatly appreciated. Thanks in advance.
It’s not working for me either…
I have the same SQL error has Snowrider & aaron:
SQL Error: 1054: Unknown column ‘Inbox.id’ in ‘where clause’
And when I use the mapping table index directly for the pagination like below, then I get my list properly paginated, but it seems impossible to get the related objects included in my list (in my case Inbox and Message):
$data = $this->paginate(‘InboxesMessage’, array(‘InboxesMessage.inbox_id’=>$id));
results:
Array
(
[0] => Array
(
[InboxesMessage] => Array
(
[id] => 1
[inbox_id] => 1
[message_id] => 1
[is_read] => 0
[is_archived] => 0
)
)
[1] => Array
(
[InboxesMessage] => Array
(
[id] => 3
[inbox_id] => 1
[message_id] => 2
[is_read] => 0
[is_archived] => 0
)
)
…
Here is the solution that worked for me:
In my InboxesController:
In my Message Model I had to override the paginate method to do the following binding:
All this is retrieving the wished paginated structure for me:
The last issue for me was then to map in this list the Outbox with it’s related User, so a 3rd level of recursivity.
As it appears, contain doesn’t work for paginate after the 2nd level of contain.
The line below in my InboxController was unable to include the Users in my list. (this kind of contain is working fine in a normal Model->find(‘all’, …) statement…
So I simply updated the resulting list of the paginate to manually include the 3rd level of objects in the paginated list ($data):
Any suggestion on how to make this nicer is very welcome. Obviously, Cakephp has changed its way of handling pagination overtime. I was initialy using the 1.2 RC3, and updated today to 1.2.1.8004, but it didn’t change anything. The code included in this article was not working for me.
Hope this helps…
@aaron, Snowrider, Julien: Thanks for your hints! I updated this article with a solution which works with the current CakePHP version.
Hope it is helpful for you!
I don’t quiet understand why, but this seems to fix the issue with 1.2.1.8004. I just hope there aren’t any side-effects.
I made $reset (second parameter for bindModel) to false, and it worked.
Instead of
$this->Book->bindModel(array('hasOne' => array('BooksCategory')));I used
$this->Book->bindModel(array('hasOne' => array('BooksCategory')),false);The API says ‘If $reset is false, association will not be reset to the originals defined in the model’ and I don’t quiet understand the repercussions of that. If I shouldn’t be doing this, please do let me know. :-)
Right, I just used my brain. That shouldn’t matter – I can always unbind…
@Palaniappan: No, it shouldn’t matter :)
@cakebaker: does making $reset=false work for you?
@Palaniappan: Yes, $reset = false seems to work here.
Man, thanks! This stuff saved my life :)
@kaizeris: I’m glad this article was helpful for you :)
Thanks – once again you’ve saved me a lot of pain, yesterday I was stuck in a terrible rut but this morning I’ve just read your article started fresh and done it.
I’ve just dived back into a lot of cake development after basically a 6 month gap and I’m loving it (1.2. is very cool) But the thing that does my head in consistently is querying across HABTM and couple that with pagination… .
It really shouldn’t be so hard, I guess these odd quirks are the price we pay for all the magic everywhere else.
Cheers
@John: I’m glad to hear this article was useful for you.
Yes, I agree with you, it is too complicated to accomplish such things. Hopefully, the cake devs will improve this in future releases (and also reduce the magic stuff)…
Great article and this explains a lot, however, I seem to be stumped on knowing how to take the paginated CategoriesProduct and attach to the actual Products in the function view()
I use your examples and add to my app. When I view one of the Categories this lists ALL the products, but with no pagination.
The $paginator->counter() shows all the correct information (1 of 8) and the $paginator->prev(), $paginator->numbers() and $paginator->next() are active and showing the correct info, but the actual Products are not paginated.
I’m sure I’ve missed something real simple but for the life of me I can’t figure it out!
Please can you shed some light on this?
Many thanks.
@mike: Can you paste the relevant code to http://bin.cakephp.org/ (or a similar service)? It’s really difficult to say what the problem could be without seeing the code ;-)
Is there a way to do:
Like:
But in a way that actually works.
http://bin.cakephp.org/view/1652975370
I’ve included;
View
Controllers
Models
$books is only holding the data for the join table and is not grabbing the related data. I’ve just tried setting the var $paginate to include ‘recursive’=>3 but this has made no difference.
Thanks.
@Michelle: If you add a “group by” your example should work:
Hope that helps!
@mike: Which CakePHP version do you use (see cake/config/config.php)? If you use the current stable release (1.2.1.8004), then you have to use the more complicated approach I described at the end of the article.
Hope that helps!
Hi Daniel,
Okay, so I removed all my old code, starting from just the basic Baked model and controller and removing all code from under the function view() in the controller. I then added your code under the “However, in CakePHP 1.2.1.8004 the previous….” section exactly as is, just changing to my own models.
Now the results are paginated.
Alas things are still not working properly. The results don’t change when I am viewing a different category, if I change;
‘Category.id’ => 1
to 2, then the results reflect the Posts that are in category 2.
I’m guessing I would need to pass the current Category Id but I’m not sure how to do this. Can you help?
Many thanks.
Thanks alot. Just what i needed.
@mike: There are in principle two ways to accomplish that.
You could simple set the $paginate instance variable with:
Or you could modify the respective array value directly:
Hope that helps!
@kyle: You are welcome!
Ah! all things Cake are definitely becoming clearer :)
So, I moved all array definitions from public $paginate = array(…) into the function view() itself – I did try the second option, passing the $id, but this did not work for me – and changing the ‘Category.id’ => 1 to use $id. This now provides the correct results for each different Category.
Perhaps it’s worth noting that the prev/next links will need to be changed as they don’t include the Category id by default. I just added $this->params[...] to the links like so;
next(__(‘next’, true).’ >>’, array(‘url’ =>$this->params['pass']), null, array(‘class’=>’disabled’));?>
Paging through the results now works correctly.
Many thanks for your help Daniel, very much appreciated.
@mike: You are welcome!
I wanted to chime in here to say that I found this post and the resulting discussion in the comments useful. In the particular project I’m finishing up now, I have a Product model that has a HABTM relationship with both Suppliers and Locations, and my product search page, using the default controller pagination, was simply not working. I eventually realized that I needed to define paginateCount() and paginate() methods in my Product model, adding the necessary “group” clause that the default pagination doesn’t respect.
That is, I had this in my controller:
But it wasn’t working. Instead I had to do this in models/product.php:
Note that this is almost the exact same code as in Controller::paginate() (starting around line 1077) but just with the addition of the group parameter being specified manually. I don’t know why the group parameter wasn’t making it all the way through Controller::paginate(), but this worked for me and I’m posting here in case it helps somebody else.
@matthew: Thanks for sharing!
Awesome tutotrial. Can’t be more simple.
Great thanks for this, helping me a lot making my first search HABTM Search system in Cake.
I really love your website!
@Jean-Philippe: You are welcome!
[...] on HABTM relations such as the one between the Tag and Story models. After some research, however, I was able to find a solution to the problem. I don’t in any way understand how this works, all I know is that it does. Add to this a list [...]
I can’t thank you enough for this little tutorial. I’ve searched for hours for a suitable and relatively neat solution. Thank you!
@KillerSponge: I’m glad to hear this article was helpful for you :)
Thank you very much for this!! It was very helpful :-)
@Regis: You are welcome!