I have created tables, models etc for an extention and can get collections from each model individually fine.
Here are the tables in question:
entity_id | magento_order_id | is_successful --------- | ---------------- | -------------
entity_id | parent_id | state | updated_at --------- | --------- | ----- | ----------
history table relates to the
order table using
Here is the current code I have to join the 2 collections:
$collection = $this->getCollection(); $collection->getSelect()->join( array('orders' => 'untitledprimanet_orders_orders'), 'orders.entity_id = main_table.parent_id', array('prima_order_id', 'magento_order_entity_id', 'is_successful', 'is_manual') );
Now this joins the history table and orders table fine. The issue I have is that the history table holds multiple records related to an order record e.g an entry in the orders table could have up to 4 records associated to it in the history table.
And where I am stuck is I need to only join the latest entry in the history table to its related order in order to get the latest state. So i need to use the updated_at attribute in the history table to get the latest entry for every item in the orders collection.
Any ideas? I've tried explaining as best as possible but if you need any more details let me know
EDIT I have worked up the raw SQL query i need to create with magento:
select * from ( select distinct orders_history.parent_id, orders_history.response_state from orders_history order by orders_history.created_at desc ) as history_temp join orders_orders on orders_orders.entity_id = history_temp.parent_id group by history_temp.parent_id
I aam performing the Join ok but i cant work out how to add the subquery to the from clause, any ideas?