Using count_all_results or get_compiled_select and $this->db->get('table') lists tab-CodePudding

How do I use get_compiled_select or count_all_results before running the query without getting the table name added twice? When I use $this->db->get('tblName') after either of those, I get the error:

Not unique table/alias: 'tblProgram'

SELECT * FROM (`tblProgram`, `tblProgram`) JOIN `tblPlots` ON `tblPlots`.`programID`=`tblProgram`.`pkProgramID` JOIN `tblTrees` ON `tblTrees`.`treePlotID`=`tblPlots`.`id` ORDER BY `tblTrees`.`id` ASC LIMIT 2000

If I don't use a table name in count_all_results or $this->db->get(), then I get an error that no table is used. How can I get it to set the table name just once?

public function get_download_tree_data($options=array(), $rand=""){

//join tables and order by tree id
   $this->db->reset_query();
   $this->db->join('tblPlots','tblPlots.programID=tblProgram.pkProgramID');
   $this->db->join('tblTrees','tblTrees.treePlotID=tblPlots.id');
   $this->db->order_by('tblTrees.id', 'ASC');

//get number of results to return
   $allResults=$this->db->count_all_results('tblProgram', false);

//chunk data and write to CSV to avoid reaching memory limit
   $offset=0;
   $chunk=2000;
   $treePath=$this->config->item('temp_path')."$rand/trees.csv";
   $tree_handle=fopen($treePath,'a');
   while (($offset<$allResults)) {
      $this->db->limit($chunk, $offset);  
      $result=$this->db->get('tblProgram')->result_array();
      foreach ($result as $row) {
           fputcsv($tree_handle, $row);
      }    
      $offset=$offset $chunk;
    }
                    
    fclose($tree_handle);
    return array('resultCount'=>$allResults);
 }

CodePudding user response：

The flaw in your code is that it aims to select a subset of some records and their total count in the same query. This is impossible in MySQL, so you cannot generate such a query, hence, you get the error as mentioned. The problem is that if you do a

select ... from t where ... limit 0, 2000

then you get maximum 2000 records, so, if the total records matching the criteria have a count that is greater than the limit, then you will not get accurately the count from above, so, in that case you need a

select count(1) from t where ...

This means that you need to build your actual query (the code below your count_all_results call), see whether the number of results reaches the limit. If the number of results does not reach the limit, then you do not need to perform a separate query in order to get the count, because you can compute $offset * $chunk $recordCount. However, if you get as many records as they can be, then you will need to build another query, without the order_by call, since the count is independent of your sort and get the counts.

CodePudding user response：

To count how many rows would be returned by a query, essentially all the work must be performed. That is, it is impractical to get the count, then perform the query; you may as well just do the query.

If your goal is to "paginate" by getting some of the rows, plus the total count, that is essentially two separate actions (that may be combined to look like one.)

If the goal is to estimate the number of rows, then SHOW TABLE STATUS or SELECT Rows FROM information_schema.TABLES WHERE ... gives you an estimate.

If you want to see if there are, say "at least 100 rows", then this may be practical:

SELECT 1 FROM ... WHERE ... ORDER BY ... LIMIT 99,1

and see if you get a row back. However, this may or may not be efficient, depending on the indexes and the WHERE and the ORDER BY. (Show us the query and I can elaborate.)

Using OFFSET for chunking is grossly inefficient. If there is not a usable index, then it is performing essentially the entire query for each chunk. If there is a usable index, the chunks are slower and slower. Here is a discussion of why OFFSET is not good for "pagination", plus an efficient workaround: Pagination . It talks about how to "remember where you left off " as an efficient technique for chunking. Fetch between 100 and 1000 rows per chunk.