CODEWITHSUNDEEP
phpkohanakohana-3kohana-db
Serhiy
Serhiy

Reputation: 2555

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

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!

Upvotes: 1

Views: 816

Answers (2)

user794314
user794314

Reputation:

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

Upvotes: 2

biakaveron
biakaveron

Reputation: 5483

Use count_last_query()

// $db is a Database instance object
$count = $db->count_last_query();

Upvotes: 2

Related Questions