[eluser]daparky[/eluser]
Hi everyone,
I have a slight issue joining multiple tables and getting multiple rows from those table into an array or object.
Basically i have 5 tables that all need to be joined together. It looks something like this:-
table1
-table1_id
-table1_name
table2
-table2_id
-table2_name
-t2_table1_id
table3
-table3_id
-table3_name
-t3_table1_id
table4
-table4_id
-table4_name
-t4_table1_id
table5
-table5_id
-table5_name
-t5_table1_id
Lets say table 4 has multiple rows for the same id in table1. At the moment my output looks like this:-
Code: Array
(
[0] => stdClass Object
(
[table1_id] => an id
[table1_name] => a name
[table2_name] => a name
[table3_name] => a name
[table5_name] => a name
[table4_name] => a name
)
)
Table4 has multi rows with the same table1_id so i want them included into the array like this:-
Code: Array
(
[0] => stdClass Object
(
[table1_id] => an id
[table1_name] => a name
[table2_name] => a name
[table3_name] => a name
[table5_name] => a name
[table4_name] => Array
(
[0] => stdClass Object
(
[table4_name] => a name
)
[1] => stdClass Object
(
[table4_name] => a name
)
)
)
)
Here's my AR query:-
Code: $this->db->select('*');
$this->db->join('table2', 'table1_id = t2_table1_id', 'left');
$this->db->join('table3', 'table1_id = t3_table1_id', 'left');
$this->db->join('table5', 'table1_id = t5_table1_id', 'left');
$this->db->join('table4', 'table1_id = t4_table1_id', 'left');
$this->db->group_by('table1');
return $this->db->get('table1')->result();
It's returning the results properly it just doesn't get multi values from table 4 if there are any, it seems to only return the first row it finds.
Any help would be appreciated.
[eluser]vitoco[/eluser]
group_by COLUMN, returns only one row per value of the column. removing the group by will get you all rows from table4..
[eluser]daparky[/eluser]
I tried removing group by and the results duplicated to how many rows I had in table 4. The results look like this after removing group by:-
Code: Array
(
[0] => stdClass Object
(
[table1_id] => an id
[table1_name] => a name
[table2_name] => a name
[table3_name] => a name
[table5_name] => a name
[table4_name] => a name
)
[1] => stdClass Object
(
[table1_id] => an id
[table1_name] => a name
[table2_name] => a name
[table3_name] => a name
[table5_name] => a name
[table4_name] => a name2
)
[2] => stdClass Object
(
[table1_id] => an id
[table1_name] => a name
[table2_name] => a name
[table3_name] => a name
[table5_name] => a name
[table4_name] => a name3
)
)
The results need to be like those demonstrated in the first post.
[eluser]WanWizard[/eluser]
You can't do that with a single query.
You're trying to convert a flat structure (table of rows) into a hierarchical structure. You will have to do that in code, i.e. loop through the result, and collapsing it while you loop...
[eluser]daparky[/eluser]
[quote author="WanWizard" date="1277925605"]You can't do that with a single query.
You're trying to convert a flat structure (table of rows) into a hierarchical structure. You will have to do that in code, i.e. loop through the result, and collapsing it while you loop...[/quote]
Thanks alot WanWizard for pointing me in the right directing. Do you have any example code you could show me i could maybe base this off.
Also, will this take a hit on performance looping through the query etc? Are there any other ways of doing it, e.g. sub query?
[eluser]WanWizard[/eluser]
Maybe it's better if you try to explain what you intend to do with such a structure, because it seems to me that it is quite complicated to parse.
why not: Code: Array
(
[0] => stdClass Object
(
[table1_id] => an id
[table1_name] => a name
[table2_name] => a name
[table3_name] => a name
[table5_name] => a name
[table4_name] => Array
(
[0] => a name
[1] => a name2
[2] => a name3
)
)
)
[eluser]WanWizard[/eluser]
You might be able to do something like
Code: $this->db->select('table1.*, GROUP_CONCAT(DISTINCT table1_name), GROUP_CONCAT(DISTINCT table2_name), GROUP_CONCAT(DISTINCT table3_name), GROUP_CONCAT(DISTINCT table5_name), GROUP_CONCAT(DISTINCT table4_name)', FALSE);
$this->db->join('table2', 'table1_id = t2_table1_id', 'left');
$this->db->join('table3', 'table1_id = t3_table1_id', 'left');
$this->db->join('table5', 'table1_id = t5_table1_id', 'left');
$this->db->join('table4', 'table1_id = t4_table1_id', 'left');
$this->db->group_by('table1');
return $this->db->get('table1')->result();
[eluser]daparky[/eluser]
[quote author="WanWizard" date="1277932335"]You might be able to do something like
Code: $this->db->select('table1.*, GROUP_CONCAT(DISTINCT table1_name), GROUP_CONCAT(DISTINCT table2_name), GROUP_CONCAT(DISTINCT table3_name), GROUP_CONCAT(DISTINCT table5_name), GROUP_CONCAT(DISTINCT table4_name)', FALSE);
$this->db->join('table2', 'table1_id = t2_table1_id', 'left');
$this->db->join('table3', 'table1_id = t3_table1_id', 'left');
$this->db->join('table5', 'table1_id = t5_table1_id', 'left');
$this->db->join('table4', 'table1_id = t4_table1_id', 'left');
$this->db->group_by('table1');
return $this->db->get('table1')->result();
[/quote]
WanWizard, this query worked to a certain degree, it output this:-
Code: Array
(
[0] => stdClass Object
(
[table1_id] => an id
[table1_name] => a name
[GROUP_CONCAT(DISTINCT table2_name)] => a name2
[GROUP_CONCAT(DISTINCT table3_name)] => a name3
[GROUP_CONCAT(DISTINCT table5_name)] => a name5
[GROUP_CONCAT(DISTINCT table4_name)] => aname1,aname2,aname3
)
)
Ideally i would like results to appear like you said:-
Code: Array
(
[0] => stdClass Object
(
[table1_id] => an id
[table1_name] => a name
[table2_name] => a name
[table3_name] => a name
[table5_name] => a name
[table4_name] => Array
(
[0] => a name
[1] => a name2
[2] => a name3
)
)
)
Getting very close to solving this issue 
[eluser]vitoco[/eluser]
if you apply explode in the loop to
Code: $row['GROUP_CONCAT(DISTINCT table4_name)'] = explode(',' , $row['GROUP_CONCAT(DISTINCT table4_name)']);
you'll get and array of table4_nameS.
Also try to add some alias to every GROUP_CONCAT statement like
Code: $this->db->select('table1.*, GROUP_CONCAT(DISTINCT table1_name) AS table1_name ...');
it gets easier to read and process
Saludos
|
