I am having troubles to sorting the result data of table in a view of my cakephp system. I can show all the result data of table, but I can't get to sorting that.
I was able to create the Paginator link to sort by order direction asc and desc, but when a click it, the order stays the same, although the url link and the arrow figure change the direction, asc and desc.
In my system I have three tables related as follow:
My php Models are look like as follow:
Disc.php:
class Disc extends AppModel {
public $name = 'Disc';
public $hasMany = array(
'Audio' => array(
'className' => 'Audio',
'foreignKey' => 'Disc_id',
'order' => 'Audio.id DESC'
)
);
Audio.php:
class Audio extends AppModel {
public $name = 'Audio';
public $belongsTo = array(
'Disc' => array(
'className' => 'Disc',
'foreignKey' => 'Disc_id'
)
);
public $hasMany = array(
'Moment' => array(
'className' => 'Moment',
'foreignKey' => 'audio_id'
)
);
Moment.php:
class Moment extends AppModel {
public $name = 'Moment';
public $belongsTo = array(
'Audio' => array(
'className' => 'Audio',
'foreignKey' => 'audio_id'
)
);
My php Controllers are look like as follow:
DiscsController:
class DiscsController extends AppController {
...
public function view($id = null) {
$this->Disc->recursive = 2; # or -1, 0, 1, 2, 3
$this->Disc->id = $id;
$this->set('disc', $this->Disc->read());
//$this->set('audio', $this->paginate());
$conditions = array("Audio.disc_id" => "$id");
$this->set('audio', $this->paginate('Audio', $conditions));
// note "plan" in the first argument of set (this is required to pass the results back to the view). Also.
//The $condition is set to return ONLY plans that match the plan_detail_id of id (on the plan_details table).
}
...
AudiosController:
class AudiosController extends AppController {
...
public function view($id = null) {
$this->Audio->id = $id;
$this->set('audio', $this->Audio->read());
$this->set('audios', $this->paginate());
}
...
And my Discs/view.ctp is look like as follow:
<table>
<!-- Sort -->
<tr>
<th><?php echo $this->Paginator->sort('id', 'DID'); ?></th>
<th><?php echo $this->Paginator->sort('audio.id', 'AID'); ?></th>
<th><?php echo $this->Paginator->sort('Audio.name', 'Audio Name'); ?></th>
<th><?php echo $this->Paginator->sort('moment.played','Audio Played') ;?></th>
</tr>
<!-- foreach ($disc['Audio'] as $audio): -->
<?php foreach ($disc['Audio'] as $audio): ?>
<?php foreach ($audio['Moment'] as $moment): ?>
<tr>
<td><?php echo $audio['Disc']['id']; ?></td>
<td><?php echo $audio['id']; ?></td>
<td><?php echo $audio['name']; ?></td>
<td><?php echo $moment['played']; ?></td>
</tr>
<?php endforeach; ?>
<?php endforeach; ?>
The url params when I click to sort are:
DID:
http://localhost/cakephp/discs/view/3/sort:id/direction:asc
AID:
http://localhost/cakephp/discs/view/3/sort:audio.id/direction:asc
Audio Name:
http://localhost/cakephp/discs/view/3/sort:Audio.name/direction:asc
Audio Played:
http://localhost/cakephp/discs/view/3/sort:moment.played/direction:asc
Maybe it is something simple, I am new at cakephp, but I searched a lot for a solution, but no successful.
Please, help me.
Thank you in advance.
EDIT 2:
Thank you Anubhav, I was out for some days, but now I'm back.
Sorry but I didn't understand very well what you asked. Please check if was it you asked.
I've set Configure::write('debug', 3) in app/config/core.php.
So, I tried to use print_r array('order' => "$sortBy.$order"), but I faced this error: syntax error, unexpected T_ARRAY.
Then, I used Debugger::dump(array('order' => "$sortBy.$order")); and when I accessed http://localhost/cakephp/discs/view/3/sort:id/direction:asc it returned this:
array(
'order' => 'Audio.id.DESC'
)
When I access http://localhost/cakephp/discs/view/3/sort:id/direction:asc
I don't face any database error, but the paginator still doesn't run.
But when I access just the http://localhost/cakephp/discs/view/3
I get that Database Erro I said before: Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Audio.id.DESC' in 'order clause'
And the SQL Query is:
SQL Query: SELECT `Audio`.`id`, `Audio`.`disc_id`, `Audio`.`name`, `Audio`.`downloaded`, `Audio`.`inserted`, `Disc`.`id`, `Disc`.`title`, `Disc`.`site`, `Disc`.`stream`, `Disc`.`type`, `Disc`.`command`, `Disc`.`comment`, `Disc`.`created`, `Disc`.`modified` FROM `disc_server`.`audios` AS `Audio` LEFT JOIN `disc_server`.`discs` AS `Disc` ON (`Audio`.`disc_id` = `Disc`.`id`) WHERE `Audio`.`disc_id` = 3 ORDER BY `Audio`.`id`.DESC ASC LIMIT 20
Sorry If I forget something, thank you so much again for the help.
EDIT 3:
Thank You Anubhav.
I changed the
$this->paginate = array('order' => "$sortBy.$order");
to
$this->paginate = array('order' => "$sortBy $order");
And the database error disappeared.
The result of Debugger::dump(array('order' => "$sortBy $order")) is:
array(
'order' => 'Audio.id ASC'
)
Now I can access http://localhost/cakephp/discs/view/3/ with no problem, but I still can't get to sorting.
I realized that following SQL query (Nr 2):
SELECT `Audio`.`id`, `Audio`.`disc_id`, `Audio`.`name`, `Audio`.`downloaded`, `Audio`.`inserted`, `Audio`.`Disc_id` FROM `disc_server`.`audios` AS `Audio` WHERE `Audio`.`Disc_id` = (3) ORDER BY `Audio`.`id` DESC
only change its order (DESC or ASC), if I change the order in Disc.php Model:
'order' => 'Audio.id DESC'
or
'order' => 'Audio.id ASC'
if I do that, the data result list is sorted as ordered in Disc.php Model.
But, if I trying to sort by access the sort indicator links:
http://localhost/cakephp/discs/view/3/sort:audio.id/direction:desc
and
http://localhost/cakephp/discs/view/3/sort:audio.id/direction:asc
it does not affect the SQL query order. Even if I change order in the function view:
public function view($id = null,$sortBy='Audio.id',$order='DESC')
or
public function view($id = null,$sortBy='Audio.id',$order='ASC')
That query order still the same.
I think paginate in the function view is not get to sort the data for some reason. There is something strange when I access only:
http://localhost/cakephp/discs/view/3
the other query (Nr 7) is:
SELECT `Audio`.`id`, `Audio`.`disc_id`, `Audio`.`name`, `Audio`.`downloaded`, `Audio`.`inserted`, `Disc`.`id`, `Disc`.`title`, `Disc`.`site`, `Disc`.`stream`, `Disc`.`type`, `Disc`.`command`, `Disc`.`comment`, `Disc`.`created`, `Disc`.`modified` FROM `disc_server`.`audios` AS `Audio` LEFT JOIN `disc_server`.`discs` AS `Disc` ON (`Audio`.`disc_id` = `Disc`.`id`) WHERE `Audio`.`disc_id` = 3 ORDER BY `Audio`.`id` DESC LIMIT 20
The order of this query (Nr 7) is related only to:
public function view($id = null,$sortBy='Audio.id',$order='DESC')
So if I change $order='DESC' to ASC the query (Nr 7) changes its order. But it does not change the order of data result list.
When I sort by accessing the sort indicator links:
http://localhost/cakephp/discs/view/3/sort:audio.id/direction:desc
or
http://localhost/cakephp/discs/view/3/sort:audio.id/direction:asc
the query (Nr 7) doesn't have order indicator. It becomes this way:
SELECT `Audio`.`id`, `Audio`.`disc_id`, `Audio`.`name`, `Audio`.`downloaded`, `Audio`.`inserted`, `Disc`.`id`, `Disc`.`title`, `Disc`.`site`, `Disc`.`stream`, `Disc`.`type`, `Disc`.`command`, `Disc`.`comment`, `Disc`.`created`, `Disc`.`modified` FROM `disc_server`.`audios` AS `Audio` LEFT JOIN `disc_server`.`discs` AS `Disc` ON (`Audio`.`disc_id` = `Disc`.`id`) WHERE `Audio`.`disc_id` = 3 LIMIT 20
Sorry, It's all so confused, but I hope these informations help us to find a solution for this trouble.
Thank you so much for help me.
Change your view function as shown below: You have to change sorting criteria in your view function:
public function view($id = null,$sortBy='Audio.id',$order='DESC') {
$this->Disc->recursive = 2; # or -1, 0, 1, 2, 3
$this->Disc->id = $id;
$this->set('disc', $this->Disc->read());
# set sorting criteria for paginator
$this->paginate = array('order' => "$sortBy $order");
$conditions = array("Audio.disc_id" => "$id");
$this->set('audio', $this->paginate('Audio', $conditions));
}