Запрос MySQL для логики трех состояний из двух полей

У меня есть простой заказ, order_items, схема order_status:

введите здесь описание изображения

Как видно, и order может иметь несколько order_items. Каждый order_item имеет два (логических) поля: checked_in и Verified.

Статус заказа принимает значения ЗАКАЗАН, ПОЛУЧЕН, ПОЛУЧЕН и ОТМЕНЕН.

После регистрации или проверки order_item я хочу создать триггер базы данных (в таблице order_items), который запрашивает значения для проверенных и проверенных полей для каждого order_item.

Если ни одно из полей не заполнено, заказ получает статус «ЗАКАЗАН». Если все поля заполнены, заказ получает статус «ПОЛУЧЕН». Если что-то среднее между ними, статус заказа «ПОЛУЧАЕТСЯ».

В настоящее время статус устанавливается «вручную», выполнив следующий запрос

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

Что дает такой результат:

введите здесь описание изображения

Полный код выглядит так:

    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);

Как видно, я «вручную» вывожу статус заказа, проверяя проверенные флаги checked_in для каждого элемента order_item.

Как это может быть достигнуто в SQL-запросе внутри триггера?


mysql sql triggers sql-update dbexpress
person Totte Karlsson    schedule 31.08.2020    source источник
comment
См. meta.stackoverflow.com/questions/333952/   -  person Strawberry    schedule 01.09.2020

Ответы (1)


arrow_upward
1
arrow_downward

По сути, я думаю, что запрос update, который вы хотите написать, выглядит следующим образом:

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

Логика состоит в том, чтобы агрегировать элементы заказа; мы можем решить, какой статус следует присвоить заказу, посмотрев на min() и max() добавления двух статусов каждого элемента. Затем мы используем таблицу status для перевода описания статуса в соответствующее значение первичного ключа.

Обратите внимание, что хранение этой информации не обязательно является хорошей идеей. Требуются дополнительные усилия для поддержания актуальности информации при изменении статусов элементов заказа (или при добавлении новых элементов в заказ, если это возможно в вашем случае использования).

Исходя из приведенного выше запроса, вы вполне можете создать представление, которое вычисляет информацию на лету при каждом запросе.

person GMB    schedule 31.08.2020
comment
Мы используем эти поля, чтобы напомнить нашим пользователям о заказах, которые не были зарегистрированы в нашем инвентаре. Не знаете, как избежать этих полей, и все еще знаете, был ли «зарегистрирован» order_item, связанный с заказом? Попробовал ваш запрос, и он отлично работает после некоторых правок! - person Totte Karlsson; 01.09.2020