1

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"

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.

| improve this answer | |
0

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
?>
| improve this answer | |
  • Nopes, I added more details in my question. If some how i can sort by id while joining then i could get the result. – Akhil Gupta Jan 21 '16 at 4:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.