MySQL query for the logic of three states from two fields

Question

I have a simple order, order_items, order_status schema:

As can be seen, and order can have several order_items. Each order_item have two (Boolean) fields, checked_in and verified.

The status of an order takes the values, ORDERED, RECEIVING, RECEIVED and CANCELED.

After checking in, or verifying an order_item, I want to create a database trigger (on the order_items table), that queries the values for the checked_in and verified fields, for each order_item.

If none of the fields are set, the order status is 'ORDERED'. If all fields are set, the order status is 'RECEIVED'. If anything in between, the order status is 'RECEIVING'.

Currently the status is set 'manually', by running the following query

SELECT oi.checked_in, oi.verified 
FROM order_items AS oi
WHERE order_id = 54;

Which yields a result like this:

The full code looks like this:

    dsl_shared_ptr<TSQLQuery> q(new TSQLQuery(NULL));
    q->SQLConnection = LITDBConnectionDM->SQLConnection1;

    int orderID = OrdersCDS->FieldByName("id")->AsInteger;

    stringstream query;
    query <<
"\
SELECT oi.checked_in, oi.verified \
FROM order_items AS oi \
WHERE order_id = :oID";
    q->SQL->Add(query.str().c_str());
    q->Params->ParamByName("oID")->AsInteger = orderID;
    q->Open();
    q->First();

    int checkedInCount(0);
    int verifiedCount(0);
    int recordCount(0);
    if(!q->IsEmpty())
    {
        while(!q->Eof)
        {
            recordCount++;
            if(q->FieldByName("checked_in") && q->FieldByName("checked_in")->AsInteger)
            {
                checkedInCount++;
            }

            if(q->FieldByName("verified") && q->FieldByName("verified")->AsInteger)
            {
                verifiedCount++;
            }
            q->Next();
        }
    }

    string orderStatus("");
    if(checkedInCount == 0 && verifiedCount == 0)
    {
        orderStatus = "ORDERED";
    }
    else if(checkedInCount == verifiedCount && checkedInCount == recordCount)
    {
        orderStatus = "RECEIVED";
    }
    else
    {
        orderStatus = "RECEIVING";
    }

    int order_status = getIDForOrderStatus(orderStatus);
    OrdersCDS->Edit();
    OrdersCDS->FieldByName("status")->AsInteger = order_status;
    OrdersCDS->Post();
    OrdersCDS->ApplyUpdates(0);

As can be seen, I'm 'manually' deducing the order status by checking the verified, checked_in flags, for each order_item.

How can the above be achieved in a SQL query inside a trigger?

Answer 1

Basically I think the update query you want to write is:

update orders o
inner join (
    select 
        order_id, 
        max(checked_in + verified) max_verif, 
        min(checked_in + verified) min_verif
    from order_items AS oi
    where order_id = 54
    group by order_id
) oi on oi.order_id = o.order_id
inner join order_status s on s.status = case
    when max_verif = 0 then 'ORDERED'
    when min_verif = 2 then 'RECEIVED'
    else 'RECEIVING'
end
set o.status = s.id

The logic is to aggregate the order items; we can decide which status should be assigned to the order by looking at the min() and max() of the addition of the two statuses of each item. We then bring the status table to translate the description of the status into the relevant primary key value.

Note that storing this information is not necessarily a good idea. Extra effort is required to keep the information up to date when the statuses of order items change (or when new items are added to an order, if that's possible in your use case).

Starting from the above query, you could very well create a view, that computes the information on the fly whenever queried.