Magento Column not found: Unknown column 'order_created_at' when accessing Sales Shipment API

Question

Will appreciate any ideas on this error in Magento 1.4.1.1. I wonder why this happens but Magento 1.4.0 and Magento 1.3.2.4 works fine.

The error is caused by accessing Magento Sales Shipment List API with "order_created_at filter. This filter worked in previous versions but with this version it fails.

I wonder why this could happen.

Also see Magento Issue for reference: http://www.magentocommerce.com/bug-tracking/issue/?issue=9556 there are some details of request sent to Magento webservice.

Here is the actual request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
<soapenv:Body>
<ns1:salesOrderShipmentList soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="urn:Magento">
<sessionId xsi:type="xsd:string">bbb778755d046129c1c5df1cf4f0b119</sessionId>
<filters href="#id0"/>
</ns1:salesOrderShipmentList>
<multiRef id="id0" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns2:filters" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns2="urn:Magento">
<filter xsi:type="ns2:associativeEntity" xsi:nil="true"/>
<complex_filter soapenc:arrayType="ns2:complexFilter[1]" xsi:type="soapenc:Array">
<complex_filter href="#id1"/>
</complex_filter>
</multiRef>
<multiRef id="id1" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns3:complexFilter" xmlns:ns3="urn:Magento" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"&gt;
<key xsi:type="xsd:string">order_created_at</key>
<value href="#id2"/>
</multiRef>
<multiRef id="id2" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns4:associativeEntity" xmlns:ns4="urn:Magento" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"&gt;
<key xsi:type="xsd:string">gt</key>
<value xsi:type="xsd:string">2010-07-08</value>
</multiRef>
</soapenv:Body>
</soapenv:Envelope>

Here is the response: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_created_at' in 'where clause'

Answer 1

The column name is created_at not order_created_at. (At least in my DB :) )

Answer 2

The truth is that "order_created_at" filter for shipments is broken (at least using SOAP API) in the latest 1.4.1.1 SE and 1.8.0 Enterprise versions, while it worked in previous versions.

The workaround is to use "created_at" filter for shipments this will give some more extra shipments than expected for the the same period, but as the shipment creation time always goes after it's order creation time - it's safe that you will get all shipments of orders created after specified creation time.

If someone needs more precise shipments list related to all orders created after given time then the shipments downloaded with "created_at" filter can be further filtered, although this will assume downloading orders list as well. Shortly, shipments can be further filtered on client side.