7

I am trying to filter a huge collection of sales order. There are more then 5 million records in it. Below is my code to get the order collection and also i puts some joins to get the desired columns

    $resource = Mage::getSingleton('core/resource');
    $collection = Mage::getResourceModel('sales/order_grid_collection');

    $collection ->join(
      'sales/order_item',
    '`sales/order_item`.order_id=`main_table`.entity_id',
    array(
    'skus' => new Zend_Db_Expr('GROUP_CONCAT(`sales/order_item`.sku SEPARATOR "</br>")'),
    )
    );



    $collection->getSelect()->joinLeft(array('sfog' => $resource->getTableName('sales_flat_order_grid')),
    'main_table.entity_id = sfog.entity_id',array('sfog.shipping_name','sfog.billing_name'));

    $collection->getSelect()->joinLeft(array('sfo'=>  $resource->getTableName('sales_flat_order')),
    'sfo.entity_id=main_table.entity_id',array('sfo.customer_email','sfo.weight',
    'sfo.discount_description','sfo.increment_id','sfo.store_id','sfo.created_at','sfo.status',
    'sfo.base_grand_total','sfo.grand_total'));

    $collection->getSelect()->joinLeft(array('sfoa'=>  $resource->getTableName('sales_flat_order_address')),
    'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street',
    'sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone','sfoa.fax'));

when i apply addAttributeToFilter function on this collection then it takes 10 minutes to fetch the result. My question is that is there any efficient and fast way to filter collection

Update

Below are my filter logic's. I want to search a order by using different filters

    $email = Mage::app()->getRequest()->getParam('email');

    $phone = Mage::app()->getRequest()->getParam('phone');

    $postcode = Mage::app()->getRequest()->getParam('postcode');

    $skus = Mage::app()->getRequest()->getParam('skus');

    if($email!='')
    {
        $collection->addAttributeToFilter('sfo.customer_email',$email);
    }

    if($phone!='' && $postcode=='')
    {
        $phone = str_replace(' ', '', $phone); // Replaces all spaces with hyphens.
        $phone = preg_replace('/[^A-Za-z0-9\-]/', '', $phone); // Removes special chars.
        $collection->addAttributeToSearchFilter(
            array(
                array(
                    'attribute' => 'sfoa.telephone',
                    'eq' => $phone
                ),
                array(
                    'attribute' => 'sfoa.fax',
                    'eq' => $phone
                )
            )
        );
    }

    if($postcode!='' && $phone!='')
    {
        $collection->addAttributeToFilter('sfoa.postcode',$postcode);
        $phone = str_replace(' ', '', $phone); // Replaces all spaces with hyphens.
        $phone = preg_replace('/[^A-Za-z0-9\-]/', '', $phone); // Removes special chars.
        $collection->addAttributeToFilter('sfoa.telephone',$phone);
    }

    if($skus!='')
    {
        $sku_array = explode(",",$skus);
        $collection->addAttributeToFilter('sku', array('in' => array('finset' => array($sku_array))));
    }
2

When you work with very large collections, there's a lot of chances you will end up either with a fatal error Allowed memory size of X bytes exhausted or Time out. In your case, it does not happen but it takes a lot of time.

Fortunately, even though most people are not aware of it, Magento provides a solution for that, the Mage_Core_Model_Resource_Iterator model. It basically lets you get data from database via an iterator (so one by one) instead of loading every results at once like you do in your code.

The iterator is using the walk() method which requires two parameters: a collection database query string and a callback method.

The walk() method code looks like this:

public function walk($query, array $callbacks, array $args=array(), $adapter = null)
{
    $stmt = $this->_getStatement($query, $adapter);
    $args['idx'] = 0;
    while ($row = $stmt->fetch()) {
        $args['row'] = $row;
        foreach ($callbacks as $callback) {
            $result = call_user_func($callback, $args);
            if (!empty($result)) {
                $args = array_merge($args, $result);
            }
        }
        $args['idx']++;
    }

    return $this;
}

You don't say what your code is doing exactly (if you loop through the results or if you want to export it), so I'm just going to give you an example that you can use.

After the code you pasted you can do the following:

Mage::getSingleton('core/resource_iterator')->walk($collection->getSelect(), array(array($this, 'collectionCallback')));

In the same class, you need to create the collectionCallback method:

public function collectionCallback($args)
{
    // The data for each entry is stored here
    $data = $args['row'];
    // Do something with the data
}

I don't know how good it would be in terms of performance compared to the addAttributeToFilter method but you could do the filter directly using this callback function, example:

public function collectionCallback($args)
{
    // The data for each entry is stored here
    $data = $args['row'];
    if ($data['attribute'] != "test") {
        return;
    }

}
  • i want to search the orders by different filters – Shaheer Ali Apr 11 '16 at 9:44
  • @ShaheerAli great then, the walk method is exactly what you need – Raphael at Digital Pianism Apr 11 '16 at 9:45
  • @Raphel check my updated question. How can i get same result by using walk function – Shaheer Ali Apr 11 '16 at 10:16
  • @Raphel at Digital. i tried your solution but it takes same time and also give me no result :( – Shaheer Ali Apr 12 '16 at 12:54
0

Fast and most effective way to deal with last size of data is use MY SQL query in Magento to get desired columns data.

$resource = Mage::getSingleton('core/resource');

To run a select you can do something like this:

$readConnection = $resource->getConnection('core_read');

$query = 'SELECT * FROM ' . $resource->getTableName('custom/model');

$results = $readConnection->fetchAll($query);

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.