Jump to content


Bug in the search

search

  • Please log in to reply
31 replies to this topic

#1 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 17 August 2013 - 07:03 AM

Hi Team,

 

I noticed there was a bug in the Grocerycrud's search system. What i noticed was if for instance in columns .. i added a field (non-existant from table) field and there i tried performing search, it also tries to search along for that given field which dose not exists and then it throws in error for the same (unknown column)
 

A better alternative here would have been like -

while performing the search, instead of searching in for all the columns set by user, we need to verify whether the column provided is actually in existence or not. If it is, then only go for the same else do not include it in search.



#2 davidoster

davidoster

    Grocery CRUD Ninja

  • Advanced Member
  • PipPipPip
  • 1,068 posts
  • LocationAthens, Greece

Posted 17 August 2013 - 05:49 PM

This is the same for all of the themes?

Are you sure this is a GC search problem of the core and not the way the themes do their search?


____________________________________________________________

 

rtfm_small.jpg---!!!Please read these guidelines before asking to the forums!!!---

____________________________________________________________

 

David Oster aka George Pasparakis,
http://odphotography.com
http://eletter.gr


#3 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 18 August 2013 - 09:08 AM

yes,

this is surely the core library problem.  As it is dealing with the field @the backend / ajaxlist / ajaxlist_info ... It picks up all the elements in the fields .. it dose not compare as whether those fields are actually part of table or not ... it just includes it as part of search and that is where the issue is. This is where i was suggesting a possibility of checking whether this field actually exists or not ... or to be more precise .. is this field i am trying to add.. is it a part of the table or not..



#4 davidoster

davidoster

    Grocery CRUD Ninja

  • Advanced Member
  • PipPipPip
  • 1,068 posts
  • LocationAthens, Greece

Posted 18 August 2013 - 04:14 PM

yes,

this is surely the core library problem.  As it is dealing with the field @the backend / ajaxlist / ajaxlist_info ... It picks up all the elements in the fields .. it dose not compare as whether those fields are actually part of table or not ... it just includes it as part of search and that is where the issue is. This is where i was suggesting a possibility of checking whether this field actually exists or not ... or to be more precise .. is this field i am trying to add.. is it a part of the table or not..

Post it on github then as an issue. This affects all versions or just the last one? Make sure you mention this when you post the issue.


____________________________________________________________

 

rtfm_small.jpg---!!!Please read these guidelines before asking to the forums!!!---

____________________________________________________________

 

David Oster aka George Pasparakis,
http://odphotography.com
http://eletter.gr


#5 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 10 September 2013 - 07:41 AM

Hi team,

 

I was looking for this solution and finally found it... i was wanting to perform the search desperately but failing to do with the existing issue of GC. Hence i finally updated the function and made the relevant changes.

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);
                    }
                }
            }
        }
    }

I have tested with Flexigrid.. (i by default use this single theme only) ... and it is functional / working smoothly - without much trouble on the same. Further improvements are surely invited. But as for now.. any1 who wants to make use of this newly modified functionality .. can surely take it to their advantage.

 

And David - have posted the same solution to the github too :)

 

Happy GCing...



#6 davidoster

davidoster

    Grocery CRUD Ninja

  • Advanced Member
  • PipPipPip
  • 1,068 posts
  • LocationAthens, Greece

Posted 10 September 2013 - 10:12 AM


And David - have posted the same solution to the github too 

:)

Happy GCing...

 

hahahahahahahahaha! ok!!


____________________________________________________________

 

rtfm_small.jpg---!!!Please read these guidelines before asking to the forums!!!---

____________________________________________________________

 

David Oster aka George Pasparakis,
http://odphotography.com
http://eletter.gr


#7 goFrendiAsgard

goFrendiAsgard

    Advanced Member

  • Members
  • PipPipPip
  • 137 posts

Posted 15 October 2013 - 06:58 AM

Amit Shah Great job. Because of your code, I don't need to spend hours to solve this


No-CMS homepage : http://www.getnocms.com
No-CMS forum : http://www.getnocms.com/forum
No-CMS Github page : https://github.com/g...diAsgard/No-CMS

 

Please don't mix up No-CMS and groceryCRUD issues.
(eg: user authentication etc is No-CMS specific issue and has nothing to do with groceryCRUD)

 


#8 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 16 October 2013 - 01:13 PM

Happy to share :)



#9 hariprasath

hariprasath

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 03 January 2014 - 11:02 AM

Hi Amit Shah

 

 

Please text me, how and where i put this code. i am using the flexigrid theme. This was very useful for me.

 

Thnanks



#10 mau

mau

    Newbie

  • Members
  • Pip
  • 9 posts
  • LocationVicenza (Italy)

Posted 31 January 2014 - 10:38 PM

Hello

 

I'm new in grocery CRUD but, and this is an example how great is this software, I already completed a project.

All well with the code but I have a problem with search.

I'm using by default flexigrid theme: now it happens that if I use $crud->set_relation() in a method in my controller and then I do a search, this fails.

I did some test over different methods; if I put into a comment all the calls of $crud->set_relation(), search works well.

But the list rendered isn't what I want. If all the code is active ($crud->set_relation() outside of comments) the list is ok but the search works partially or at all.

I tryed to find a common behaviour in the methods to understand what's the reason that $crud->set_relation() interferes with the search option but I'm not enough smart to do that.

I kindly ask if some one can help me to solve this problem or to help me to point in the right direction.

 

Best regards.

Mau

 

 

 

 



#11 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 05 February 2014 - 10:39 AM

Well this is interesting - i never encountered the issue as such but i surely will give it a shot / tryout and update u in case i find relevant solution. Be in my touch with personal message ... i am a bit busy with overloaded project work this days .. so i will be able to look up on Sunday or next week someday.. do message me a reminder for the same so i will lookup and share the solution if i found 1



#12 mau

mau

    Newbie

  • Members
  • Pip
  • 9 posts
  • LocationVicenza (Italy)

Posted 06 February 2014 - 11:31 PM

Hi Amit Shah

 

Thanks a lot for your answer.

To give you an idea where was born the problem, i add an example of a function that I created in my controller and that has this behaviour.

	public function mostra_compat()
	{
		$session_tipo = $this->session->userdata('menuTipo');
		if($this->_session_is_ok($session_tipo))
		{
			$this->_connselDB($session_tipo);
			$crud = new grocery_CRUD();

			$crud->set_table('cszA_anacomp');
			$crud->set_relation('cod_evento','cszA_anaeventi','descrizione');
			$crud->display_as('cod_evento','Stato');
			$crud->set_relation('id_gest','cszA_anagest','nome');
			$crud->set_relation('id_gest_saldo','cszA_anagest','nome');
			$crud->display_as('id_gest','Gestore');
			$crud->display_as('id_gest_saldo','Gestore saldo');
			$crud->display_as('serial_num','Numero di serie');
			$crud->display_as('automatic_ident','Auto id');
			$crud->set_subject('Compattatori');
//*** aggiunto $crud->callback_field() per condizionare la dropdown list della select
			$crud->callback_field('id_gest_saldo',array($this,'_mod_select_callback'));
//*** eliminato unset_edit per id_gestore_saldo
			$crud->required_fields('serial_num','indirizzo','id_gest','id_gest_saldo');
			$crud->columns('serial_num','indirizzo','ubicazione','tipo','id_gest','id_gest_saldo','last_update','automatic_ident','cod_evento','note');
			$crud->callback_column('note',array($this,'_cut_note'));

			$output = $crud->render();
			$dati = array();
			$dati['sitepath'] = 'Compattatori';
			$output->dati=$dati;
			$this->_example_output($output);
		}
	}

I stay in touch.

Best regards.

 

Mau

 

 

 

 



#13 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 10 February 2014 - 06:42 AM

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 :)



#14 connard

connard

    Member

  • Members
  • PipPip
  • 16 posts

Posted 03 March 2014 - 02:43 PM

Hey Amit, I'm facing the same problem, I'm adding custom columns that are populated with functions, and it breaks the sorting and the search. Is your fix added in latest version of GC or we still need to patch it manually ?



#15 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 03 March 2014 - 07:56 PM

We still need to patch it manually.. i have not added the same.. you surely can add it ... sorry am not good at patching it up.. need to learn on the same :)



#16 connard

connard

    Member

  • Members
  • PipPip
  • 16 posts

Posted 05 March 2014 - 02:58 PM

Hey Amit, I added it and it seems that my custom columns are still not working.. any idea ?

 

I can't search and can't order by column. I modified the code according to your #5 post. Do I need something else ?



#17 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 05 March 2014 - 08:01 PM

What exactly you trying to do.. trying to search / order on custom column? Well if so.. this is not the code for it.. this is to make GC understand the fields that are not part of table and exclude them from search and not to search / order on the same...

 

if u apply the code and try to avoid he search on custom column and if u still having trouble..... u provide me the dump from firebug .. it will help understand the issue / trouble...!! thn we can fix the same.



#18 connard

connard

    Member

  • Members
  • PipPip
  • 16 posts

Posted 05 March 2014 - 10:24 PM

Well I have both problems you mentioned:

1) I was indeed trying to search/order on custom columns, so you can confirm that it is not currently possible. :/

2) Because of these custom columns, and despite the fact I added your code, I can sort but not search on "real" column values of this table (filter simply don't apply). I will try to get a firebug dump :)

 

 

3) I wanted to do something like this (I take a simple example) :

 

lets say I have 3 tables (buildings, floors, and rooms).

rooms have a 1-1 relation to floors, and floors has a 1-1 relation on buildings. I want to show the rooms table rows and in them show the building name. I do it using a callback_column that use a custom function to return building name. And i would like to be able to sort/search in this field. Any idea? Maybe it's just not possible :)



#19 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 06 March 2014 - 09:22 AM

Well i have a simpler solution but that leads to denormalization of the stuff.. u can add building id to the room itself... and in gc.. u have an extension for dependent select extension. Here  with this u can make the user select the building / floor for creating a room (if at all required. Else with the selection of floor id, the building id can be retrieved and set).

Advantage- u don't need custom column callback, u can sort, u can search ... do everything u want ...

Disadvantage - an extra field leading to denormalization.

 

But the advantage i believe is greater here then disadvantage.

 

Happy GCing :)



#20 connard

connard

    Member

  • Members
  • PipPip
  • 16 posts

Posted 06 March 2014 - 02:26 PM

Thanks Amit, yes you are right maybe the most efficient is to break the rules sometimes :) I will do it.

 

But I found a new bug with search function.

 

If you add a condition in GC, typically a "or_like", then you can not search anymore. Is it normal ?







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users