⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Problem with Search and set_relation



ypazval
  • profile picture
  • Member

Posted 17 March 2015 - 23:01 PM

Hi! I have a problem :(

 

I have the following controller

public function adultos()
    {
	
		$crud = new grocery_CRUD();
		
		$crud->set_language('spanish'); 
        $crud->set_table('adultos');
		$crud->set_subject('Adultos');
		
		$crud->fields('foto_acudiente','nombre_acudiente', 'primer_apellido', 'segundo_apellido','ID_tipo_documento','numero_documento','telefono_1','telefono_2','telefono_3','direccion_1','correo_electronico','profesion','empresa','cargo','alumnos','ID_parentesco');
		$crud->columns('foto_acudiente','nombre_acudiente', 'primer_apellido', 'segundo_apellido','telefono_1','telefono_2','telefono_3','direccion_1','correo_electronico','profesion','empresa','cargo','alumnos');
		$crud->set_relation('ID_parentesco','parentescos','{parentesco}');
		$crud->set_relation('ID_tipo_documento','tipo_documento','{tipo_documento}');
		$crud->set_relation_n_n('alumnos', 'alumnos_acudientes', 'alumnos', 'ID_acudiente', 'ID_alumno', '{nombre_alumno} {primer_apellido} {segundo_apellido}','priority');
		
		$crud->set_field_upload('foto_acudiente','assets/uploads/files');
		
		$crud->display_as('telefono_1','Teléfono Celular');
		$crud->display_as('telefono_2','Teléfono Casa');
		$crud->display_as('telefono_3','Teléfono Oficina');
		$crud->display_as('profesion','Profesión');
		$crud->display_as('direccion_1','Dirección');
		$crud->display_as('ID_parentesco','Parentesco');
		$crud->display_as('ID_tipo_documento','Tipo documento');
		
		$crud->required_fields('nombre_acudiente','ID_parentesco');	
		$crud->form_validation()->set_message('required','El campo %s es obligatorio');
		
		$ID = isset($_SESSION['ID_perfil']) ? $_SESSION['ID_perfil'] : NULL; 
		if ($ID == 3) {
			$crud->unset_add();
			$crud->unset_delete();
			$crud->unset_edit();
		}
		$crud->unset_columns('ID_parentesco','direccion_1','numero_documento');  
		
		$output = $crud->render();
         $this->_vista($output);   
		
			
	}
	

and when i see the view...

 

error1.png

The Search don´t work!!! It happens with all the CRUD's with set_relation. With CRUD's without this instruction is working perfectly

 

I´m working with Codeingniter 2.2 -GroceryCrud 1.5.0, - PHP/5.6.3 - MySQL 5.6.21

 

Please, help me :'(


XemmeX
  • profile picture
  • Member

Posted 08 May 2015 - 12:16 PM

Hello,
 
We hit on the same bug and we notice that it was related to the fact that we set_relation twice to the same table and it is what generated the error !
The bug is deep into the core of the Grocery Crud that generate on the fly the unique id for the table. The problem is that when we set 2 or more relation to a table, it create a collision because there is more then one relation set with the same unique id.

A Database Error Occurred

Error Number: 1052

Column 'fabric_id' in where clause is ambiguous

SELECT `fasp_inspection`.*, CONCAT('', COALESCE(j62188325.fabric_id, ''), ' - ', COALESCE(j62188325.item_lg, ''), ' : ', COALESCE(j62188325.item_lg_name, ''), '') as s62188325, CONCAT('', COALESCE(j754be067.status_id, ''), ' - ', COALESCE(j754be067.desc_en, ''), '') as s754be067, CONCAT('', COALESCE(jcdca5ced.status_id, ''), ' - ', COALESCE(jcdca5ced.desc_en, ''), '') as scdca5ced, CONCAT('', COALESCE(j8e48e56d.discoloration_status_id, ''), ' - ', COALESCE(j8e48e56d.desc_en, ''), '') as s8e48e56d, CONCAT('', COALESCE(j855ffbf6.status_id, ''), ' - ', COALESCE(j855ffbf6.desc_en, ''), '') as s855ffbf6, CONCAT('', COALESCE(jd5ca3224.face_status_id, ''), ' - ', COALESCE(jd5ca3224.desc_en, ''), '') as sd5ca3224, CONCAT('', COALESCE(j7497c72b.inspection_status_id, ''), ' - ', COALESCE(j7497c72b.desc_en, ''), '') as s7497c72b, CONCAT('', COALESCE(j4c79c540.test_type_id, ''), ' - ', COALESCE(j4c79c540.desc_en, ''), '') as s4c79c540, CONCAT('', COALESCE(jd6496e1d.status_id, ''), ' - ', COALESCE(jd6496e1d.desc_en, ''), '') as sd6496e1d, CONCAT('', COALESCE(j5aac1bff.transfer_status_id, ''), ' - ', COALESCE(j5aac1bff.desc_en, ''), '') as s5aac1bff, CONCAT('', COALESCE(jae43f9d0.fabric_id, ''), '  |  ⇩ LOWER: ', COALESCE(jae43f9d0.stretch_chain_lower, ''), '  |  ☉ TARGET: ', COALESCE(jae43f9d0.stretch_chain_target, ''), '  |  ⇧ UPPER: ', COALESCE(jae43f9d0.stretch_chain_upper, ''), '') as sae43f9d0 FROM (`fasp_inspection`) LEFT JOIN `fasp_fabric` as j62188325 ON `j62188325`.`fabric_id` = `fasp_inspection`.`fabric` LEFT JOIN `fasp_status` as j754be067 ON `j754be067`.`status_id` = `fasp_inspection`.`color_fastness_status` LEFT JOIN `fasp_status` as jcdca5ced ON `jcdca5ced`.`status_id` = `fasp_inspection`.`color_finition` LEFT JOIN `fasp_discoloration_status` as j8e48e56d ON `j8e48e56d`.`discoloration_status_id` = `fasp_inspection`.`discoloration_status` LEFT JOIN `fasp_status` as j855ffbf6 ON `j855ffbf6`.`status_id` = `fasp_inspection`.`dwr_status` LEFT JOIN `fasp_face_status` as jd5ca3224 ON `jd5ca3224`.`face_status_id` = `fasp_inspection`.`face` LEFT JOIN `fasp_inspection_status` as j7497c72b ON `j7497c72b`.`inspection_status_id` = `fasp_inspection`.`inspection_status` LEFT JOIN `fasp_test_type` as j4c79c540 ON `j4c79c540`.`test_type_id` = `fasp_inspection`.`test_type` LEFT JOIN `fasp_status` as jd6496e1d ON `jd6496e1d`.`status_id` = `fasp_inspection`.`quality_control` LEFT JOIN `fasp_transfer_status` as j5aac1bff ON `j5aac1bff`.`transfer_status_id` = `fasp_inspection`.`transfer_status` LEFT JOIN `fasp_fabric` as jae43f9d0 ON `jae43f9d0`.`fabric_id` = `fasp_inspection`.`stretch_chain_value` WHERE `fabric_id` LIKE '%mx%' OR `item_lg` LIKE '%mx%' OR `item_lg_name` LIKE '%mx%' LIMIT 10

Filename: /home/lgcrud/public_html/models/grocery_crud_model.php

Line Number: 87
 
You probably hit on something like : Column 'your_field_id' in where clause is ambiguous
Which is our case !
 
If someone have a solution for this problem, it would be very appreciated!  :blink:

skekeu
  • profile picture
  • Member

Posted 08 May 2015 - 16:55 PM

I had a similar problem some weeks ago.

And solved using getState to disable the set_relation on the list page ... worked for me.

$state = $crud->getState();
	if($state == 'add' || $state == 'edit' || $state == 'read')
		    {
        $crud->set_relation('uf','estados','SIGLA');
        $crud->set_relation('cidade','cidades','NOME');
    }

In my case there was no relation column on the list screen, so I had no issue.


XemmeX
  • profile picture
  • Member

Posted 08 May 2015 - 18:43 PM

I had a similar problem some weeks ago.

And solved using getState to disable the set_relation on the list page ... worked for me.

$state = $crud->getState();
	if($state == 'add' || $state == 'edit' || $state == 'read')
		    {
        $crud->set_relation('uf','estados','SIGLA');
        $crud->set_relation('cidade','cidades','NOME');
    }

In my case there was no relation column on the list screen, so I had no issue.

Thank works fine for us too! :)


Blair Jersyer
  • profile picture
  • Member

Posted 01 April 2016 - 16:05 PM

I just created an account to testify that it worked for me too.


ypazval
  • profile picture
  • Member

Posted 07 September 2016 - 22:12 PM

sorry for the lateness , thanks for the solution

;)


richb201
  • profile picture
  • Member

Posted 11 August 2020 - 21:38 PM

I has been many years since this solution was unearthed, but it worked for me too. THANK YOU!