27

A bit of context for this. I want to extend the sales order export function (via the grid) to have more columns. I have created a module which adds a new grid for exporting and also a new collection model which extends the original. This uses the _beforeLoad() function so I can join the tables that I need.

The trouble that I'm having is that when the filters from the grid are added (the increment_id, order date etc), the where clause this adds doesn't prefix the table and I'm getting issues with ambiguous column names. For example, on increment_id I'm having the issue in the where clause:

SELECT `main_table`.*, `sales`.`total_qty_ordered`, `sales`.`entity_id` AS `order_id`, `sagepay`.`vendor_tx_code` FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN `sales_flat_order` AS `sales` ON main_table.increment_id = sales.increment_id
 LEFT JOIN `sagepaysuite_transaction` AS `sagepay` ON order_id = sagepay.order_id WHERE (increment_id LIKE '%100000261%') GROUP BY `main_table`.`entity_id`

This where clause is added before I do the joins to the other tables in the _addColumnFilterToCollection() function

protected function _addColumnFilterToCollection($column)
    {
        if ($this->getCollection()) {
            $field = ( $column->getFilterIndex() ) ? $column->getFilterIndex() : $column->getIndex();
            if ($column->getFilterConditionCallback()) {
                call_user_func($column->getFilterConditionCallback(), $this->getCollection(), $column);
            } else {
                $cond = $column->getFilter()->getCondition();
                if ($field && isset($cond)) {
                    // Filter added at this point
                    $this->getCollection()->addFieldToFilter($field , $cond);
                }
            }
        }
        return $this;
    }

As a brief test, I changed the line to

$this->getCollection()->addFieldToFilter('main_table.' . $field , $cond);

and this worked but it doesn't feel a great way to do it.

My code in _beforeLoad() is

protected function _beforeLoad()
{
    // Join the sales_flat_order table to get order_id and and total_qty_ordered
    $this->getSelect()->joinLeft(array('sales' => $this->getTable('sales/order')),
        'main_table.increment_id = sales.increment_id',
        array('total_qty_ordered' => 'sales.total_qty_ordered',
              'order_id' => 'sales.entity_id'));

    // Join the SagePay transaction table to get vendor_tx_code
    $this->getSelect()->joinLeft(array('sagepay' => $this->getTable('sagepaysuite2/sagepaysuite_transaction')),
        'order_id = sagepay.order_id',
        array('vendor_tx_code' => 'vendor_tx_code'));

    $this->getSelect()->group('main_table.entity_id');
    parent::_beforeLoad();
}

I have to use increment_id to join the sales order grid table and the SagePay transaction table as that is the only common ID I can see.

Basically I'm wondering what the best approach is to tackle this. I could probably get away with doing the change I mentioned above but it doesn't feel right. Is there anything I can change in my join statements?

Thanks.

  • 1
    How did you join the tables? Working on the Zend_Db_Select model is a bad idea, because magento records the joint tables and adds normally all the prefixes. I wrote a blog article about joining, maybe it helps: blog.fabian-blechschmidt.de/articles/… – Fabian Blechschmidt Jan 27 '13 at 15:50
  • Thank you for the response, I will have a read of that. I tried to use the joinTable() but it wasn't available in the collection model. – Paul Jan 27 '13 at 16:14
49

You can easily resolve any ambiguous where condition by using the following method of collection:

  • addFilterToMap($filterName, $alias, $group = 'fields')
    • $filter - it is the name of the filter that is used in addFieldToFilter() method, for your case it is increment_id
    • $alias - it is the full name of the column that is associated to the filter, for you case it is main_table.increment_id.
    • $group - was intended to be a map for any kind of info in collection, but for now it is used only in filters, so you can omit this argument.

Also I don't think that beforeLoad is the right place to place your joins, unless you are observing an event. In your case it is better to move it into _initSelect() method with calling before parent::_initSelect(). You can call addFilterToMap() method inside of your _initSelect() method for resolving join conflicts, like this:

$this->addFilterToMap('increment_id', 'main_table.increment_id');
  • Out of interest, why is it better to do the joins in _initSelect()? – Paul Jan 27 '13 at 16:12
  • @Paul _initSelect is executed only once all the time, _beforeLoad can be called more then one time, since you can load() collection more than once if you reset its state. – Ivan Chepurnyi Jan 27 '13 at 16:31
  • @Paul as well, since _beforeLoad can be called twice, you will receive a fatal error from Zend_Db_Select on second call. – Ivan Chepurnyi Jan 27 '13 at 16:38
  • 2
    I did soemthing like this: $collection = Mage::getModel("education/ticket") ->getCollection() ->addFilterToMap('updated_at', 'main_table.updated_at') ->addFilterToMap('created_at', 'main_table.created_at'); – FosAvance Nov 14 '16 at 12:29
  • @IvanChepurnyi, Are genious. It reflects that you are excellent as Magento 1 architect – Amit Bera Mar 2 '18 at 8:05

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.