php - mysql where 子句语法错误 prestashop

Question

我正在尝试根据送货国家/地区列出订单。为此，我在下面编写了 sql 查询。它在数据库上运行良好。但是当我尝试在 AdminOrderController.php 文件上实现时，它会抛出语法错误，我尝试通过更改语法以多种方式解决该错误。

如何在 php 端实现此 where 子句 WHEREcountry_lang.name = 'France'？

你能帮我解决这个问题吗？

错误消息:

     Bad SQL query
            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 
            'country_lang.name = 'France' ORDER BY a.`id_order` 
             DESC LIMIT 0,50' at line 22

SQL查询:

    SELECT
       ps_orders.id_currency,
       ps_orders.id_order AS id_pdf,
       CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`,
       osl.`name` AS `osname`,
       os.`color`,
       IF((SELECT COUNT(so.id_order) FROM `ps_orders` so WHERE so.id_customer =
           ps_orders.id_customer) > 1, 0, 1) as new,
       country_lang.name as cname,
       IF(ps_orders.valid, 1, 0) badge_success
    FROM ps_orders

    LEFT JOIN `ps_customer` c ON (c.`id_customer` = ps_orders.`id_customer`)
    INNER JOIN `ps_address` address ON address.id_address = 
               ps_orders.id_address_delivery
    INNER JOIN `ps_country` country ON address.id_country = country.id_country
    INNER JOIN `ps_country_lang` country_lang ON (country.`id_country` = 
                country_lang.`id_country` AND country_lang.`id_lang` = 3)
    LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = ps_orders.`current_state`)
    LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` 
               AND osl.`id_lang` = 3)

    WHERE country_lang.name = 'France'
    ORDER BY id_order DESC

PHP 代码:

    $this->_select = '
                     a.id_currency,
                     a.id_order AS id_pdf,
                     CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS `customer`,
                     osl.`name` AS `osname`,
                     os.`color`,
                     IF((SELECT COUNT(so.id_order) FROM `'._DB_PREFIX_.'orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new,
                     country_lang.name as cname,
                     IF(a.valid, 1, 0) badge_success';

    $this->_join = '
                    LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
                    INNER JOIN `'._DB_PREFIX_.'address` address ON address.id_address = a.id_address_delivery
                    INNER JOIN `'._DB_PREFIX_.'country` country ON address.id_country = country.id_country
                    INNER JOIN `'._DB_PREFIX_.'country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = '.(int)$this->context->language->id.')
                     LEFT JOIN `'._DB_PREFIX_.'order_state` os ON (os.`id_order_state` = a.`current_state`)
                     LEFT JOIN `'._DB_PREFIX_.'order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = '.(int)$this->context->language->id.')';

    $this->_where = '';
    $this->_orderBy = 'id_order';
    $this->_orderWay = 'DESC';

解决方案:

    $this->_where = 'AND country_lang.id_country = 8';

Answer 1

解决方案:

$this->_where = 'AND country_lang.id_country = 8';