[TechDaddies-Kevin]

We are using a custom model for one of our grocery crud pages and it is working great for the most part. The one problem is that we cannot search fields from our joined table. Our error log shows a query error when we try to search on this field "Unknown column 'card' in 'where clause'"

 

Here is our GC setup:

 

[php]        $crud = new grocery_CRUD();

        $crud->set_model('grocery_crud/Orders_crud');

 

        $crud->set_table('store_orders')->set_subject('Order');

 

        $crud->columns('shipping_first_name', 'shipping_last_name', 'transaction_id', 'subtotal','shipping', 'date', 'card');

 

        $crud->display_as('transaction_id', 'Order ID');

        $crud->unset_edit();

        $crud->unset_delete();

        $crud->unset_add();

 

        $crud->order_by('transaction_id', 'DESC');

 

        $output = $crud->render();

 

        $this->load->view('admin/view_user_orders', $output);

[/php]

 

Here is our model, which brings in the date and card columns:

 

[php]<?php

class Orders_crud extends grocery_CRUD_Model

{

//The function get_list is just a copy-paste from grocery_CRUD_Model

    function get_list()

    {

        if($this->table_name === null)

            return false;

 

        $select = "`{$this->table_name}`.*";

        $select .= ", transactions.date as date, transactions.card as card";

 

        //set_relation special queries

        if(!empty($this->relation))

        {

            foreach($this->relation as $relation)

            {

                list($field_name , $related_table , $related_field_title) = $relation;

                $unique_join_name = $this->_unique_join_name($field_name);

                $unique_field_name = $this->_unique_field_name($field_name);

 

                if(strstr($related_field_title,'{'))

                {

                    $related_field_title = str_replace(" ","&nbsp;",$related_field_title);

                    $select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE({$unique_join_name}.",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $unique_field_name";

                }

                else

                {

                    $select .= ", $unique_join_name.$related_field_title AS $unique_field_name";

                }

 

                if($this->field_exists($related_field_title))

                    $select .= ", `{$this->table_name}`.$related_field_title AS '{$this->table_name}.$related_field_title'";

            }

        }

 

        //set_relation_n_n special queries. We prefer sub queries from a simple join for the relation_n_n as it is faster and more stable on big tables.

        if(!empty($this->relation_n_n))

        {

            $select = $this->relation_n_n_queries($select);

        }

 

        $this->db->select($select, false);

        $this->db->join('transactions', 'store_orders.transaction_id = transactions.id');

 

        $results = $this->db->get($this->table_name)->result();

 

        return $results;

    }

 

}

[/php]

 

Any advice on getting the searching working in joined tables would be great.

[TechDaddies-Kevin]

This is not a bug. We just had to add another method to our model.

 

From /topic/735-searching-on-a-custom-model-with-joins/#entry8444

 

We added the following method:

 

[php]  

    function get_total_results() {

 

        $this->db->from($this->table_name);

        //make the joins you need.

        $this->db->join('transactions','store_orders.transaction_id = transactions.id');

        return $this->db->count_all_results();

    }

[/php]

 

Now searching on joined tables works beautifully.

 

I really love Grocery Crud sometimes.