CODEWITHSUNDEEP
magentomagento-soap-api
stereoscope
stereoscope

Reputation: 501

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/.../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...

Upvotes: 0

Views: 4989

Answers (1)

Anthony
Anthony

Reputation: 31

Rather than using "from" or "to" - have you tried using "gteq" and "lteq" instead? When testing using PHP it works; however you can't supply an upper and lower bound, e.g., 

<complexObjectArray>
  <item>
   <key>created_at</key>
   <value>
     <key>gteq</key>
     <value>2012-07-06 12:55:51</value>
   </value>
  </item>
  <item>
   <key>created_at</key>
   <value>
     <key>lteq</key>
     <value>2013-07-06 12:55:51</value>
   </value>
  </item>
<complexObjectArray>

Upvotes: 3

Related Questions