• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Reuse Active Record query condition to get result and number of result

#1
[eluser]tkaw220[/eluser]
Considering I have below Model:

Code:
$this->db->select('id, title, format, property, isbn')
->where(array($type => urldecode($value), 'bookseller_access !=' => 'no'))
->order_by('format asc, title asc');

$books = $this->db->get('products', $limit, $offset);

$book_total = $this->db->count_all_results(); // FAIL

Firstly, I extracted results by using Get. Next, I want to be able to calculate the number of results returned by the query but fail.

How can I reuse the query, and get the result and number of results at the same time without having to rewrite the query condition again?

Thanks in advanced.

#2
[eluser]danmontgomery[/eluser]
http://ellislab.com/codeigniter/user-gui...sults.html
Code:
$books = $this->db->get('products', $limit, $offset);

$book_total = $books->num_rows();

#3
[eluser]tkaw220[/eluser]
Hi noctrum,

Arr ... I totally forgot this useful function. Thanks for the help.

Have a nice day.

#4
[eluser]tkaw220[/eluser]
Hi notrum,

Sorry for the mistake. The num_rows() did not solve my problem. My first get() function return query with limit (said 20), but I wish to be able to return all query which match the where() clause, said 86 for example.

I am building my product page with pagination, and show 20 products at time. I want to show total 86 products for that brand on top. Below is my working code:

Code:
$where = array($type => urldecode($value), 'bookseller_access !=' => 'no');

$this->db->select('id, title, format, property, isbn')
->where($where)
->order_by('format asc, title asc');

$q = $this->db->get('products', $limit, $offset);

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

$total_items = $this->db->count_all_results('products');

if ($q->num_rows() > 0)
{
    $data['items'] = $q->result();
    $data['total_items'] = $total_items;

    return $data;
}

The first line get() return query limited to $limit, and the second count_all_results() return exactly total books match the where() clause.

I am wondering there is better way to do this instead of having two where() statement.

Thank you.

#5
[eluser]toopay[/eluser]
If the case is : you've use very very common where clause, until using general CI AR syntax become ineffective, you can always create a shorthand for that, as simply like

Code:
// In your model
function select_product()
{
   $this->db->select('id, title, format, property, isbn');
   return $this;
}
function where_not_bookseller($type, $value)
{
   $where = array($type => urldecode($value), 'bookseller_access !=' => 'no');
   $this->db->where($where);
   return $this;
}
function get_product($limit = 0, $offset = 0)
{
   if((is_numeric($limit) AND is_numeric($offset)) AND ($limit > 0 AND $offset !== FALSE))
   {
      $this->db->get('products', $limit, $offset);
   }
   else
   {
      $this->db->get('products');
   }
   return $this;
}
With above model, you now can use it in any controller, with the way CI AR you've used too, like
Code:
$limit_products = $this->product_model->select_product()
                                     ->where_not_bookseller($sometype, $somevalue)
                                     ->get_product($somelimit, $someoffset);
var_dump($limit_products->result_array());
// For all page, with same where clause, you can just do
$all_products = $this->product_model->select_product()
                                     ->where_not_bookseller($sometype, $somevalue)
                                     ->get_product();
var_dump($all_products->result_array());

#6
[eluser]tkaw220[/eluser]
Hi toopay,

Thanks for the trick. Now my code has became more reusable.

Have a nice day.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.