DataMapper ORM v1.8.1

[eluser]Anestetikas[/eluser]
Found a new problem to deal with.
I have a DB with folowing tables funds -< inventories -< files -< documents
All of these 4 tables has a many to many relations to languages and characteristics tables.

There are like 2k rows in funds 5k in inventories 4mil in files and ~200k in documents.
Every one of these rows has 0 1 or 2 relations to languages and characteristics.

I want to get all related languages and characteristics to specific fund, inventory, file.

If I do or_where_related:

Code:

$kalbos = new Language;
                $kalbos->select('description');
                $kalbos->or_where_related('inventory/fund', 'id', $fondas->id);
                $kalbos->or_where_related('file/inventory/fund', 'id', $fondas->id);
                $kalbos->or_where_related('document/file/inventory/fund', 'id', $fondas->id);
                $kalbos->group_by('id')->get();
                $this->data['kalbos'] = $kalbos;

it does a huge query:

Code:

SELECT  `languages`.`description`
FROM (
`languages`
)
LEFT OUTER JOIN  `inventories_languages` inventories_languages ON  `languages`.`id` =  `inventories_languages`.`language_id`
LEFT OUTER JOIN  `inventories` inventories ON  `inventories`.`id` =  `inventories_languages`.`inventory_id`
LEFT OUTER JOIN  `files_languages` files_languages ON  `languages`.`id` =  `files_languages`.`language_id`
LEFT OUTER JOIN  `files` files ON  `files`.`id` =  `files_languages`.`file_id`
LEFT OUTER JOIN  `inventories` file_inventories ON  `file_inventories`.`id` =  `files`.`inventory_id`
LEFT OUTER JOIN  `documents_languages` documents_languages ON  `languages`.`id` =  `documents_languages`.`language_id`
LEFT OUTER JOIN  `documents` documents ON  `documents`.`id` =  `documents_languages`.`document_id`
LEFT OUTER JOIN  `files` document_files ON  `document_files`.`id` =  `documents`.`file_id`
LEFT OUTER JOIN  `inventories` document_file_inventories ON  `document_file_inventories`.`id` =  `document_files`.`inventory_id`
WHERE  `inventories`.`fund_id` =831
OR  `file_inventories`.`fund_id` =831
OR  `document_file_inventories`.`fund_id` =831
GROUP BY  `languages`.`id`

EXPLAIN:

Code:

+----+-------------+---------------------------+--------+------------------------------+------------------+---------+----------------------------------------------+------+-------------+
| id | select_type | table                     | type   | possible_keys                | key              | key_len | ref                                          | rows | Extra       |
+----+-------------+---------------------------+--------+------------------------------+------------------+---------+----------------------------------------------+------+-------------+
|  1 | SIMPLE      | languages                 | index  | NULL                         | PRIMARY          | 2       | NULL                                         |    1 |             |
|  1 | SIMPLE      | inventories_languages     | ref    | lan_inv_unique,language_id   | lan_inv_unique   | 2       | pagrindas.languages.id                       |   59 | Using index |
|  1 | SIMPLE      | inventories               | eq_ref | PRIMARY                      | PRIMARY          | 2       | pagrindas.inventories_languages.inventory_id |    1 |             |
|  1 | SIMPLE      | files_languages           | ref    | lang_file_unique,language_id | lang_file_unique | 3       | pagrindas.languages.id                       |    1 | Using index |
|  1 | SIMPLE      | files                     | eq_ref | PRIMARY                      | PRIMARY          | 4       | pagrindas.files_languages.file_id            |    1 |             |
|  1 | SIMPLE      | file_inventories          | eq_ref | PRIMARY                      | PRIMARY          | 2       | pagrindas.files.inventory_id                 |    1 |             |
|  1 | SIMPLE      | documents_languages       | ref    | lang_doc_unique,language_id  | lang_doc_unique  | 3       | pagrindas.languages.id                       |  525 | Using index |
|  1 | SIMPLE      | documents                 | eq_ref | PRIMARY                      | PRIMARY          | 4       | pagrindas.documents_languages.document_id    |    1 |             |
|  1 | SIMPLE      | document_files            | eq_ref | PRIMARY                      | PRIMARY          | 4       | pagrindas.documents.file_id                  |    1 |             |
|  1 | SIMPLE      | document_file_inventories | eq_ref | PRIMARY                      | PRIMARY          | 2       | pagrindas.document_files.inventory_id        |    1 | Using where |
+----+-------------+---------------------------+--------+------------------------------+------------------+---------+----------------------------------------------+------+-------------+

From EXPLAIN we can see that it creates 3 temp tables document_files, document_file_inventories and file_inventories.
Query execution:

Code:

34 rows in set (24 min 18.99 sec)

Is there a way to get related languages for each object (its very fast), and then merge them into one $languages object?

EDIT:
Went around with getting ids all_to_array for each object, then merged ids to $langIDs array, then selected once again by where_in('id', $langIDs); Lots of code, but seems to get proper stuff in 0.04sec instead of 25minutes.