How to LEFT-JOIN 'catalog/category' fields to 'sales/order_item' collection?

Question

I'm trying to left-join some of the 'catalog/category' attributes to a 'sales/order_item' collection. For example: a satisfying result will let me add the category-name to the order-item data.

*I know I can use the categories flat table, but I need to know if it's possible to join the EAV-collection and how.

**It must be done with one mysql query (Not by looping through the order items or something like that...)

***I know that it might lead to duplicated rows if the product has more than 1 category, that's exactly what I need.

Answer 1

I know that it might lead to duplicated rows if the product has more than 1 category, that's exactly what I need.

Then it is not possible. Internally, collections use an array for the loaded items with the entity id as key. If the SQL query returns several rows with the same entity id, you'll get an error "Item with same id already exists".

So to accomplish your task you will have to work with the SQL query directly, and if you need Magento models, instantiate them yourself.

You still can prepare the query using a collection as far as it is possible, but as soon as it comes to the left join, continue working on the Zend_Db_Select object returned by $collection->getSelect() and do not load the collection itself.