2

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
6

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')
);
1

Step1:primary item collection of order item:

  $collection = Mage::getResourceModel('sales/order_item_collection');

Step2: sales_flat_order table inner join by below code

 $collection ->joinInner(
                array('order_table' =>'sales_flat_order'),
                'main_table.order_id = order_table.entity_id',
                array());

Step3: admin_user table inner join by below code

$collection ->joinInner(
                array('author' =>'admin_user'),
                'main_table.author_id = author.user_id',
                array());

Step4:author_debit_amount table left join

$collection ->getSelect() ->joinLeft( array('a' => 'author_debits'), ' main_table.author_id = a.author_id' ->columns('SUM(a.amount) as author_debit_amount ,a.author_id') ->group('a.author_id');

  • That would do the sum in the select rather than using the sum as a join. But, I may use this elsewhere in my project. Thanks! – Laura Sep 8 '14 at 16:01

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.