0

I'm working on a CodeIgniter project that uses grocery-crud.

I'm using this code:

$crud->set_table('dtr');
$crud->set_relation_n_n('branch', 'users_branches', 'branches', 'user_id', 'branch_id', 'name');

In SQL, its equivalent is this:

SELECT `dtr`.*, (SELECT GROUP_CONCAT(DISTINCT branches.name) 
FROM branches 
LEFT JOIN users_branches ON users_branches.branch_id = branches.id WHERE
>> users_branches.user_id = `dtr`.id    
GROUP BY users_branches.user_id) AS branch FROM (`dtr`)

But what I want to happen is this:

SELECT `dtr`.*, (SELECT GROUP_CONCAT(DISTINCT branches.name) FROM branches LEFT JOIN users_branches ON users_branches.branch_id = branches.id WHERE
users_branches.user_id = `dtr`.user_id    
GROUP BY users_branches.user_id) AS branch FROM (`dtr`)

I cannot make it happen the way I need it to because table dtr's primary key is 'id' and not 'user_id'. Besides, changing the primary key would cause a lot of error on other parts of my system. So I need to find a way to code it differently so it would work right.

Please help. Thanks.

ITWitch
  • 1,729
  • 5
  • 20
  • 38
  • have you tried changing the column name of `dtr`.id to `dtr`.user_id? – claw68 Aug 08 '13 at 03:20
  • @GanahanKoAni No. I can't do that because dtr.id and dtr.user_id are both existing fields and both of them have their use in the system. My main problem is with grocery crud's set_relation syntax: set_relation_n_n( string $field_name, string $relation_table, string $selection_table, string $primary_key_alias_to_this_table, string $primary_key_alias_to_selection_table , string $title_field_selection_table [ , string $priority_field_relation ] ) The only field it allows to be connected to the relation table 'users_branches' is the primary key dtr.id. – ITWitch Aug 08 '13 at 05:03

0 Answers0