Grocery CRUD V1.2.2 - Dealing with several set_relation

[eluser]Gilles_T[/eluser]
Hi,

Congratulation for Grocery CRUD module. I have build my own methodology for implementing my admin interface, but gCRUD id definitely a very clever and efficient approach. I just discored it last week.

I did few experiments to implement admin parts based on gCRUD, it seems so far to work. Getting step by step to more complex requests I'm blocked with an error when trying to combine 2 set_relation().

The request was fine with the first set_relation(). When adding a second set_relation() it fails on Where clause as ambiguous. There should be a reference to the table of the column

Code:

$crud = new grocery_CRUD();
    
    $crud->set_theme('datatables');
    $crud->set_table('TN_ESPACE_phase_desc');
    $crud->set_relation('statut'    , 'TN_GEN_statut'  , 'lib_statut');
    $crud->set_relation('espace_id' , 'TN_ESPACE_desc' , 'lib_id');
    $crud->columns('lib' , 'date_deb' , 'date_fin' , 'phase_type' , 'statut','desc');
    $crud->unset_fields('phase_id','us_id' , 'fct_id' , 'date_crea');

    if( $this->session->userdata('esp_id') != 0)
     $crud->where('espace_id' , $this->session->userdata('esp_id') );

    $crud->callback_field('espace_id',array($this,'liste_espace_callback'));
    
    $crud ->display_as('espace_id','Espace de rattachement')
      ->display_as('desc','Descriptif')
      ->display_as('lib','libellé');
    
    $output = $crud->render();
    
    $this->_phases_output($output);

Trapped error :

Code:

SELECT TN_ESPACE_phase_desc.*, jd099aae5.lib_statut AS sd099aae5, j159d32c4.lib_id AS s159d32c4 FROM (TN_ESPACE_phase_desc) LEFT JOIN `TN_GEN_statut` as jd099aae5 ON `jd099aae5`.`statut` = `TN_ESPACE_phase_desc`.`statut` LEFT JOIN `TN_ESPACE_desc` as j159d32c4 ON `j159d32c4`.`espace_id` = `TN_ESPACE_phase_desc`.`espace_id` WHERE `espace_id` = '100'
Column'espace_id' in where clause is ambiguous

Actually should be a `TN_ESPACE_desc`.`espace_id` = '100'

Did I do something wrong, is there something I don't understand ?

Thank for your help

Gilles

[eluser]Gilles_T[/eluser]
Oops, beginner mistake :

I understand now that it's me who has to link the field with its related table in order to avoid the ambiguity in the "where" clause.

Code:

$crud->where('TN_ESPACE_phase_desc.espace_id' , $this->session->userdata('esp_id') );

So it should be like :

Code:

$crud = new grocery_CRUD();
    
    $crud->set_theme('datatables');
    $crud->set_table('TN_ESPACE_phase_desc');
    
    if( $this->session->userdata('esp_id') != 0)
     $crud->where('TN_ESPACE_phase_desc.espace_id' , $this->session->userdata('esp_id') );

    $crud->set_relation('statut'    , 'TN_GEN_statut'  , 'lib_statut');
    $crud->set_relation('espace_id' , 'TN_ESPACE_desc' , 'lib_id');
    $crud->columns('lib' , 'espace_id' , 'date_deb' , 'date_fin' , 'phase_type' , 'statut','desc');
    //$crud->unset_fields('phase_id','us_id' , 'fct_id' , 'date_crea');

    $crud->callback_field('espace_id',array($this,'liste_espace_callback'));
    
    $crud ->display_as('espace_id','Espace de rattachement')
      ->display_as('desc','Descriptif')
      ->display_as('lib','libellé');
    
    $output = $crud->render();
    
    $this->_phases_output($output);

By the way, I also removed the ->unset_fields() that should not come next to ->columns(). gCRUD does not complain but it's rather stupid to mix the 2.

Hope it can help someone.

Cheers