Complex custom collection join problem

Question

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

Answer 1

So try this code:

$collection->setOrder('updated_at','DESC');

or

$collection->setOrder('main_table.updated_at','DESC');

if you will have updated_at in the both tables.

And instead of 'untitledprimanet_orders_orders'

use

$collection->getTable('{modelNamespaceFromConfigModule}/{tableEntity}')

Or simple '{modelNamespaceFromConfigModule}/{tableEntity}'.

Otherwise you could have problems with a table prefix.

Answer 2

You should make subqueries like this:

$subselect = new Zend_Db_Select(); // or get it from another collection which is better and easer!

$collection->getSelect()->from($subSelect);