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($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->having($where[0],$where[1],$where[2]);
//Get the list of actual columns and then before adding it to search ..
//compare it with the field ... does it exists? .. if yes.. great ..
//go ahead and add it to search list.. if not.. just ignore it
$field_types = $this->get_field_types();
$actual_columns = array();
foreach($field_types as $field) {
if( !isset($field->db_extra) || $field->db_extra != 'auto_increment' )
$actual_columns[] = $field->name;
}
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)
{
$this->or_like($search_field, $search_text);
}
}
else
{
$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
{
if(array_search($column->field_name, $actual_columns) === false) {
continue;
}
$this->or_like($column->field_name, $search_text);
}
}
}
}
}
Hi Johny
This one is exclusively for you to look into. I faced trouble in search ....
Used above code to fix the issue of search omission of the fields that dose not exists - fine - good to go with .. no trouble with. But i suddenly faced an issue - some place other then that tooo.... Following is the code in my controller
function view_all_pickups() {
try{
$this->load->library('grocery_crud_categories') ;
$crud = new grocery_CRUD();
$crud->set_table('locations');
$crud->set_subject('Pickups');
$crud->columns('customer_id', 'pickup_type', 'location', 'city', 'number_of_fryers', 'zone_id', 'next_pickup_date', 'pickup_status');
$crud->set_relation('customer_id', 'customers', 'organization');
$crud->callback_column('pickup_status', array($this, 'show_pickup_status'));
$crud->callback_column('next_pickup_date', 'formatMysqlDate');
$crud->callback_read_field('pickup_status', array($this, 'show_pickup_status'));
$crud->callback_read_field('frequency', 'show_frequency');
$crud->set_relation('created_by', 'users', '{first_name} {last_name}');
$crud->set_relation('zone_id', 'zones', 'zone_name');
$crud->set_relation('truck_id', 'trucks', 'truck_name');
$crud->display_as('zone_id', 'zone');
$crud->unset_add();
$crud->unset_edit();
$crud->unset_delete();
$crud->add_action('Change Pickup Date', '', "pickups/schedule_pickup");
$crud->add_action('Suspend', '', "pickups/suspend_location");
$crud->callback_before_insert(array($this, 'set_location_details'));
$crud->callback_before_update(array($this, 'set_location_details'));
$crud->display_as('customer_id', 'Customer');
$crud->unset_jquery();
$crud->unset_jquery_ui();
$crud->set_js('assets/scripts/custom_allpickups.js');
//$crud->where("locations.id IN (SELECT location_id FROM pickups WHERE picked_up = FALSE )");
$crud->where("locations.customer_id in (select id from customers where is_approved=1) and locations.is_suspended !=1");
$crud->order_by('next_pickup_date', 'asc');
if($crud->getState() != 'read') {
$config = array(
'table_name'=>'locations',
'related_table'=>'customers',
'sort_field'=>'customer_id',
'categories_primary_key'=>'id',
'related_title_field'=>'organization',
'related_table_fields'=>'organization',
'order_by'=>'organization',
'first_url' => base_url().strtolower(__CLASS__).'/'.__FUNCTION__,
'segment_name' => 'customer',
'style' => 'height:24px; width: 200px;',
'class'=>'',
'text'=>array('all_rows'=>'Search by Customer')
);
$categories = new grocery_crud_categories($crud,$config);
$output = $categories->render();
} else {
$output = $crud->render();
}
$data = array();
$data['active_menu'] = 'Pickups';
$data['page_title'] = 'All Pickups';
$output->data=$data;
$this->load->view('crud',$output);
}catch(Exception $e){
show_error($e->getMessage().' --- '.$e->getTraceAsString());
}
}
Now if u noticed - the one marked up inside the function in red color - is the 1 with an additional where being called up. When i hit a search in here - it omits the field - pickup_status (the one marked in green) but it still failed with the query error message -
Unknown column 'locations.customer_id' in 'IN/ALL/ANY subquery'
SELECT COUNT(*) AS `numrows`
FROM (`locations`)
LEFT JOIN `customers` AS jcb24373b ON `jcb24373b`.`id` = `locations`.`customer_id`
LEFT JOIN `users` AS jdad46b20 ON `jdad46b20`.`id` = `locations`.`created_by`
LEFT JOIN `zones` AS j06342dd7 ON `j06342dd7`.`id` = `locations`.`zone_id`
LEFT JOIN `trucks` AS j0fa304df ON `j0fa304df`.`id` = `locations`.`truck_id`
WHERE locations.`customer_id` IN (SELECT id FROM customers WHERE is_approved=1) AND locations.is_suspended !=1
AND `jcb24373b`.`organization` LIKE '%junk yard%'
OR `j06342dd7`.`zone_name` LIKE '%junk yard%'
HAVING locations.`customer_id` IN (SELECT id FROM customers WHERE is_approved=1) AND locations.is_suspended !=1
Above is the query generated - and the one bold and marked as underline is where i encountered the issue.... this is already there in where statement - GC is again adding it as HAVING statement - is it really necessary as for that because this is where the trouble begins.
I commented the following line and the issue was resolved... meaning - i eliminated the extra having clause for the same where clause
$this->basic_model->having($where[0],$where[1],$where[2]);
(this is the line from the code above in the function ... marked in red there tooo ...)
Do let me know your thoughts behind the same. I am sure u can think up of some better solution on the same
Happy GCing :)
