Blog

Working with admin grids is fine in Magento when dealing with a collection that selects data from one table, however when you start to introduce more complex SQL when generating your collection – such as Joins or Unions – things start to get a bit more complicated. Whilst performing these Unions and Joins are easily done with Magento Collections, it often breaks either the sorting and filtering functionality of the grid – or both! What we really need to be able to do in this situation is make Magento as blissfully ignorant as possible with regards to the true source of the data it is looking at, and this is where SQL Views come in. Firstly let’s take a look at why this problem exists and an example on how you would usually go about fixing it.

Let’s say that you are starting with the order item table and you wish to join the order table to it, you will have a _prepareCollection function that looks not dissimilar to this:

protected function _prepareCollection()
{
   $collection = Mage::getResourceModel('sales/order_item_collection');
 
   $collection->getSelect()->join(
       array('order' => $collection->getTable('sales/order')),
       'main_table.order_id=order.entity_id',
       array('increment_id', 'customer_firstname', 'customer_lastname', 'order_created_at' => 'created_at')
   );
 
   $this->setCollection($collection);
 
   return parent::_prepareCollection();
}

If we put a breakpoint at the end of the function and look at the SQL of the collection using:

$collection->getSelect()->__toString();

We will see the following SQL that is used to retrieve the data for the collection

SELECT `main_table`.*, 
       `order`.`increment_id`, 
       `order`.`customer_firstname`, 
       `order`.`customer_lastname`, 
       `order`.`created_at` AS `order_created_at` 
FROM   `sales_flat_order_item` AS `main_table` 
       INNER JOIN `sales_flat_order` AS `order` 
               ON main_table.order_id = ORDER.entity_id;

As we can see, Magento is referring to the sales_flat_order_item table as ‘main_table’. Now let’s say that we try to use Magento’s default filtering to try to filter by a field that is joined from the sales_flat_order_item table, in this case we will pick the created_at field. Putting a breakpoint at the end of the _setFilterValues function in Mage_Adminhtml_Block_Widget_grid we can see the SQL that is generated once all filters have been applied:

SELECT `main_table`.*, 
       `order`.`increment_id`, 
       `order`.`customer_firstname`, 
       `order`.`customer_lastname`, 
       `order`.`created_at` AS `order_created_at` 
FROM   `sales_flat_order_item` AS `main_table` 
       INNER JOIN `sales_flat_order` AS `order` 
               ON main_table.order_id = ORDER.entity_id 
WHERE  ( `order_created_at` >= '2016-02-12 00:00:00' 
         AND `order_created_at` <= '2016-02-26 23:59:59' );

Notice the WHERE clause, it is clear at this point that Magento does not know that our order_created_at field is being joined from the order table, and letting this query run to completion will result in a SQLSTATE error, complaining that the column does not exist. Typically we would fix this by implementing a customer filter callback function that looks something like this:

function filterOrderDate($collection, $column)
{
   $value = $column->getFilter()->getValue();
   if(!$value) return;
   $cond = $column->getFilter()->getValue($column->getIndex()) ? $column->getFilter()->getValue($column->getIndex()) : $value;
   $this->getCollection()->addFieldToFilter('order.created_at', $cond);
}

And referencing it when we add the column:

$this->addColumn('order_date', array(
            'header'=> Mage::helper('sales')->__('Purchased On'),
            'width' => '80px',
            'type'  => 'datetime',
            'index' => 'order_created_at',
            'is_system' => true,
            'frame_callback' => array($this, 'renderOrderDate'),
            'filter_condition_callback' => array($this, 'filterOrderDate')
        ));

SQL Views allow you to perform all of these Joins and Unions outside of Magento, generating what we can think of as a ‘Virtual Table’ that is populated by a SQL query, specified at creation. When pointing a Collection in Magento at this table it is none the wiser, it looks at this View the same it would any other table. What this means for us is that all that standard Magento sorting and filtering remains intact and working as it should!

Let’s say that you want to use unions to create pseudo-rows within your admin grid, for this example let’s say that you want to list invoices and invoice items within the same grid, with the increment id field of invoice order items labelled as “invoice item”. Below is an example on how you might fetch that data using SQL:

SELECT `invoice`.entity_id    AS entity_id, 
       `invoice`.increment_id AS increment_id, 
       `invoice`.grand_total  AS price 
FROM   sales_flat_invoice AS `invoice` 
UNION 
(SELECT invoice_item.entity_id AS entity_id, 
        "invoice item"         AS increment_id, 
        invoice_item.price     AS price 
 FROM   sales_flat_invoice_item AS `invoice_item`);

Creating a view that uses this query to populate itself is as simple as:

CREATE VIEW `dbname`.`invoiceview` 
AS 
  SELECT `invoice`.entity_id    AS entity_id, 
         `invoice`.increment_id AS increment_id, 
         `invoice`.grand_total  AS price 
  FROM   sales_flat_invoice AS `invoice` 
  UNION 
  (SELECT invoice_item.entity_id AS entity_id, 
          "invoice item"         AS increment_id, 
          invoice_item.price     AS price 
   FROM   sales_flat_invoice_item AS `invoice_item`);

This is all we need to create a view named ‘invoiceview’ for the database ‘dbname’, populated with the same data that running the first query would return. Magento can now use this view just as it would any other table, filtering and sorting included. Just as you would specify a regular table when creating a resource in config XML, you specify the name of the view inside the table tags.