Magento 1.7 soap request using filter and/or complex filter
===

I am trying to get a list of orders from magento using filters and complex filter through the use of soap. The following xml-snippet shows the the structure of a standard request without parameters set yet. 

Magento 1.7
Soap Client using Apache CXF
There are items in the database...


----------


    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:Magento">
	   <soapenv:Header/>
	   <soapenv:Body>
		  <urn:salesOrderListRequestParam>
			 <sessionId>?</sessionId>
			 <filters>
				<!--Optional:-->
				<filter>
				   <!--Zero or more repetitions:-->
				   <complexObjectArray>
					  <key>?</key>
					  <value>?</value>
				   </complexObjectArray>
				</filter>
				<!--Optional:-->
				<complex_filter>
				   <!--Zero or more repetitions:-->
				   <complexObjectArray>
					  <key>?</key>
					  <value>
						 <key>?</key>
						 <value>?</value>
					  </value>
				   </complexObjectArray>
				</complex_filter>
			 </filters>
		  </urn:salesOrderListRequestParam>
	   </soapenv:Body>
	</soapenv:Envelope>


----------


I tried to call the api using this request and one filter, like this: 

	<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:Magento">
	   <soapenv:Header/>
	   <soapenv:Body>
		  <urn:salesOrderListRequestParam>
			 <sessionId>02ec011a4deef70a55104d8b229e0d41</sessionId>
			 <filters>
				<!--Optional:-->
				<filter>
				   <!--Zero or more repetitions:-->
				   <complexObjectArray>
					  <key>customer_lastname</key>
					  <value>cook</value>
				   </complexObjectArray>
				</filter>
				<!--Optional:-->
				<complex_filter>
				   <!--Zero or more repetitions:-->
				</complex_filter>
			 </filters>
		  </urn:salesOrderListRequestParam>
	   </soapenv:Body>
	</soapenv:Envelope>


----------

The response holds data as i wished, a list of orders from Mr. Cook. So far so good.

Now after enjoying this great success (;-)) i tried to get to the next level. In this case I tried to send more criteria to aggregate the desired data and get it from the database.

Therefore I tried to look up all orders created up to a certain date. The following xml shows this type of request:


----------

	<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
		<soap:Body>
			<ns2:salesOrderListRequestParam
				xmlns:ns2="urn:Magento">
				<sessionId>2b15208c5153189ed7477750c177716c</sessionId>
				<filters>
					<complex_filter>
						<complexObjectArray>
							<key>created_at</key>
							<value>
								<key>from</key>
								<value>2012-07-06 12:55:51</value>
							</value>
						</complexObjectArray>
					</complex_filter>
				</filters>
			</ns2:salesOrderListRequestParam>
		</soap:Body>
	</soap:Envelope>


----------

In fact this request creates an error in the database. Here is the statement created by magento:

	SELECT `main_table`.*, `billing_o_a`.`firstname`, `billing_o_a`.`lastname`, `billing_o_a`.`telephone`, `billing_o_a`.`postcode`, `shipping_o_a`.`firstname`, `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 `mage_sales_flat_order` AS `main_table`
	LEFT JOIN `mage_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 `mage_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') WHERE (((from = '')))

Obviously this makes no sense.. there is no column 'from' and where is the send date hiding and why 

 ... WHERE (((from = '')))

    <complexObjectArray>
       <key>created_at</key>
       <value>
         <key>from</key>
         <value>2012-07-06 12:55:51</value>
       </value>
    <complexObjectArray>

   Exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from = '')))' at line 3' in /home/gaschler/public_dev/lib/Zend/Db/Statement/Pdo.php:234

Well something goes terrible wrong here ... 

Here is the example snippet from the magento page: 

	<item xsi:type="ns1:complexFilter">
		<key xsi:type="xsd:string">protect_code</key>
		<value xsi:type="ns1:associativeEntity">
			<key xsi:type="xsd:string">in</key>
			<value xsi:type="xsd:string">a4ffa8</value>
		</value>
	</item>


Maybe somebody has a clue to solve this issue!?

Thanks in advance...

----------


