0

I am trying to get the list of shipments for a given order via the API (I use the SOAP API but I don't think it matters).

I don't have the order ID but the 9-digit number (the increment_id from the sales_flat_order table).

I can see the following code in the API method I am trying to call:

    /**
     * Retrieve shipments by filters
     *
     * @param null|object|array $filters
     * @return array
     */
    public function items($filters = null)
    {
        $shipments = array();

        $shipmentCollection = Mage::getResourceModel('sales/order_shipment_collection')
            ->addAttributeToSelect('increment_id')
            ->addAttributeToSelect('created_at')
            ->addAttributeToSelect('total_qty')
            ->joinAttribute('shipping_firstname', 'order_address/firstname', 'shipping_address_id', null, 'left')
            ->joinAttribute('shipping_lastname', 'order_address/lastname', 'shipping_address_id', null, 'left')
            ->joinAttribute('order_increment_id', 'order/increment_id', 'order_id', null, 'left')
            ->joinAttribute('order_created_at', 'order/created_at', 'order_id', null, 'left');

        $apiHelper = Mage::helper('api');
        $filters = $apiHelper->parseFilters($filters, $this->_attributesMap['shipment']);
        foreach ($filters as $field => $value) {
            $shipmentCollection->addFieldToFilter($field, $value);
        }

        ...
        return $shipments;
    }

From: Mage/Sales/Model/Order/Shipment/Api.php (slightly edited for brevity).

I don't fully understand the syntax of this query but it seems like a join is made with the orders table, and then filters are added from the filters API parameter.

I have tried to call this API this way (calling from a C# program):

var shipments = Client.SalesOrderShipmentList(sessionId, new Filters
{
    filter = new AssociativeEntity[]
    {
        new AssociativeEntity
        {
            key = "order_increment_id",
            value = "000000006"
        }
    }
});

I am getting the following error:

'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_increment_id' in 'where clause', query was: SELECT main_table.increment_id, main_table.created_at, main_table.total_qty FROM sales_flat_shipment AS main_table WHERE (order_increment_id = '000000006')'

It seems that the join isn't being translated to SQL. I have tried several things like prefixing the column name with the orders table name, to no avail.

Am I doing something wrong?

Thank you

5
  • Have you tried doing echo $shipmentCollection->getSelectSql(true); to debug the SQL it generates? You can this in various places in the Api.php-file. As a workaround, you could do another call before the one above and get the order_id by increment_id; then use this for filtering shipments. Aug 11 '20 at 16:10
  • Thanks for the input. I can't seem to echo anything since it's an API (sorry I'm not a PHP dev). But I would guess the SQL would be the same as included in the error message? I am open to the work around but unfortunately I couldn't find an API that would give me the order_id from the increment_id. Aug 12 '20 at 8:58
  • I think it's quite easy in M2, and your client should update anyway :D M1 is EOL since June. Doesn't salesOrderInfo give you the entity_id? magento.stackexchange.com/questions/32215/…, csharp.hotexamples.com/de/examples/-/MagentoService/… Aug 12 '20 at 11:30
  • Can't believe I missed it! I did look at that API but somehow did not find the entity_id the first time around. I will use that, thank you! Aug 14 '20 at 8:45
  • Nice. I added an answer so this question can be marked as solved. Aug 14 '20 at 8:52
0
+100

Although I can't help with the actual problem, I suggest to use a workaround:

Use 2 API-requests to first get the order's entity_id, and get shipment info with the second request.

The API-method to use here is salesOrderInfo as, for example, described here:

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.