I have created tables, models etc for an extention and can get collections from each model individually fine.
Here are the tables in question:
orders_table
entity_id | magento_order_id | is_successful
--------- | ---------------- | -------------
orders_history_table
entity_id | parent_id | state | updated_at
--------- | --------- | ----- | ----------
So the history
table relates to the order
table usinghistory.parent_id
and orders.entity_id
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?
Thanks