Klemart3D Klemart3D - 3 months ago 9
MySQL Question

Advanced SQL: Filter orders that have a mismatch with order state with the last order state in order history

I'm using Prestashop and I need to verify data integrity comparing the current state of each order with the last order history state registered.

The orders table:

╔══════════╦═══════════════╦
║ id_order ║ current_state ║
╠══════════╬═══════════════╬
║ 1 ║ 3 ║
║ 2 ║ 1 ║
║ 3 ║ 2 ║
║ 4 ║ 1 ║
╚══════════╩═══════════════╩


The order_history table:

╔══════════════════╦══════════╦════════════════╦═════════════════════╦
║ id_order_history ║ id_order ║ id_order_state ║ date_add ║
╠══════════════════╬══════════╬════════════════╬═════════════════════╬
║ 1 ║ 1 ║ 1 ║ 2016-08-01 11:00:00 ║
║ 2 ║ 2 ║ 1 ║ 2016-08-02 12:00:00 ║
║ 3 ║ 1 ║ 3 ║ 2016-08-03 13:00:00 ║
║ 4 ║ 3 ║ 1 ║ 2016-08-04 14:00:00 ║
║ 5 ║ 3 ║ 2 ║ 2016-08-05 15:00:00 ║
║ 6 ║ 2 ║ 3 ║ 2016-08-06 16:00:00 ║
║ 7 ║ 4 ║ 1 ║ 2016-08-07 17:00:00 ║
╚══════════════════╩══════════╩════════════════╩═════════════════════╩


(The voluntary missed table is to give a name for order states : 1 = "Paid", 2 = "Confirmed", 3 = "Shipped"…)

Normally, current_state order must be equal to the last state history entry, but sometimes not (in my sample, for order #2, current_state = 1 but the id_order_state of the last history update is 2), and thats what I want to reveal.

I do this to get the each last order state update:

SELECT o.id_order, o.current_state, h.id_order_state, max(h.date_add)
FROM orders o, order_history h
WHERE o.id_order = h.id_order
GROUP BY o.id_order, o.current_state, h.id_order_state
ORDER BY o.id_order ASC


But it's not enough because firstly I want only the states of the last update of each order:

╔══════════╦═══════════════╦════════════════╦═════════════════════╗
║ id_order ║ current_state ║ id_order_state ║ max(h.date_add) ║
╠══════════╬═══════════════╬════════════════╬═════════════════════╣
║ 1 ║ 3 ║ 3 ║ 2016-08-03 13:00:00 ║
║ 2 ║ 1 ║ 3 ║ 2016-08-06 16:00:00 ║
║ 3 ║ 2 ║ 2 ║ 2016-08-05 15:00:00 ║
║ 4 ║ 1 ║ 1 ║ 2016-08-07 17:00:00 ║
╚══════════╩═══════════════╩════════════════╩═════════════════════╝


And secondly add a filter with
WHERE current_state <> id_order_state
to show only corrupted data (like order #2):

╔══════════╦═══════════════╦════════════════╦═════════════════════╗
║ id_order ║ current_state ║ id_order_state ║ max(h.date_add) ║
╠══════════╬═══════════════╬════════════════╬═════════════════════╣
║ 2 ║ 1 ║ 3 ║ 2016-08-06 16:00:00 ║
╚══════════╩═══════════════╩════════════════╩═════════════════════╝


Does a full SQL request can do this ?

Answer

E.g.:

SELECT o.id_order
     , o.current_state
     , x.id_order_state
     , x.date_add
  FROM order_history x 
  JOIN 
     ( SELECT id_order,MAX(date_add) date_add FROM order_history GROUP BY id_order ) y 
    ON y.id_order = x.id_order 
   AND y.date_add = x.date_add
  JOIN orders o
    ON o.id_order = x.id_order
 WHERE x.id_order_state <> o.current_state;
Comments