How to sort the order of Magento joinLeft

Question

I am trying to use a join query in Magento Abandoned Grid this way:

 $collection->getSelect()->joinLeft(
                        'abandoned_comment_history', 'main_table.customer_id=abandoned_comment_history.customer_id', array('admin_comment', 'comment_datetime', 'status'))
                ->group('main_table.customer_id');

In my custom table abandoned_comment_history there are multiple matches with main table.customer_id & i am getting the first one but i need last one.

Is there any way to get the last match from my custom table. I searched a lot but couldn't find the solution. Hope will get here.

Description: In my table abandoned_comment_history have some data as

id customer_id admin_comment
1     2082      Test 1
2     2082      Test 2
3     2091      Hello

In main_table customer_id column is unique.

  customer_id 
     2082      
     2084      
     2091      

Now if i try to join & get admin_comment value i get "Test 1" but i need "Test 2"

Answer 1

The SQL query would have to look like this:

SELECT * FROM main_table c
    LEFT JOIN abandoned_comment_history h ON c.customer_id=h.customer_id
    LEFT JOIN (
        SELECT customer_id, MAX(id) FROM abandoned_comment_history GROUP BY customer_id
    ) AS m ON h.id = m.id
WHERE 1;

I can't give you a solution with the Magento and Zend methods, but I would try to accomplish this query with $select->join() and a Zend_Db_Expr to use arbitrary SQL expressions.

Answer 2

Try this:

<?php
    $collection->getSelect()->joinLeft(
                        'abandoned_comment_history', 'main_table.customer_id=abandoned_comment_history.customer_id', array('admin_comment', 'comment_datetime', 'status'))
                ->group('main_table.customer_id');

$collection->getSelect()->order('main_table.customer_id ASC');// DESC OR ASC
?>

OR

<?php
    $collection->getSelect()->joinLeft(
                        'abandoned_comment_history', 'main_table.customer_id=abandoned_comment_history.customer_id', array('admin_comment', 'comment_datetime', 'status'))
                ->group('main_table.customer_id');

$collection->setOrder('main_table.customer_id','desc');// desc OR asc
?>