[Alok Pandey]

While i was following this /topic/1963-simple-guide-to-executing-custom-queries/ , i too got issues like pagination not working, sorting not working, search not working. And that's why i updated it. I am sharing the code here as well as you can download the model file from the attachment. 

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Custom_grocery_crud_model extends Grocery_crud_model {

    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 __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(){
        $this->arrange_queries();
	return $this->db->query($this->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;
    }

}

/* End of file Custom_grocery_crud_model.php */
/* Location: ./application/models/Custom_grocery_crud_model.php */

Note:- set_custom_query function accepts optional parameter where you should add your group by query. This solves the issue that occurs while pagination when you use group by. 

Apart from this methods, i assume you will use all other methods like where, having, etc in your custom query. 

 

This is my controller method for your reference.

public function conversations(){
	$crud = new grocery_CRUD();
	$crud->set_model('Custom_grocery_crud_model');
	$crud->set_table('messages');
	$crud->basic_model->set_custom_query("my query"," GROUP BY message_from,message_to"); 
	$crud->set_subject('Conversations');
	$data = $crud->render();
	$this->load->view('admin/crud_view',$data);	
}

I had created it to fulfill my purpose but i updated it so that this model works everywhere without any code change. Let me know if there's some issue so that i can try to fix that too.

[GiancarloN]

Well done! this is better than what I have done yesterday so, thank you very much man!

[GiancarloN]

To make pagination work properly I have made some changes:

	function get_total_results(){
		$this->arrange_queries(false);
		return $this->db->query($this->query_str)->num_rows();
	}
	
	public function arrange_queries($limit=true) {
		[...]
		$query = $without_group_by_str.$like_str.$or_like_str.$this->group_by_str.$this->order_by_str;
		if($limit) $query .= $this->limit_str;
		$this->query_str = $query;
	}

[mckaygerhard]

the custom query works, with the pagination but i noted the columns only set columns from the "set_table"

 

if the query has more columns, those are not howed, specially if are from external table

 

how can i added/show the clumns from the query.. maybe i set alternate names?

[Gokul Mu]

Search does not work when using alias in query. eg: if i do select category.field as category1, ... then category1 is not searchable.