Grocery_CRUD where clause does not work together with filters

Question

In the project in which I fixed errors, was used grocery_CRUD () plugin. The documentation has an example of using the where clause as below:

$crud = new grocery_CRUD();
$crud->set_language($this->config->item('language'));  
$crud->set_table('users_products');      
$crud->where('users_products.users_id !=', 1);

The table does not contain records that do not meet the conditions. It looks like everything is ok. Unfortunately, when the user uses the filters placed under the plugin, records that do not meet the condition are also included in the search results.

What am I doing wrong? How do I add search terms?

Answer 1

Ok, I found a solution to the problem. Maybe it is not perfect and probably needs some refactoring but everyone I think will understand the principle of this solution:

In /application/models/Grocery_crud_model.php we must to add two functions:

    function group_start()
    {
        $this->db->group_start();
    }
    function group_end()
    {
        $this->db->group_end();
    }

In /application/libraries/Grocery_CRUD.php we add:

    public function whereFix($key, $value = NULL, $escape = TRUE)
    {
        $this->whereFix[] = array($key,$value,$escape);

        return $this;
    }

In the same file we add loop to create whereFix conditions and closure for another types of terms by group_start() and group_end(). In fact we can replace functions get_list() and get_total_results():

    protected function get_list()
    {
        if(!empty($this->whereFix))
            foreach($this->whereFix as $where)
                $this->basic_model->where($where[0],$where[1],$where[2]);

        if(!empty($this->order_by))
            $this->basic_model->order_by($this->order_by[0],$this->order_by[1]);


        if(!empty($this->where)){
                    $this->basic_model->group_start();
            foreach($this->where as $where)
                $this->basic_model->where($where[0],$where[1],$where[2]);
                    $this->basic_model->group_end();
                }

        if(!empty($this->or_where)){
                    $this->basic_model->group_start();
            foreach($this->or_where as $or_where)
                $this->basic_model->or_where($or_where[0],$or_where[1],$or_where[2]);
                    $this->basic_model->group_end();
                }        

        if(!empty($this->like)){
                    $this->basic_model->group_start();
            foreach($this->like as $like)
                $this->basic_model->like($like[0],$like[1],$like[2]);
                    $this->basic_model->group_end();
                }

        if(!empty($this->or_like)){
                    $this->basic_model->group_start();
            foreach($this->or_like as $or_like)
                $this->basic_model->or_like($or_like[0],$or_like[1],$or_like[2]);
                    $this->basic_model->group_end();
                }

        if(!empty($this->having))
            foreach($this->having as $having)
                $this->basic_model->having($having[0],$having[1],$having[2]);

        if(!empty($this->or_having))
            foreach($this->or_having as $or_having)
                $this->basic_model->or_having($or_having[0],$or_having[1],$or_having[2]);

        if(!empty($this->relation))
            foreach($this->relation as $relation)
                $this->basic_model->join_relation($relation[0],$relation[1],$relation[2]);

        if(!empty($this->relation_n_n))
        {
            $columns = $this->get_columns();
            foreach($columns as $column)
            {
                //Use the relation_n_n ONLY if the column is called . The set_relation_n_n are slow and it will make the table slower without any reason as we don't need those queries.
                if(isset($this->relation_n_n[$column->field_name]))
                {
                    $this->basic_model->set_relation_n_n_field($this->relation_n_n[$column->field_name]);
                }
            }

        }

        if($this->theme_config['crud_paging'] === true)
        {
            if($this->limit === null)
            {
                $default_per_page = $this->config->default_per_page;
                if(is_numeric($default_per_page) && $default_per_page >1)
                {
                    $this->basic_model->limit($default_per_page);
                }
                else
                {
                    $this->basic_model->limit(10);
                }
            }
            else
            {
                $this->basic_model->limit($this->limit[0],$this->limit[1]);
            }
        }

        $results = $this->basic_model->get_list();

        return $results;
    }

    protected function get_total_results()
    {
        if(!empty($this->whereFix))
            foreach($this->whereFix as $where)
                $this->basic_model->where($where[0],$where[1],$where[2]);

                if(!empty($this->where)){
                    $this->basic_model->group_start();
            foreach($this->where as $where)
                $this->basic_model->where($where[0],$where[1],$where[2]);
                    $this->basic_model->group_end();
                }

        if(!empty($this->or_where)){
                    $this->basic_model->group_start();
            foreach($this->or_where as $or_where)
                $this->basic_model->or_where($or_where[0],$or_where[1],$or_where[2]);
                    $this->basic_model->group_end();
                }

        if(!empty($this->like)){
                    $this->basic_model->group_start();
            foreach($this->like as $like)
                $this->basic_model->like($like[0],$like[1],$like[2]);
                    $this->basic_model->group_end();
                }

        if(!empty($this->or_like)){
                    $this->basic_model->group_start();
            foreach($this->or_like as $or_like)
                $this->basic_model->or_like($or_like[0],$or_like[1],$or_like[2]);
                    $this->basic_model->group_end();
                }

        if(!empty($this->having))
            foreach($this->having as $having)
                $this->basic_model->having($having[0],$having[1],$having[2]);

        if(!empty($this->or_having))
            foreach($this->or_having as $or_having)
                $this->basic_model->or_having($or_having[0],$or_having[1],$or_having[2]);

        if(!empty($this->relation))
            foreach($this->relation as $relation)
                $this->basic_model->join_relation($relation[0],$relation[1],$relation[2]);

        if(!empty($this->relation_n_n))
        {
            $columns = $this->get_columns();
            foreach($columns as $column)
            {
                //Use the relation_n_n ONLY if the column is called . The set_relation_n_n are slow and it will make the table slower without any reason as we don't need those queries.
                if(isset($this->relation_n_n[$column->field_name]))
                {
                    $this->basic_model->set_relation_n_n_field($this->relation_n_n[$column->field_name]);
                }
            }

        }

        return $this->basic_model->get_total_results();
    }

I hope this piece of code will be useful for someone.