并在模型打开购物车中请求

Question

我在管理员打开购物车中开发了新的过滤器。但是我的sql请求不起作用

字段"值"在表 oc_order_option 中声明

没有我的过滤器的 SQL 请求

SELECT o.order_id, CONCAT(o.lastname, ' ', o.firstname) AS customer, COUNT(po.pass_id) as passengers, m.name as carrier, cd.name as tour, o.shipping_code, o.total, os.name as order_status, o.currency_code, o.currency_value, o.date_added, o.date_modified 
FROM oc_order o 
LEFT JOIN oc_agent_to_order ato ON ato.order_id = o.order_id
JOIN oc_order_option oo ON oo.order_id = o.order_id 
JOIN oc_order_status os ON os.order_status_id = o.order_status_id 
JOIN oc_passenger_to_order po ON o.order_id = po.order_id 
JOIN oc_passenger pfo ON po.pass_id = pfo.pass_id 
JOIN oc_order_product op ON op.order_id = o.order_id 
JOIN oc_product p ON p.product_id = op.product_id 
JOIN oc_manufacturer m ON m.manufacturer_id = p.manufacturer_id 
JOIN oc_product_to_category ptc ON ptc.product_id = p.product_id AND ptc.main_category = 1 
JOIN oc_category_description cd ON cd.category_id = ptc.category_id 
WHERE o.order_status_id > '0'

我为我的请求尝试使用此sql代码

AND value BETWEEN '10.04.2019' AND '10.04.2019'

完整代码：

SELECT o.order_id, CONCAT(o.lastname, ' ', o.firstname) AS customer, COUNT(po.pass_id) as passengers, m.name as carrier, cd.name as tour, o.shipping_code, o.total, os.name as order_status, o.currency_code, o.currency_value, o.date_added, o.date_modified 
FROM oc_order o 
LEFT JOIN oc_agent_to_order ato ON ato.order_id = o.order_id 
JOIN oc_order_option oo ON oo.order_id = o.order_id 
JOIN oc_order_status os ON os.order_status_id = o.order_status_id 
JOIN oc_passenger_to_order po ON o.order_id = po.order_id 
JOIN oc_passenger pfo ON po.pass_id = pfo.pass_id 
JOIN oc_order_product op ON op.order_id = o.order_id 
JOIN oc_product p ON p.product_id = op.product_id 
JOIN oc_manufacturer m ON m.manufacturer_id = p.manufacturer_id 
JOIN oc_product_to_category ptc ON ptc.product_id = p.product_id AND ptc.main_category = 1 
JOIN oc_category_description cd ON cd.category_id = ptc.category_id 
WHERE o.order_status_id > '0' AND value BETWEEN '10.04.2019' AND '10.04.2019'

Answer 1

如果对

常量使用正确的日期格式，它是否有效？

value BETWEEN '2019-04-10' AND '2019-04-10'

当然，这相当于：

value = '2019-04-10'

如果value是日期/时间值，则仅在 2019-04-10 午夜匹配。 我怀疑你可能想要：

value >= '2019-04-10' AND
value < '2019-04-11'