API SOAP V1 - sales_order.list not returning results

Question

I'm dealing with a Magento Store (CE 1.7) that has over 10M orders.

I am trying to get the full order list through SOAP V1 in order to extract some statistics from it, and i encounter the following problem.

When i run

$result = $soap->call($session_id, 'sales_order.list', array(array('status'=>array('eq'=>'Canceled'))));

echo "<pre>";
foreach($result as $order){        
    echo ($order['increment_id'])." - ";        // order number
    echo ($order['customer_id'])." - ";        // customer number
    echo ($order['billing_lastname'])." - ";    // customer last name
    echo ($order['grand_total']);        // grand Total    
    echo "<br/>";
}        
echo "</pre>"; 

i sucessfuly get the results (~ 1300 records).

When i try to change 'Canceled' to 'Processing', or 'eq' to 'neq' (expecting about 10M results) i get an error Warning: Invalid argument supplied for foreach() and if i print_r($result); i get an empty string.

Answer 1

The only solution is to process records in batches. Use order ID range in your filter along with filter by status (make sure to use 'from' and 'to' filters for range, since these are the only filters that can be applied to the same field in one request). This is not possible with SOAP V1, so my suggestion is to use SOAP V2. Filter should look something like:

$params = array(
    'complex_filter' => array(
        array(
            'key' => 'status',
            'value' => array(
                'key' => 'eq',
                'value' => 'canceled'
            ),
        ),
        array(
            'key' => 'order_id',
            'value' => array(
                'key' => 'from',
                'value' => '1'
            ),
        ),
        array(
            'key' => 'order_id',
            'value' => array(
                'key' => 'to',
                'value' => '100'
            ),
        ),
    )
);
$result = $client->__call('salesOrderList', $params);

Send multiple requests with different order ID range, then Magento will be able to generate response without exceeding memory limit.

Answer 2

Please try bellow script

try {
    $client = new SoapClient('http://magento.com/index.php/api/?wsdl'); // api url
    $sessionId = $client->login('test', 'test123'); // API user name & key 

    $result = $client->call($sessionId, 'sales_order.list', array(array('status'=>array('neq'=>'Processing'))));

    echo "<pre>";
    foreach($result as $order){        
        echo ($order['increment_id'])." - ";        // order number
        echo ($order['customer_id'])." - ";        // customer number
        echo ($order['billing_lastname'])." - ";    // customer last name
        echo ($order['grand_total']);        // grand Total    
        echo "<br/>";
    }        
    echo "</pre>"; 
    }
catch (Exception $e) {
   echo $e->getMessage();
}

Answer 3

I tried this one with python3.6 and it works. You can try it out too with Magento xmlrpc v1,

from xmlrpc import client
import json
from pprint import pprint

client = client.ServerProxy('http://'+MAGENTO_DOMAIN+'/index.php/api/xmlrpc/',allow_none=True)
session = client.login(API_USERNAME,API_PASSWORD)

filter_array = [{'increment_id':'100000001', 'status':'pending'}]
response = client.call(session, 'sales_order.list', filter_array)

encode_response = json.dumps(response)
response_data = json.loads(encode_response)

pprint(response_data)

Note: Those filter array will join to below select query where conditions. So you can add more filters there as normal select where columns.

SELECT `main_table`.*, `billing_o_a`.`firstname`, `billing_o_a`.`middlename`, 
`billing_o_a`.`lastname`, `billing_o_a`.`telephone`, `billing_o_a`.`postcode`, 
`shipping_o_a`.`firstname`, `shipping_o_a`.`middlename`, `shipping_o_a`.`lastname`, 
`shipping_o_a`.`telephone`, `shipping_o_a`.`postcode`, `billing_o_a`.`firstname` 
AS `billing_firstname`, `billing_o_a`.`lastname` AS `billing_lastname`, `shipping_o_a`.`firstname` 
AS `shipping_firstname`, `shipping_o_a`.`lastname` AS `shipping_lastname`, 
CONCAT(billing_o_a.firstname, " ", billing_o_a.lastname) AS `billing_name`, 
CONCAT(shipping_o_a.firstname, " ", shipping_o_a.lastname) AS `shipping_name` 
FROM `sales_flat_order` AS `main_table` LEFT JOIN `sales_flat_order_address` AS `billing_o_a` 
ON (main_table.entity_id = billing_o_a.parent_id AND 
billing_o_a.address_type = 'billing') LEFT JOIN `sales_flat_order_address` AS `shipping_o_a` 
ON (main_table.entity_id = shipping_o_a.parent_id AND shipping_o_a.address_type = 'shipping')