Jump to content

SQL query does not work


cranton

Recommended Posts

Hello everyone

In version 1.6.1.12 i used the following query (the dates are just an example):

SELECT psorderdetail.id_order, psorders.reference, psorderdetail.product_name, psorderdetail.product_reference, psorderdetail.product_quantity, psorderdetail.unit_price_tax_incl, psorderdetail.total_price_tax_incl FROM ps_order_detail psorderdetail
INNER JOIN ps_orders psorders ON psorderdetail.id_order=psorders.id_order
WHERE psorders.date_add  >= '2018-01-1 0:0:0' AND psorders.date_add  <= '2019-01-1 0:0:0' AND (psorders.current_state=4 or psorders.current_state=5 or psorders.current_state=11 or psorders.current_state=17)

When I upgraded to 1.7.5.1 and made the same query it gives the "Undefined "checkedForm" error (and I can't save the query). Can anyone tell me what I am missing?

Thnx :)

 

 

Link to comment
Share on other sites

  • 10 months later...

Having the same problem here.  Trying to add a query to extract order-detail level information suitable for exporting to Excel so I can generate shipping labels.  The query runs perfectly fine in phpMyAdmin, but I'm unable to Save it in the Prestashop SQL Manager.  Gives 'Undefined "checkdForm" error'

The query started from a snippet someone else had posted (the joins in particular).

SELECT o.current_state "Order_Status", os.name "Order_Status_Descr", d.id_order, d.id_order_detail, d.product_id, d.product_attribute_id, d.id_customization, d.product_name, d.product_quantity, d.product_price, d.product_reference, d.total_price_tax_incl, 
o.reference "Order_Reference", o.payment, o.total_discounts, o.total_paid, o.date_add, 
ad.alias "Delivery_Address_Source", ad.LastName "Delivery_LastName", ad.firstname "Delivery_FirstName", ad.address1 "Delivery_Addr1", ad.address2 "Delivery_Addr2", ad.postcode "Delivery_Zip", ad.city "Delivery_City", ad.phone "Delivery_Phone", ad.id_state "Delivery_StateID", sd.iso_code "State_Abbr", sd.name "State_Name",
ai.alias "Invoice_Address_Source", ai.LastName "Invoice_LastName", ai.firstname "Invoice_FirstName", ai.address1 "Invoice_Addr1", ai.address2 "Invoice_Addr2", ai.postcode "Invoice_Zip", ai.city "Invoice_City", ai.phone "Invoice_Phone",  
g.company, g.firstname "Cust_Firstname", g.lastname "Cust_Lastname", g.email "Cust_Email"
FROM psar_order_detail d
    LEFT JOIN psar_orders o ON (d.id_order = o.id_order)
    LEFT JOIN psar_address ad ON (o.id_address_delivery = ad.id_address)
    LEFT JOIN psar_state sd ON (ad.id_state = sd.id_state)
    LEFT JOIN psar_address ai ON (o.id_address_invoice = ai.id_address)
    LEFT JOIN psar_stock_available s ON (d.product_id = s.id_product)
    LEFT JOIN psar_customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN psar_group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'piiri%'
    LEFT JOIN psar_order_state_lang os ON (o.current_state = os.id_order_state)
    WHERE os.id_lang = 1 and
             o.invoice_date >= '2020-03-01' 
GROUP BY gl.name, d.id_order, d.product_reference

 

Edited by redrob
added sample (see edit history)
Link to comment
Share on other sites

  • 1 month later...

I didn't fix it but I've done a workaround. It consists in overriding validation method and bypass it's validations. Of course I develop my queries in mysql before I paste on Prestashop.

So I created a new file: /override/controllers/AdminRequestSqlController.php with the following content:

<?php

class AdminRequestSqlController extends AdminRequestSqlControllerCore
{
    public function _childValidation() {
        if (Tools::getValue('submitAdd' . $this->table) && $sql = Tools::getValue('sql')) {
            $request_sql = new RequestSql();
            $parser = $request_sql->parsingSql($sql);
            $validate = $request_sql->validateParser($parser, false, $sql);
        }
    }
}

?>

After that remove file /shop/var/cache/prod/class_index.php

 

Hope it helps ;)

Edited by Becquel (see edit history)
  • Like 1
Link to comment
Share on other sites

  • 7 months later...

Another workaround I found successful is to create a SQL View which is essentially the query, then just add the SELECT * FROM ViewName as the PrestaCart query manager query (you can add criteria to the where clause too if needed)

 

The simpler query in PC's SQL manager seems to work ok.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...