Databases
StackOverflow2013
Postsdbo
POMagento 1.7 soap request using filter and/or complex filter
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMagento 1.7 soap request using filter and/or complex filter
    primarykey
    data
    text
    <p>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. </p> <p>Magento 1.7 Soap Client using Apache CXF There are items in the database...</p> <hr> <pre><code>&lt;soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:Magento"&gt; &lt;soapenv:Header/&gt; &lt;soapenv:Body&gt; &lt;urn:salesOrderListRequestParam&gt; &lt;sessionId&gt;?&lt;/sessionId&gt; &lt;filters&gt; &lt;!--Optional:--&gt; &lt;filter&gt; &lt;!--Zero or more repetitions:--&gt; &lt;complexObjectArray&gt; &lt;key&gt;?&lt;/key&gt; &lt;value&gt;?&lt;/value&gt; &lt;/complexObjectArray&gt; &lt;/filter&gt; &lt;!--Optional:--&gt; &lt;complex_filter&gt; &lt;!--Zero or more repetitions:--&gt; &lt;complexObjectArray&gt; &lt;key&gt;?&lt;/key&gt; &lt;value&gt; &lt;key&gt;?&lt;/key&gt; &lt;value&gt;?&lt;/value&gt; &lt;/value&gt; &lt;/complexObjectArray&gt; &lt;/complex_filter&gt; &lt;/filters&gt; &lt;/urn:salesOrderListRequestParam&gt; &lt;/soapenv:Body&gt; &lt;/soapenv:Envelope&gt; </code></pre> <hr> <p>I tried to call the api using this request and one filter, like this: </p> <pre><code>&lt;soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:Magento"&gt; &lt;soapenv:Header/&gt; &lt;soapenv:Body&gt; &lt;urn:salesOrderListRequestParam&gt; &lt;sessionId&gt;02ec011a4deef70a55104d8b229e0d41&lt;/sessionId&gt; &lt;filters&gt; &lt;!--Optional:--&gt; &lt;filter&gt; &lt;!--Zero or more repetitions:--&gt; &lt;complexObjectArray&gt; &lt;key&gt;customer_lastname&lt;/key&gt; &lt;value&gt;cook&lt;/value&gt; &lt;/complexObjectArray&gt; &lt;/filter&gt; &lt;!--Optional:--&gt; &lt;complex_filter&gt; &lt;!--Zero or more repetitions:--&gt; &lt;/complex_filter&gt; &lt;/filters&gt; &lt;/urn:salesOrderListRequestParam&gt; &lt;/soapenv:Body&gt; &lt;/soapenv:Envelope&gt; </code></pre> <hr> <p>The response holds data as i wished, a list of orders from Mr. Cook. So far so good.</p> <p>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.</p> <p>Therefore I tried to look up all orders created up to a certain date. The following xml shows this type of request:</p> <hr> <pre><code>&lt;soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"&gt; &lt;soap:Body&gt; &lt;ns2:salesOrderListRequestParam xmlns:ns2="urn:Magento"&gt; &lt;sessionId&gt;2b15208c5153189ed7477750c177716c&lt;/sessionId&gt; &lt;filters&gt; &lt;complex_filter&gt; &lt;complexObjectArray&gt; &lt;key&gt;created_at&lt;/key&gt; &lt;value&gt; &lt;key&gt;from&lt;/key&gt; &lt;value&gt;2012-07-06 12:55:51&lt;/value&gt; &lt;/value&gt; &lt;/complexObjectArray&gt; &lt;/complex_filter&gt; &lt;/filters&gt; &lt;/ns2:salesOrderListRequestParam&gt; &lt;/soap:Body&gt; &lt;/soap:Envelope&gt; </code></pre> <hr> <p>In fact this request creates an error in the database. Here is the statement created by magento:</p> <pre><code>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 = ''))) </code></pre> <p>Obviously this makes no sense.. there is no column 'from' and where is the send date hiding and why </p> <p>... WHERE (((from = '')))</p> <pre><code>&lt;complexObjectArray&gt; &lt;key&gt;created_at&lt;/key&gt; &lt;value&gt; &lt;key&gt;from&lt;/key&gt; &lt;value&gt;2012-07-06 12:55:51&lt;/value&gt; &lt;/value&gt; &lt;complexObjectArray&gt; </code></pre> <p>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</p> <p>Well something goes terrible wrong here ... </p> <p>Here is the example snippet from the magento page: </p> <pre><code>&lt;item xsi:type="ns1:complexFilter"&gt; &lt;key xsi:type="xsd:string"&gt;protect_code&lt;/key&gt; &lt;value xsi:type="ns1:associativeEntity"&gt; &lt;key xsi:type="xsd:string"&gt;in&lt;/key&gt; &lt;value xsi:type="xsd:string"&gt;a4ffa8&lt;/value&gt; &lt;/value&gt; &lt;/item&gt; </code></pre> <p>Maybe somebody has a clue to solve this issue!?</p> <p>Thanks in advance...</p> <hr>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USstereoscope
    1. USstereoscope
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COobviously this seems to be a bug in magento's v2 API
      singulars
      1. POMagento 1.7 soap request using filter and/or complex filter
      1. USstereoscope
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload