[onica]

Hi,

 

Search by name is not working on bootstrap theme (it is working on flexigrid). I uploaded a screenshot

 

the table diplayed in the grid is papers, related to table users like this

 

$crud->set_relation('user_id','users','{last_name} {first_name}');        

$crud->display_as('user_id','Name');
       

Thank you !

               

 

[Amit Shah]

Well,

 

This issues were addressed earlier too, but anyways, i still can share the same again :) ...

In the method serving the crud functionality, if the current state is search ... and if there is user_id in search ..

extract the values for user_id .... set it as where in the joining table...

ex:

$search_value = .....//Some code to extract the value from the search parameter  (pardon me .. i forgot the actual code)

$crud->where('RelationTable.firstName', $search_value)

$crud->where_or('RelationTable.lastName',$search_value)

 

...This way .. when u set it .. crud will filter it.. remember to mention the <relationtable>.<fieldname> as part of where clause as the query may not be able to identify firstName / lastName directly associated with the query

 

This solution should work fine.

 

Happy GCing :)

[pimnr47]

Well,

 

This issues were addressed earlier too, but anyways, i still can share the same again :) ...

In the method serving the crud functionality, if the current state is search ... and if there is user_id in search ..

extract the values for user_id .... set it as where in the joining table...

ex:

$search_value = .....//Some code to extract the value from the search parameter  (pardon me .. i forgot the actual code)

$crud->where('RelationTable.firstName', $search_value)

$crud->where_or('RelationTable.lastName',$search_value)

 

...This way .. when u set it .. crud will filter it.. remember to mention the <relationtable>.<fieldname> as part of where clause as the query may not be able to identify firstName / lastName directly associated with the query

 

This solution should work fine.

 

Happy GCing :)

 Hi Amit,

 

When I use the WHERE clause in my controller with the Bootstrap theme, the frontend search (through the ajax_list call) ignores this where clause. Do you know how to fix that as well?

 

Thnx!

 

Edit: 

 

Here is the query from my query log that is currently produced:

 

FROM `sale`

LEFT JOIN `pointofsale` as `j2b19ee5f` ON `j2b19ee5f`.`id` = `sale`.`posId`

WHERE `posId` = '3'

OR  `saletransactionId` LIKE '%test%' ESCAPE '!'

OR  `code` LIKE '%test%' ESCAPE '!'

OR  `name` LIKE '%test%' ESCAPE '!'

OR  `timestamp` LIKE '%test%' ESCAPE '!'

OR  `productname` LIKE '%test%' ESCAPE '!'

OR  `productprice` LIKE '%test%' ESCAPE '!'

OR  `quantity` LIKE '%test%' ESCAPE '!'

OR  `totalexvat` LIKE '%test%' ESCAPE '!'

OR  `vatpercentage` LIKE '%test%' ESCAPE '!'

OR  `vat` LIKE '%test%' ESCAPE '!'

OR  `paymentmethod` LIKE '%test%' ESCAPE '!'

 

 

And how I would like it to be:

 

FROM `sale`

LEFT JOIN `pointofsale` as `j2b19ee5f` ON `j2b19ee5f`.`id` = `sale`.`posId`

WHERE `posId` = '3'

AND  (`saletransactionId` LIKE '%test%' ESCAPE '!'

OR  `code` LIKE '%test%' ESCAPE '!'

OR  `name` LIKE '%test%' ESCAPE '!'

OR  `timestamp` LIKE '%test%' ESCAPE '!'

OR  `productname` LIKE '%test%' ESCAPE '!'

OR  `productprice` LIKE '%test%' ESCAPE '!'

OR  `quantity` LIKE '%test%' ESCAPE '!'

OR  `totalexvat` LIKE '%test%' ESCAPE '!'

OR  `vatpercentage` LIKE '%test%' ESCAPE '!'

OR  `vat` LIKE '%test%' ESCAPE '!'

OR  `paymentmethod` LIKE '%test%' ESCAPE '!')

 

 

 

I guess I have to change something in the set_ajax_list_queries function, but I cannot figure it out.

[pimnr47]

 Hi Amit,

 

When I use the WHERE clause in my controller with the Bootstrap theme, the frontend search (through the ajax_list call) ignores this where clause. Do you know how to fix that as well?

 

Thnx!

 

Edit: 

 

Here is the query from my query log that is currently produced:

 

FROM `sale`

LEFT JOIN `pointofsale` as `j2b19ee5f` ON `j2b19ee5f`.`id` = `sale`.`posId`

WHERE `posId` = '3'

OR  `saletransactionId` LIKE '%test%' ESCAPE '!'

OR  `code` LIKE '%test%' ESCAPE '!'

OR  `name` LIKE '%test%' ESCAPE '!'

OR  `timestamp` LIKE '%test%' ESCAPE '!'

OR  `productname` LIKE '%test%' ESCAPE '!'

OR  `productprice` LIKE '%test%' ESCAPE '!'

OR  `quantity` LIKE '%test%' ESCAPE '!'

OR  `totalexvat` LIKE '%test%' ESCAPE '!'

OR  `vatpercentage` LIKE '%test%' ESCAPE '!'

OR  `vat` LIKE '%test%' ESCAPE '!'

OR  `paymentmethod` LIKE '%test%' ESCAPE '!'

 

 

And how I would like it to be:

 

FROM `sale`

LEFT JOIN `pointofsale` as `j2b19ee5f` ON `j2b19ee5f`.`id` = `sale`.`posId`

WHERE `posId` = '3'

AND  (`saletransactionId` LIKE '%test%' ESCAPE '!'

OR  `code` LIKE '%test%' ESCAPE '!'

OR  `name` LIKE '%test%' ESCAPE '!'

OR  `timestamp` LIKE '%test%' ESCAPE '!'

OR  `productname` LIKE '%test%' ESCAPE '!'

OR  `productprice` LIKE '%test%' ESCAPE '!'

OR  `quantity` LIKE '%test%' ESCAPE '!'

OR  `totalexvat` LIKE '%test%' ESCAPE '!'

OR  `vatpercentage` LIKE '%test%' ESCAPE '!'

OR  `vat` LIKE '%test%' ESCAPE '!'

OR  `paymentmethod` LIKE '%test%' ESCAPE '!')

 

 

 

I guess I have to change something in the set_ajax_list_queries function, but I cannot figure it out.

 

I fixed it by changing the set_ajax_list_queries method:

 

 

   protected function set_ajax_list_queries($state_info = null) {

        if (!empty($state_info->per_page)) {

            if (empty($state_info->page) || !is_numeric($state_info->page))

                $this->limit($state_info->per_page);

            else {

                $limit_page = ( ($state_info->page - 1) * $state_info->per_page );

                $this->limit($state_info->per_page, $limit_page);

            }

        }

 

        if (!empty($state_info->order_by)) {

            $this->order_by($state_info->order_by[0], $state_info->order_by[1]);

        }

 

        if (!empty($state_info->search)) {

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

                foreach ($this->relation as $relation_name => $relation_values) {

                    $temp_relation[$this->_unique_field_name($relation_name)] = $this->_get_field_names_to_search($relation_values);

                }

            }

 

            if (is_array($state_info->search)) {

                foreach ($state_info->search as $search_field => $search_text) {

 

 

                    if (isset($temp_relation[$search_field])) {

                        if (is_array($temp_relation[$search_field])) {

                            foreach ($temp_relation[$search_field] as $relation_field) {

                                $this->or_like($relation_field, $search_text);

                            }

                        } else {

                            $this->like($temp_relation[$search_field], $search_text);

                        }

                    } elseif (isset($this->relation_n_n[$search_field])) {

                        $escaped_text = $this->basic_model->escape_str($search_text);

                        $this->having($search_field . " LIKE '%" . $escaped_text . "%'");

                    } else {

                        $this->like($search_field, $search_text);

                    }

                }

            } elseif ($state_info->search->field !== null) {

                if (isset($temp_relation[$state_info->search->field])) {

                    if (is_array($temp_relation[$state_info->search->field])) {

                        foreach ($temp_relation[$state_info->search->field] as $search_field) {

                            $this->or_like($search_field, $state_info->search->text);

                        }

                    } else {

                        $this->like($temp_relation[$state_info->search->field], $state_info->search->text);

                    }

                } elseif (isset($this->relation_n_n[$state_info->search->field])) {

                    $escaped_text = $this->basic_model->escape_str($state_info->search->text);

                    $this->having($state_info->search->field . " LIKE '%" . $escaped_text . "%'");

                } else {

                    $this->like($state_info->search->field, $state_info->search->text);

                }

            } else {

                $columns = $this->get_columns();

 

                $search_text = $state_info->search->text;

 

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

                    foreach ($this->where as $where)

                        $this->basic_model->where($where[0], $where[1], $where[2]);

 

                $likes = array();

                

                foreach ($columns as $column) {

                    if (isset($temp_relation[$column->field_name])) {

                        if (is_array($temp_relation[$column->field_name])) {

                            foreach ($temp_relation[$column->field_name] as $search_field) {

                                $likes[$search_field] = $search_text;

                                //$this->or_like($search_field, $search_text);

                            }

                        } else {

                            $likes[$temp_relation[$column->field_name]] = $search_text;

                            //$this->or_like($temp_relation[$column->field_name], $search_text);

                        }

                    } elseif (isset($this->relation_n_n[$column->field_name])) {

                        //@todo have a where for the relation_n_n statement

                    } else {

                        $likes[$column->field_name] = $search_text;

                        //$this->or_like($column->field_name, $search_text);

                    }

                }

                

                $where_clause = "";

                foreach ($likes as $field => $search_text) {

                    $where_clause = $where_clause. " OR ".$field. " LIKE '%".$search_text."%'";

                }

                

                if(sizeof($likes)>0){

                    $where_clause = "(".substr($where_clause, 4).")";

                    $this->basic_model->where($where_clause);

                }

                

                

            }

        }

    }