Custom Model for Grocery CRUD version 1
I reopen here an old post in the previous Grocery Crud forum. The issue was to manage custom queries within Grocery Crud and the real problem was to have correct pagination, (tried to post the link but the system does not allow me to do it). Alok Pandey posted his own solution there, a great way to manage this need. Regretfully, there was a logical mistake for which just the first page was available; the “solution” proposed by GiancarloN increased the problem loading on the web page the entire dataset without pagination. The original problem was that the query at first call was changed by Grocery Crude kernel adding LIMIT N where N is the number of rows chosen by the end-user, by default N = 10. The same query is used by the function get_total_results() the output of which is the total number of rows returned by the custom query and used to calculate the number of pages (TOTAL ROWS / N plus 1 if the rest of the division is different than 0). Since the original query, at the time when the function get_total_results() is called, is already modified by LIMIT N the total number of pages was always equal to 1. The solution is to create two variables containing the same original custom query: the first to be used for the rows to be shown at each web page limited to N; the second to be used to calculate the number of pages letting the function get_total_results() to return the total number of rows without the limitation of N rows. Here following the entire code with this correction. Tested and working; hope it will help others.
IN YOUR CONTROLLER SET YOUR CUTOM QUERY
$this->grocery_crud->basic_model->set_query_str("SELECT xxx FROM table WHERE something_smart");
CUSTOM MODEL
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Mycustomquery_model extends grocery_CRUD_model {
private $original_query_str = '';
private $query_str = '';
private $like_arr = array();
private $or_like_arr = array();
private $order_by_str = '';
private $limit_str = '';
private $group_by_str = '';
public function set_query_str($query_str) {
$this->query_str = $query_str;
$this->original_query_str = $query_str;
}
public function __construct(){
parent::__construct();
}
function get_list() {
$this->arrange_queries();
$query=$this->db->query($this->query_str);
$results_array=$query->result();
return $results_array;
}
public function set_custom_query($query_str,$groupby = '') {
$this->group_by_str = $groupby;
$this->query_str = $query_str;
}
function order_by($order_by , $direction){
$this->order_by_str = ' ORDER By '.$order_by.' '.$direction;
}
function like($field, $match = '', $side = 'both'){
$like_str = ' '.$field.' LIKE ';
if($side=='both'){
$like_str.= '"%'.$match.'%"';
}else if($side=='before'){
$like_str.= '"%'.$match.'"';
}else if($side=='after'){
$like_str.= '"'.$match.'%"';
}
$this->like_arr[] = $like_str;
}
function or_like($field, $match = '', $side = 'both'){
$or_like_str = ' '.$field.' LIKE ';
if($side=='both'){
$or_like_str.= '"%'.$match.'%"';
}else if($side=='before'){
$or_like_str.= '"%'.$match.'"';
}else if($side=='after'){
$or_like_str.= '"'.$match.'%"';
}
$this->or_like_arr[] = $or_like_str;
}
function limit($value, $offset = ''){
$this->limit_str = ' LIMIT '.($offset ? $offset.', ' : '').$value;
}
function get_total_results(){
log_message('error', "TOTAL NUMBER OF ROW query_str: ".$this->db->query($this->query_str)->num_rows()); //$query_str
log_message('error', "TOTAL NUMBER OF ROW original_query_str: ".$this->db->query($this->original_query_str)->num_rows());
return $this->db->query($this->original_query_str)->num_rows();
}
public function arrange_queries(){
$query = $this->query_str;
$without_limit_str = str_replace($this->limit_str, '', $query);
$without_order_by_str = str_replace($this->order_by_str, '', $without_limit_str);
$without_group_by_str = str_replace($this->group_by_str,'',$without_order_by_str);
$like_array = $this->like_arr;
$like_str = '';
$or_like_array = $this->or_like_arr;
$or_like_str = '';
$i = 0;
foreach ($like_array as $value) {
if($i==0){
$like_str = ' AND ('.$value;
}
else{
$like_str.= ' AND '.$value;
}
if($i==count($like_array)-1){
$like_str.= ')';
}
$i++;
}
$i=0;
foreach ($or_like_array as $value) {
if($i==0){
$or_like_str = ' AND ('.$value;
}
else{
$or_like_str.= ' OR '.$value;
}
if($i==count($or_like_array)-1){
$or_like_str.= ')';
}
$i++;
}
$query = $without_group_by_str.$like_str.$or_like_str.$this->group_by_str.$this->order_by_str.$this->limit_str;
$this->query_str = $query;
}
}