Magento 1.7 – Using an Expression as a Join

collection;join;magento-1.7

I have a query that I'm trying to use in an admin grid. It is summing the values in another table, in a subselect, and then joining on that table:

SELECT 
   main_table.*,
   author_debit_amount
FROM   sales_flat_order_item AS main_table
   INNER JOIN sales_flat_order AS order_table
           ON main_table.order_id = order_table.entity_id
   INNER JOIN admin_user AS author
           ON main_table.author_id = author.user_id
   LEFT JOIN (SELECT author_id, SUM(amount) AS author_debit_amount FROM author_debits
               GROUP BY author_debits.author_id) a 
           ON main_table.author_id = a.author_id
WHERE  ( order_table.created_at >= '2014-08-01 04:00:00' ) 
     AND ( order_table.created_at <= '2014-09-01 03:59:59' )
GROUP  BY main_table.author_id;

My question is, is it possible to write this query using standard magento collections? i.e., $collection->join(…). The subselect is where I'm stuck:

LEFT JOIN (SELECT author_id, SUM(amount) AS author_debit_amount FROM author_debits
               GROUP BY author_debits.author_id) a 
           ON main_table.author_id = a.author_id

Best Answer

By digging into Zend_Db_Select, I was able to figure it out:

$query = 'SELECT author_id, SUM(amount) AS author_debit_amount FROM author_debits GROUP BY author_debits.author_id';
$collection->getSelect()->joinLeft(
    new Zend_Db_Expr('('.$query.')'),
    'main_table.author_id = t.author_id',
    array('author_debit_amount')
);
Related Topic