[eluser]Lockzi[/eluser]
Hi,
I'm trying to do a huuuge relation query.
What I'm trying to achieve is something like this:
Code:
$query = $this->db->select('*')
->from('applications')
->join('applications_categories', 'applications.application_id=applications_categories.application_id', 'left')
->join('categories', 'applications_categories.category_id=categories.category_id', 'left')
->join('applications_keys', 'applications.application_id=applications_keys.application_id', 'left')
->join('keys', 'applications_keys.key_id=keys.key_id', 'left')
->join('applications_installations', 'applications.application_id=applications_installations.application_id', 'left')
->join('installations', 'installations.installation_id=applications_installations.installation_id', 'left')
->join('installations_options', 'installations.installation_id=installations_options.installation_id', 'left')
->join('options', 'installations_options.option_id=options.option_id', 'left')
->join('applications_downloadsites', 'applications.application_id=applications_downloadsites.application_id', 'left')
->join('downloadsites', 'applications_downloadsites.downloadsite_id=downloadsites.downloadsite_id', 'left')
->join('downloadsites_scraplocators', 'downloadsites.downloadsite_id=downloadsites_scraplocators.downloadsite_id', 'left')
->join('scraplocators', 'downloadsites_scraplocators.scraplocator_id=scraplocators.scraplocator_id', 'left')
->get();
The problem with the query is it is, is that I get multiple rows whenever there's a one-to-many relation in the JOIN statement.
To illustrate:
An application can have many keys.
To store that, the relation is handled by application_keys table.
application_keys_id application_id key_id
------------------- -------------- ------
1 1 1
2 1 2
This table indicates that application_id 1, has two different keys.
Classic one to many relation so to speak.
Onto the problem.
The current query would now show the first application two times, it doesn't "group" these together.
I would like the output to be:
Code:
Application id: 1
Key_id: 1
Key_id: 2
but it currently generates:
Code:
Application id: 1
Key_id: 1
Application id: 1
Key_id: 2
The problem is greatly simplified since the massive query has the same problem all over, on some instances I get 18 records of the same application because of different tables relation.
One way to solve this could be something like the following:
Code:
$query = $this->db->get('applications');
foreach($query->result_array() as $application)
{
$application['applications_keys'] = $this->db->get_where('applications_keys', array('application_id' => $application['application_id']))->result_array();
foreach($application['applications_keys'] as $app_key)
{
$application['keys'] += $this->db->get_where('keys', array('key_id' => $app_key['key_id']))->result_array();
}
What would be the best way for me to solve this?
I want one record of application to include all the data, not multiple rows.
You could use different join type like inner outer left right up down forward backward, but some applications might be missing different data and therefore not show up at all.
Please let me know if you don't understand, I basically want each result to become an array or object array if it matches more results.
Thanks,
Lockzi
