• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
orderBy not sorting results

#1
First, you folks are amazing.  Thank you for the tremendous effort you have put into CI4.
I am testing CI4 on a LIMS that I wrote way back for CI2.  I am trying to get sorted results from one of the tables.  I think I am doing it correctly but it is just as likely there is something here I am not understanding.  I have a table with an auto incremented ID and a Cell_Name column.  I am simply trying to do a select all and retrieve the data sorted on the cell name column.  

   $builder = $this->db->table('cell_lines');
   $builder->select('*')
       ->orderBy('Cell_Line', 'asc');

   echo $builder->getCompiledSelect(false);
   $query = $builder->get();
   $temp_result = array();

   foreach ( $query->getResult('array') as $row )
  {
$temp_result[] = array(
'ID'            => $row['CellID'],
'Cell_Name'     => $row['Cell_Name'],
'idCell_Type'   => $row['idCell_Type'],
'idGenotype'    => $row['idGenotype'],
'idBasel_Media' => $row['idBasel_Media'],
);
  }

   return $temp_result;

The generated sql is
                         SELECT * FROM `cell_lines` ORDER BY `Cell_Line` ASC
however I think the order by part is being dropped.

Any suggestions?
Reply

#2
(10-05-2018, 07:54 PM)pkMyt1 Wrote: First, you folks are amazing.  Thank you for the tremendous effort you have put into CI4.
I am testing CI4 on a LIMS that I wrote way back for CI2.  I am trying to get sorted results from one of the tables.  I think I am doing it correctly but it is just as likely there is something here I am not understanding.  I have a table with an auto incremented ID and a Cell_Name column.  I am simply trying to do a select all and retrieve the data sorted on the cell name column.  

   $builder = $this->db->table('cell_lines');
   $builder->select('*')
       ->orderBy('Cell_Line', 'asc');

   echo $builder->getCompiledSelect(false);
   $query = $builder->get();
   $temp_result = array();

   foreach ( $query->getResult('array') as $row )
  {
$temp_result[] = array(
'ID'            => $row['CellID'],
'Cell_Name'     => $row['Cell_Name'],
'idCell_Type'   => $row['idCell_Type'],
'idGenotype'    => $row['idGenotype'],
'idBasel_Media' => $row['idBasel_Media'],
);
  }

   return $temp_result;

The generated sql is
                         SELECT * FROM `cell_lines` ORDER BY `Cell_Line` ASC
however I think the order by part is being dropped.

Any suggestions?

Sorry but I'm unable to reproduce your problem, for my all works fine:

Order by DESC:

Model.php
PHP Code:
public function ordered () {
 
   $query $this->builder()
 
       ->select('*')
 
       ->orderBy('title''desc');
 
   echo $query->getCompiledSelect(false);

 
   return $query->get()->getResult();

[Image: JxnAxGm.png]
[Image: NaVPJ9f.png]

Order by ASC
Model.php
PHP Code:
public function ordered () {
 
   $query $this->builder()
 
       ->select('*')
 
       ->orderBy('title''asc');
 
   echo $query->getCompiledSelect(false);

 
   return $query->get()->getResult();

[Image: V0WM8kT.png]
[Image: w1QvsDU.png]
Reply

#3
(10-05-2018, 11:02 PM)unodepiera Wrote:
(10-05-2018, 07:54 PM)pkMyt1 Wrote: First, you folks are amazing.  Thank you for the tremendous effort you have put into CI4.
I am testing CI4 on a LIMS that I wrote way back for CI2.  I am trying to get sorted results from one of the tables.  I think I am doing it correctly but it is just as likely there is something here I am not understanding.  I have a table with an auto incremented ID and a Cell_Name column.  I am simply trying to do a select all and retrieve the data sorted on the cell name column.  

   $builder = $this->db->table('cell_lines');
   $builder->select('*')
       ->orderBy('Cell_Line', 'asc');

   echo $builder->getCompiledSelect(false);
   $query = $builder->get();
   $temp_result = array();

   foreach ( $query->getResult('array') as $row )
  {
$temp_result[] = array(
'ID'            => $row['CellID'],
'Cell_Name'     => $row['Cell_Name'],
'idCell_Type'   => $row['idCell_Type'],
'idGenotype'    => $row['idGenotype'],
'idBasel_Media' => $row['idBasel_Media'],
);
  }

   return $temp_result;

The generated sql is
                         SELECT * FROM `cell_lines` ORDER BY `Cell_Line` ASC
however I think the order by part is being dropped.

Any suggestions?

Sorry but I'm unable to reproduce your problem, for my all works fine:

Order by DESC:

Model.php
PHP Code:
public function ordered () {
 
   $query $this->builder()
 
       ->select('*')
 
       ->orderBy('title''desc');
 
   echo $query->getCompiledSelect(false);

 
   return $query->get()->getResult();

[Image: JxnAxGm.png]
[Image: NaVPJ9f.png]

Order by ASC
Model.php
PHP Code:
public function ordered () {
 
   $query $this->builder()
 
       ->select('*')
 
       ->orderBy('title''asc');
 
   echo $query->getCompiledSelect(false);

 
   return $query->get()->getResult();

[Image: V0WM8kT.png]
[Image: w1QvsDU.png]

Thank you for checking. I found the error but I don't understand it. I had left

echo $builder->countAll();

in the model just before the get() to confirm I was seeing the same number of rows as in the table. Doing this removed the orderBy from the sql. Working now as it should.

Further reading I understand my error. countAll() resets the select statement. Should have used countAll(false).
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.