⚠ 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

set_relation multiple times to same table: Not unique table/alias



Roeland W

Roeland W
  • profile picture
  • Member

Posted 19 March 2014 - 15:52 PM

I'm using datatable theme.

 

When I'd like to use set_relation multiple times to the same sub table. Reason I want this, is that I have a general table with choices ('yes/no/maybe') which I'd like to link to several fields of my main table.

However, this gives a problem in the theme, I get the error

Not unique table/alias

Reason is that following query is being used:

SELECT `deposit`.*, choice_name AS sa9c0a50b, choice_name AS s2c05bb21 FROM (`deposit`) LEFT JOIN `choice` ON `choice_id` = `deposit`.`dp_possibility_to_replenish_deposit_id` LEFT JOIN `choice` ON `choice_id` = `deposit`.`dp_possibility_to_increase_rate_id`

As you can see choice_name is used twice. We need to prefix it with something to make this work, so for instance:

SELECT deposit.*, c1.choice_name AS sa9c0a50b, c2.choice_name AS s2c05bb21 FROM (deposit) LEFT JOIN choice c1 ON c1.choice_id = deposit.dp_possibility_to_replenish_deposit_id LEFT JOIN choice c2 ON c2.choice_id = deposit.dp_possibility_to_increase_rate_id

I have been digging for an hour in the Grocery_crud_model, and I came with following solution:

 

in function get_list():

replace:
 
   else
    {
            $select .= ", $related_field_title AS $unique_field_name";
    }

with:

 
    else
    {
            $select .= ", $unique_join_name.$related_field_title AS $unique_field_name";
    }

in function join_relation(, replace:

 
$this->db->join( $related_table, "$related_primary_key = $table.$field_name",'left');

with:

 
$this->db->join( $related_table.' '.$unique_name, "$unique_name.$related_primary_key = $table.$field_name",'left');

Please advise if I missed some options where this hack is not necessary. R.