php codeigniter活动记录获取查询和不带LIMIT子句的查询

codeigniter active record get query and query without the LIMIT clause

提问by braindamage

im using active record, its all working ok but i want to set the $data["totalres"]to the total results, i mean, the same query but without the LIMIT

我正在使用活动记录，一切正常，但我想将$data["totalres"]设置为总结果，我的意思是，相同的查询但没有LIMIT

the problem is the previous statements gets unset when you do a query modifier, so i cant even add the $this->db->limit() after i get the results.

问题是当您执行查询修饰符时，前面的语句会被取消设置，因此在获得结果后我什至无法添加 $this->db->limit() 。

any ideas? i think its a bad practice to 'duplicate' the query just to do this

有任何想法吗？我认为只是为了做到这一点而“复制”查询是一种不好的做法

function get_search($start, $numrows, $filter = array())
{    

    ...

    $this->db
    ->select("emp")
    ->from('emp')
    ->join('empr', 'empr.b = empr.id', 'left')
    ->like('code', $code)
    ->limit($numrows, $start);

    ...

    $q = $this->db->get();        

    // number of rows WITHOUT the LIMIT X,Y filter
    $data["totalres"] = ???????;        

    if ($q->num_rows() > 0)
    {        
        $data["results"] = $q->result();
    } else {
        $data["results"] = array();
    }   

    return $data;
}    

回答by Rocket Hazmat

You can use SQL_CALC_FOUND_ROWSto get the number of rows that would have been returned sans-LIMIT. Note the ,FALSEin the selectline. This tells CodeIgniter not to try to escape the SELECTclause with backticks (because SQL_CALC_FOUND_ROWSis not a field, and CodeIgniter doesn't realize that).

您可以使用SQL_CALC_FOUND_ROWS来获取本应以 sans- 方式返回的行数LIMIT。请注意,FALSE在select行。这告诉 CodeIgniter 不要试图SELECT用反引号转义子句（因为SQL_CALC_FOUND_ROWS它不是一个字段，而且 CodeIgniter 没有意识到这一点）。

$this->db
->select("SQL_CALC_FOUND_ROWS emp", FALSE)
->from('emp')
->join('empr', 'empr.b = empr.id', 'left')
->like('code', $code)
->limit($numrows, $start);

$q = $this->db->get();

Then after that query is ran, we need run another query to get the total number of rows.

然后在运行该查询之后，我们需要运行另一个查询来获取总行数。

$query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
$data["totalres"] = $query->row()->Count;

回答by Luke

I would actually suggest the use of CIs query caching.

我实际上建议使用 CI 查询缓存。

To use this, you start the cache, build the query without the limits in place. Run your query to get the full count, then apply the limit and run the query to get your list for your page with the offset you need.

要使用它，您需要启动缓存，在没有限制的情况下构建查询。运行查询以获取完整计数，然后应用限制并运行查询以获取具有所需偏移量的页面列表。

The cache will remember the variables that have been defined.

缓存将记住已定义的变量。

You can then clear the cache for subsequent queries.

然后，您可以为后续查询清除缓存。

Example

例子

    $this->db->start_cache();

    // Where, like, having clauses in here

    $this->db->stop_cache();

    $count = $this->db->get('table')->num_rows();

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

    $result = $this->db->get('table')->result_array();

    $this->db->flush_cache();

回答by Alfonso Rubalcava

Try this:

尝试这个：

function get_search($start, $numrows, $filter = array()){    
    $tmp= $this->db
    ->select("emp")
    ->from('emp')
    ->join('empr', 'empr.b = empr.id', 'left')
    ->like('code', $code)
    ->_compile_select();

    $q= $this->db->limit($numrows, $start)->get();

    // number of rows WITHOUT the LIMIT X,Y filter

    $data["totalres"] = $this->db->query($tmp)->num_rows();

    if ($q->num_rows() > 0){        
        $data["results"] = $q->result();
    } else {
        $data["results"] = array();
    }   
    return $data;
}    

回答by chu.tien

$this->db
->select("SQL_CALC_FOUND_ROWS emp", FALSE)
->from('emp')
->join('empr', 'empr.b = empr.id', 'left')
->like('code', $code)
->limit($numrows, $start); $q = $this->db->get();

$query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
$data["totalres"] = $this->db->get()->row()->Count;

CodeIgniter 2.1.0 not run, below code will fix it.

CodeIgniter 2.1.0 未运行，下面的代码将修复它。

$query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
$objCount = $query->result_array();
$data["totalres"] = $objCount[0]['Count'];