When using Kohana DB, how does one avoid duplicate code when needing a count for pagination?

Question

Using the Kohana query builder, is it possible to build my query piece by piece.

Then execute a count on said query.

Then execute the query itself.

All without having to write duplicate conditionals... one for the count and one for the results...

Adding

DB::select(array('COUNT("pid")', 'mycount'))

To the main query results in only getting back one record.

Is it maybe possible to execute the count, and somehow remove

array('COUNT("pid")', 'mycount')

from the select...

Right now the only way I can think of is a find and replace on the SQL code itself, and then just running said code SQL... there must be a better way though... I hope...

Thanks!

Answer 1

To do just that I use 3 methods. First one returns the paginated results, second one gets the count. Third, private method, holds common conditions used by #1 and #2. If the query needs to use JOINs or WHEREs or anything like that, it all goes to #3. This way there is no need to repeat the query.

/* 1 */
public function get_stuff($pagination = false){
    $query = DB::select(/* ... columns here ... */);
    $query = $this->get_stuff_query($query);
    if($pagination) $query->limit($pagination->items_per_page)->offset($pagination->offset);
    return $query->execute();
}

/* 2 */
public function get_stuff_count(){
    $query = DB::select(array('COUNT("id")', 'total_rows'));
    $query = $this->get_stuff_query($query);
    $result = $query->execute();
    return $result->get('total_rows',0);
}

/* 3 */
private function get_stuff_query($query){
    $query->from(/* tablename */);
    $query->join(/* ... */);
    $query->where(/* ... */);
    return $query;
}