I need to make a complex mysql query into a Magento collection to present in an admin grid. Query is:

SELECT g.increment_id AS magento_order_number,
       c.increment_id AS rcs_number,
       g.created_at AS date_entered,
       if(o.iorder_salesrep_email IS NOT NULL
          AND o.iorder_position_id IS NOT NULL, 'iOrder', 'eComm') AS order_system,
       group_concat(concat(oi.sku,' - ',CAST(oi.qty_ordered AS UNSIGNED))) AS products
FROM sales_flat_order_grid g,
     customer_entity c,
     sales_flat_order_item oi,
     sales_flat_order o
WHERE g.entity_id = oi.order_id
  AND c.entity_id = g.customer_id
  AND o.entity_id = g.entity_id
  AND o.store_id != 2
GROUP BY g.entity_id;

and i have absolutely no idea how to make it work. So far i have a couple of joins but im pretty far from the expected results. Any suggestions or resources to handle this?



If you're happy with a slightly less efficient workaround

  1. Set up your_custom_method() in Grid.php, and
  2. Call this method from the _prepareColumns() function, also in Grid.php

You'd need to orientate your grid around a custom model that you predefine. You could then assign this model the fields that you're interested in. The your_custom_method() would hence truncate your model's table on load & repopulate it accordingly.

Granted you use direct SQL statements to do so, this should run in less than 2 seconds for a dataset of < 20000. (From my experience).

  • I tried something similar like passing the raw SQL query result as the collection to the grid and i failed. This task has been postponed for a sprint or so, once i get the chance to try that again i will try this – mbalparda Jul 15 '14 at 14:51
  • 1
    Sure thing, if/when you do, give me a shout if you run into any hassles. I have this exact functionality setup in quite a few instances. – Moose Jul 16 '14 at 12:06

I love converting SQL to Magento collections syntax, don't get me wrong. but I'm on my phone at the moment.

So I'll suggest something else - and I'm probably going to get hammered for this - but here's another way of thinking:

Why not make this a MySQL view and then access it as if it were a flat table? That would allow you to access it as if it were a flat collection. This would probably be considered an antipattern and some may scoff at me - but it's certainly an approach. In my opinion any approach that works for you and is somewhat maintainable is a decent approach.

One criteria that would prevent you from using the MySQL view strategy, though, would be if you need to lazy-load the collection (it is modified downstream).

There is a module coded by Vinai Kopp and the team at Magento Live UK that allows you to set any flat table as an admin grid with just some configuration xml setup:



  • I saw the Autogrid module yesterday too and i think it might serve well, but its complex to add community code to this project. I will try the View approach and let you know. Thank you Phil! – mbalparda Jul 15 '14 at 15:06

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.