Using count_all_results or get_compiled_select and $this->db->get('table') lists table twice in query?

Question

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);
 }

Answer 1

Rick James got me on the right track. I ended up having to chunk the results using pagination AND a nested query. Using LIMIT on even 1 chunk of 2000 records was timing out. This is the code I ended up with, which uses get_compiled_select('tblProgram') and then get('tblTrees O1'). Since I didn't use FALSE as the second argument to get_compiled_select, the query was cleared before the get() was run.

        //grab the data in chunks, write it to CSV chunk by chunk
        $offset=0;
        $chunk=2000;    
        $i=10;  //counter for the progress bar
        
        $this->db->limit($chunk);
        $this->db->select('tblTrees.id');
        
        //nesting the limited query and then joining the other field later improved performance significantly
        $query1=' ('.$this->db->get_compiled_select('tblProgram').') AS O2';
        $this->db->join($query1, 'O1.id=O2.id');
       
        $result=$this->db->get('tblTrees O1')->result_array();
        $allResults=count($result);
        $putHeaders=0;
        
        $treePath=$this->config->item('temp_path')."$rand/trees.csv";
        $tree_handle=fopen($treePath,'a');  
        
        //while select limit returns the limit
        while (count($result)===$chunk) {
            $highestID=max(array_column($result, 'id'));
            
            //update progres bar with estimate
            if ($i<90) {
              $this->set_runStatus($qcRunId, $status = "processing", $progress = $i);
              $i=$i+1;
            }

            //only get the fields the first time
            foreach ($result as $row) {
                if ($offset===0 && $putHeaders===0){
                    fputcsv($tree_handle, array_keys($row));
                    $putHeaders=1;
                }
                fputcsv($tree_handle, $row);
            }  
            
            //get the next chunk
            $offset=$offset+$chunk;
            $this->db->reset_query();
            $this->make_query($options);
            $this->db->order_by('tblTrees.id', 'ASC');
            $this->db->where('tblTrees.id >', $highestID);
            $this->db->limit($chunk);
            $this->db->select('tblTrees.id');
            $query1=' ('.$this->db->get_compiled_select('tblProgram').') AS O2';
            $this->db->join($query1, 'O1.id=O2.id');
                   
            $result=$this->db->get('tblTrees O1')->result_array();
            
            $allResults=$allResults+count($result);
        }
            //write out last chunk 
            foreach ($result as $row) {
                fputcsv($tree_handle, $row);
            }          
            fclose($tree_handle);

            return array('resultCount'=>$allResults);