Count rows before adding a further limit statement

问题:

I have ran into a problem...

I have a bunch of where statments like so...

$this->db->where('Pool', "1");
$this->db->where('Bedrooms >=', "3");

Then a limit statement

$this->db->limit($limit, $offset);

And finally my get statement

$query = $this->db->get('table-name');

My problem is I need to count the results before my limit statement, to get the total rows without the limit.. So I tried this..

$this->db->where('Pool', "1");
$this->db->where('Bedrooms >=', "3");

$num_rows = $this->db->count_all_results();

$this->db->limit($limit, $offset);
$query = $this->db->get('table-name');

This counts my rows with the where statements fine.. However, the get statement now gets records without the previous where statements working.

It's not visible, but there is a large amount of code handling more where statements, and grabbing things in urls, So I'd prefer not to perform the retrieval of data twice in order to fix this...

Cheers!

回答1:

$get_data = $this->your_model->get_data();
$data     = $get_data['data'];
$count    = $get_data['count'];

Model

function get_data($limit = 10, $offset= 0)
{
    $table = 'table-name';
    $where = array('Pool' => 1, 'Beedrooms >=' 3);

    $return['data']  = $this->db->from($table)->where($where)->limit($limit, $offset)->get();
    $return['count'] = $this->db->from($table)->where($where)->count_all_results();

    return $return;
}

回答2:

I know this is an old question, but I just ran into this problem and came up with a different solution.

The idea is to take a copy of the db class before the limit and offset.

$this->db->where('Pool', "1");
$this->db->where('Bedrooms >=', "3");

//here we use the clone command to create a shallow copy of the object
$tempdb = clone $this->db;
//now we run the count method on this copy
$num_rows = $tempdb->from('table-name')->count_all_results();

$this->db->limit($limit, $offset);
$query = $this->db->get('table-name');

回答3:

    $this->db->select('*');
    $this->db->from('users');
    $this->db->where('active',$status);

    //open1 here we copy $this->db in to tempdb and apply 
    //count_all_results() function on to this tempdb
    $tempdb = clone $this->db;
    $num_results= $tempdb->count_all_results();   

    // now applying limit and will get actual result 
    $this->db->limit(10);

    $this->db->get();
    $query = $this->db->last_query();
    $res = $this->db->query($query);        

    $data_array = array('num_results' => $num_results, 'results' => $res->result() );
    return $data_array;

回答4:

I know that's an old question but I found a pretty simple solution.

//do your select, from and where
$this->db->select('your selects');
$this->db->from('your table');

$this->db->where('your where');

//get the filtered rows count
//the trick is the first empty parameter and second false parameter
$filtered_count = $this->db->count_all_results('', false);

//limit your results and get the rows
$this->db->limit($length, $start);
$results = $this->db->get()->result_array();

Hope it helps someone

回答5:

It is quite evident that you would need to use two different queries. It would be optimum to do this as quickly as possible using a single query, but since you need to get all the records before the second query, we need to use two queries.

However, you can optimize the first query based on the engine you use with MySQL. If you use InnoDB then you should use SELECT COUNT(*) FROM <table-name> cause the total row size is cached in InnoDB.

I believe count_all_rows uses count(*) for performance and you should be sorted using this direcctly.

With MyISAM you can use COUNT(<column-name>).

So, you have a count function in your model class which returns the count for your table and then you can call the function to insert/update/get data from your database.

回答6:

You can use SQL_CALC_FOUND_ROWS of mysql

SELECT SQL_CALC_FOUND_ROWS * FROM table1
WHERE
  cond1, cond2, ..., condN
LIMIT 10

SELECT FOUND_ROWS();

This is using mysql, you may need to check how to use it codeignitor way.

Reference:

https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows

回答7:

I know this question is old but I had the same issue and got a simpler solution than the displayed here. No need to build the query twice and no need to clone the db class. Just count the result without resting the query builder after you add the where part and then you add the limit and execute your query.

$this->db->where('Pool', 1);
$this->db->where('Beedrooms >=' 3);
$count = $this->db->count_all_results('table-name', false); // No reset query builder
$this->db->limit($limit, $offset)
$result = $this->db->get();

You are going to have two variables: $count with the number of results and $result with the result.