⚠ 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

Multiple Relation on the same table



a.lazzarini
  • profile picture
  • Member

Posted 09 July 2019 - 15:20 PM

Hi i need to setRelation on same two table with different where like this:

        $crud->setRelation('aimm_tipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'TIP_IMMOB']);
        $crud->setRelation('aimm_subtipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'SUBTIP_IMMOB']);
        $crud->setRelation('aimm_situazione_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'SIT_IMMOB']);

 

But i receive thi serror:

 

Message: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'tbl_dati_sistema'

 

I'm using grocery crud enterprise 2.7.6
 

 

 

Regards

Andrea


web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 10 July 2019 - 15:56 PM

Hi i need to setRelation on same two table with different where like this:

        $crud->setRelation('aimm_tipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'TIP_IMMOB']);
        $crud->setRelation('aimm_subtipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'SUBTIP_IMMOB']);
        $crud->setRelation('aimm_situazione_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'SIT_IMMOB']);

 

But i receive thi serror:

 

Message: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'tbl_dati_sistema'

 

I'm using grocery crud enterprise 2.7.6
 

 

 

Regards

Andrea

Hello Andrea and thank you for sending this,

 

This seems like a duplicate of this forum post: /topic/4338-error-in-setrelation-when-two-relations-point-to-same-table-and-both-use-where-clause/

And yes! I confirm that this is a bug!

 

I will try to fix that for the next version 2.7.8

 

You can also check the progress of the ticket from here: https://trello.com/c/tzXt4LAU/32-gc-310-bug-with-setrelation-when-we-are-using-the-same-table-name-with-a-where

 

I will inform you once this is done.

Regards

Johnny


web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 11 July 2019 - 12:54 PM

Hi i need to setRelation on same two table with different where like this:

        $crud->setRelation('aimm_tipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'TIP_IMMOB']);
        $crud->setRelation('aimm_subtipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'SUBTIP_IMMOB']);
        $crud->setRelation('aimm_situazione_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'SIT_IMMOB']);

 

But i receive thi serror:

 

Message: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'tbl_dati_sistema'

 

I'm using grocery crud enterprise 2.7.6
 

 

 

Regards

Andrea

Hello Andrea

 

I am glad to inform you that the issue with the setRelation bug is now solved on version 2.7.8

In case you are intrested you can download the latest version from here: https://www.grocerycrud.com/users/enterprise_latest_version

Please let me know in case you are still having issues

 

Regards

Johnny


a.lazzarini
  • profile picture
  • Member

Posted 13 July 2019 - 06:27 AM

Hi jonni.

Now there is no error but there is not results

 

Here is a example:

my original query:

 $this->db->select('*, ......  t_imm.tbds_destab_txt,sub_t_imm.tbds_destab_txt,.....');

$this->db->join('tbl_dati_sistema  t_imm', 't_imm.tbds_keytab_txt = imm.aimm_tipologia_code and t_imm.tbds_tblname_txt = "TIP_IMMOB"', 'LEFT');
 $this->db->join('tbl_dati_sistema sub_t_imm', 'sub_t_imm.tbds_keytab_txt = imm.aimm_tipologia_code and sub_t_imm.tbds_tblname_txt = "SUBTIP_IMMOB"', 'LEFT');

 

That work fine

 

This my crocery porting:

 

.

.

.

        $crud->setPrimaryKey('tbds_destab_txt', 'tbl_dati_sistema');
        $crud->setRelation('aimm_tipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'TIP_IMMOB']);
        $crud->setRelation('aimm_subtipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'SUBTIP_IMMOB']);

.

.

.

 

Regards

Andrea

 


web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 13 July 2019 - 20:55 PM

Hi jonni.

Now there is no error but there is not results

 

Here is a example:

my original query:

 $this->db->select('*, ......  t_imm.tbds_destab_txt,sub_t_imm.tbds_destab_txt,.....');

$this->db->join('tbl_dati_sistema  t_imm', 't_imm.tbds_keytab_txt = imm.aimm_tipologia_code and t_imm.tbds_tblname_txt = "TIP_IMMOB"', 'LEFT');
 $this->db->join('tbl_dati_sistema sub_t_imm', 'sub_t_imm.tbds_keytab_txt = imm.aimm_tipologia_code and sub_t_imm.tbds_tblname_txt = "SUBTIP_IMMOB"', 'LEFT');

 

That work fine

 

This my crocery porting:

 

.

.

.

        $crud->setPrimaryKey('tbds_destab_txt', 'tbl_dati_sistema');
        $crud->setRelation('aimm_tipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'TIP_IMMOB']);
        $crud->setRelation('aimm_subtipologia_code', 'tbl_dati_sistema', 'tbds_destab_txt', ['tbds_tblname_txt' => 'SUBTIP_IMMOB']);

.

.

.

 

Regards

Andrea

Hello @a.lazzarini,

 

Can you please share a screenshot of where you are not getting any results? Unfortunately I can't reproduce your issue :(

Do you mean at the datagrid or the relation dropdown?

Regards

Johnny