Snowdrama Snowdrama - 1 year ago 145
MySQL Question

How do I call Codeigniters 2 Active record to get 2 sets of results from the same query?

I am essentially creating a chunking function for a file export, and I have a select->from->where that I have built already. I want to be able to call 'get' twice each time with different limit/offset values.

Here's a walk-through of the basic idea.

// BallReport.php
function ProcessData(){
//Report 1
$query = createSelectQuery();
$query = applyReportOneWhereValues($query);
$results1 = CSVTool::processLargeDataSet($query, 10, 1000);

//Report 2
$query = createSelectQuery();
$query = applyReportTwoWhereValues($query);
$results2 = CSVTool::processLargeDataSet($query, 10, 1000);
}

function createSelectQuery(){
// the select is complicated having multiple joins and sub queries
// so I only want to have to write this once
$query = $this->db->select('ball.name,
color.name,
size.name,
shape.name')
->from('ball')
->join('color', 'ball.color_id = color.id')
->join('size', 'ball.size_id = size.id')
->join('shape', 'ball.shape_id = shape.id');
return $query;
}

function applyReportOneWhereValues($query){
// I have 2 different sets of where parameters
// But they are both using the same select
// so I separated them into these functions
// So I can apply the set of where statements
// all at once
$query = $query->where("table.color", "blue")
->where("table.size" , "large")
->where("table.shape", "round");
return $query;
}

function applyReportTwoWhereValues($query){
$query = $query->where("table.color", "red")
->where("table.size" , "small")
->where("table.shape", "round");
return $query;
}

//In CSVTool.php
public static function processLargeDataSet($query, $numberOfPages, $chunkSize){
// Since the data set is going to be so large we want to process in chunks
// So that we don't hit the limit and break mid way.
// To do that we only call the DB in sets of 1000 rows
for(int $i = 0; $i <= $numberOfPages: $i++){
processRows($query, $i * $chunkSize, $chunkSize);
}
}

function processRows($query, $offset, $limit){
// We limit in here so each time it's called we change the offset and limit
$query = $query->offset($offset)->limit($limit);
$valuesToProcess = $query->get()->result_array();

// process the rows here
}


this of course doesn't work because once processRows calls $query->get() the first time all subsequent calls throws a
Query error: No tables used


Is there any solution for this? Is there a chunking function in Codeigniter 2 that I'm unaware of?

Answer Source

Here is a new answer to the revised question.

public function ProcessData()
{
    //Report 1
    $query_builder = $this->applyReportOneWhereValues($this->createSelectQuery());
    $this->db->stop_cache();
    $results1 = CSVTool::processLargeDataSet($query_builder, 10, 1000);
    $this->db->flush_cache();

    //Report 2
    $query_builder = $this->applyReportTwoWhereValues($this->createSelectQuery());
    $this->db->stop_cache();
    $results2 = CSVTool::processLargeDataSet($query_builder, 10, 1000);
    $this->db->flush_cache(); //just to be safe
}

public function createSelectQuery()
{
    $this->db->start_cache();
    return $this->db->select('ball.name, color.name, size.name, shape.name')
        ->join('color', 'ball.color_id = color.id')
        ->join('size', 'ball.size_id = size.id')
        ->join('shape', 'ball.shape_id = shape.id');
}

public function applyReportOneWhereValues($query_builder)
{
    return $query_builder
      ->where("table.color", "blue")
      ->where("table.size", "large")
      ->where("table.shape", "round");
}

public function applyReportTwoWhereValues($query_builder)
{
    return $query_builder
      ->where("table.color", "red")
      ->where("table.size", "small")
      ->where("table.shape", "round");
}

In CSVTool.php

/**
 * Process the records in chunks
 * @param CI_DB_query_builder $qb An instance of the CI_DB_query_builder class
 * @param int $numberOfPages The number of pages to create in the set (1 to n)
 * @param int $pageSize The number of records per page
 */
public static function processLargeDataSet($qb, $numberOfPages, $pageSize)
{
    if($numberOfPages < 1)
    {
        $numberOfPages = 1;
    }
    for($i = 1; $i < $numberOfPages; $i++)
    {
        $valuesToProcess = $qb
          ->get('ball', $pageSize, $i - 1 * $pageSize)
          ->result_array();
        // process the rows in $valuesToProcess
    }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download