Conflicts in where clause with ambiguous column names

by

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment