CakePHP: find neighbors, order on 'name' or 'order'

Question

Hi,

I have a list of ordered items, ordered according to the int field order. I'm creating a gallery in CakePHP 1.2 that has a prev and next button and those should link to the previous and next item according to their ordering, not according to their id.

In order to get this result I've included the 'order' parameter to the find function, and populated it with 'Item.order'=>'DESC'. Still the result is an id ordered list.

My question is: what do I do wrong? My controller:

$this->Item->id = 16;

$neighbours = $this->Item->find('neighbors', array('order'=>array('Item.order'=>'DESC'), 'fields'=>array('id','name')));

Solution I've tried a different approach. My code now does the job and looks as follows:

$order = $this->Item->findById(6);

$neighbours = $this->Item->find('neighbors', array('field'=>'order', 'value'=>$order['Item']['order']));

By setting the parameter 'field' to the field will be the ordering field, and set the 'value' parameter to the order value of you current Item you'll get the prev and next.

Answer 1

Nice solution:)

Answer 2

Somehow the code below is not working for me .. it works fine for the first photo in the album .. however when i open any other photo i get the following error ...

Notice (8): Undefined offset: 0 [CORE/cake/libs/model/model.php, line 2235]

Code snippet:

$photo = $this->Photo->findById($id); $conditions = array('Photo.album_id'=>$photo["Photo"]["album_id"]); $neighbors = $this->Photo->find('neighbors',array('field'=>'position','value'=>$photo["Photo"]["position"],'conditions'=>$conditions,'fields'=>array('Photo.id','Photo.album_id')));

What is it that am doing wrong ???

Answer 3

Got the problem ..

I have to specify Photo.position in the fields list .. because if you look at the sql query generated .. cakephp executes 2 queries to get the neighbors .. first to get the prev link .. and then to the next link .. hence for the second query it will the position column value as well ..

1st SQL Query .. SELECT Photo.id, Photo.album_id, Photo.position FROM photos AS Photo LEFT JOIN albums AS Album ON (Photo.album_id = Album.id) WHERE Photo.album_id = 62 AND Photo.position < 2 ORDER BY Photo.position DESC LIMIT 1

2nd query SELECT Photo.id, Photo.album_id, Photo.position FROM photos AS Photo LEFT JOIN albums AS Album ON (Photo.album_id = Album.id) WHERE Photo.album_id = 62 AND Photo.position >= 1 AND Photo.position != 2 ORDER BY Photo.position ASC LIMIT 2