CODEWITHSUNDEEP
cakephp-2.0
Robert Bowen
Robert Bowen

Reputation: 487

CakePhp 2.0 sort on associated model using Pagination / Paginator

I realize this question is literally all over StackOverflow, but none of the answers seem to work for me.

My problem is dirt-simple. I just want to list Companies ordered by City name:

class Company extends AppModel
 public $recursive = 2;

 public $belongsTo = array(
 'City' => array(
  'className'    => 'City',
   'foreignKey'   => 'city_id'
  )
 );

class City extends AppModel {
 public $useTable = 'cities';

 public $belongsTo = 'Country';
 public $hasMany = array(
  'Company' => array(
    'className'    => 'Company',
    'foreignKey'   => 'city_id'
 )
);

On the Companies page, and thus from within the Companies Controller, I would like to do something like this:

$this->paginate = array(
 'order' => array('Company.City.name' => 'ASC' ),
 // or is it 'order' => array('Company->City.name' => 'ASC' ), ??
);
$companies = $this->paginate('Company');

And in the View, have a link to change the ordering to be by City name:

<?php echo $this->Html->link(__('Sort by City name'), array('controller' => 'companies', 'action' => 'index', '?' => array('sort' => 'Company.City.name'))); ?>

When I make a link to change the ordering to be by a field that is within the Company Model, say name (of the Company), I make the link like this:

<?php echo $this->Html->link(__('Sort by Company name'), array('controller' => 'companies', 'action' => 'index', '?' => array('sort' => 'Company.name'))); ?>

... it works fine. And all the Pagination links, like first, next, numbers, etc., also work, and create links like this:

localhost/companies/20/sort:Company.name/page:2

So how in the world can I do this for Company.City.name (and while I'm at it, Company.City.Country.name!) Currently it seems to be ignoring my 'sort' parameter. If I turn debug on, the generated SQL queries have NO 'order by' clause when I try to order by Company.City.name.

This is driving me nuts and it seems a simple enough thing to be able to do. Much help much appreciated!

UPDATE: Hey! That worked! Or at least, I am half-way there! I was able to use your suggestion in my Controller:

$this->paginate = array(
  'limit' => 10,
  'order' => array('City.name' => 'ASC' )
);

And it works! Now I am trying to do the same thing for Country and it does NOT work. I've tried:

$this->paginate = array(
      'limit' => 10,
      'order' => array('City.Country.name' => 'ASC' )
    );

$this->paginate = array(
      'limit' => 10,
      'order' => array('City->Country.name' => 'ASC' )
    );

And no love. It ignores my 'order' parameter. Any ideas on how I can do this?

Many thanks again for your helpful answer.

Upvotes: 2

Views: 4723

Answers (1)

Arun Jain
Arun Jain

Reputation: 5464

If you defined a model association ship, then you don't need to Use Company.City.name etc. You can directly use City.name.

And if you are showing a list then you can use the following code instead of giving a link on which you want to sort your table data.

<?php echo $this->Paginator->sort('City.name', 'City Name');?>

Kindly ask if it not worked for you.

Upvotes: 3

Related Questions