How to persist the status of a queue of requests?

Question

I am working on a C# application that requires a row of items of the type Pedido. Basically whenever a new order is created it is queued and the application shows the orders in sequential order that must be delivered. When an order is finalized it leaves the queue.

For now what I’m doing is basically maintaining a property enum status in the requested class that when the request is created has value StatusPedido.Enviado and when the order is delivered has value StatusPedido.Entregue. The queue therefore only includes items in the sent state.

To persist this, it is not difficult at first. My first idea was to create a property DataModificacao in the requests and then order the requests by the modification date remembering that queues are "first in first out". Thus, the application reads the requests in the database, sorts and assembles the queue.

So far so good, the problem is this: there is a requirement for users to be able to reorder the queue. This is because sometimes an order that can be delivered faster can be advanced and passed in front of others.

Doing this in the code is easy. Basically it’s just exchanging references and everything works. But and to persist this? Only modification date does not solve more, because when loading the database requests will not have information of which have been advanced. I thought of a new property that has the order ID that should come next, and a property containing a reference to the next order but I don’t know if it’s a good approach, because this seems more a persistence implementation detail than something that is part of the same domain.

Also, an order when it is out of line (or before it is integrated into the queue, or after it is delivered) no longer has that next one, so the next order does not look like a class property Pedido even though it only makes sense in certain cases.

How can I persist with this sort of thing: a queue that can be reordered and needs to have its ordering persisted?

Answer 1

Approach 1: Priority

An approach to solve this would be to put a column Prioridade, which is a enumerable. For example:

public enum Prioridade
{
    Minima,
    Baixa,
    Media,
    Alta,
    Maxima
}

The standard would be Minima. Note that the order of enum is purposeful. Maxima will receive the highest entire value in all, and Minima the smallest.

Thus the ordering would be made first by the priority, then by the modification date.

Approach 2: Date of Order

Now, if an absolute order is really needed, it is best to use a DateTime with the date of order. By including the record, DataOrdem is equal to DataModificacao.

Next, implement a method that changes the order on screen. To do this, put in your Model one property calling for Ordem, bank-mapped:

[NotMapped]
public int Ordem { get; set; }

Assign values to the order of each element (1, 2, 3, 4...), allow the user to change the order on screen and when persisting, do DataOrdem receive DateTime.Now.

Thus, the first element inserted will be the first in the list as it has the smallest DataOrdem. The others will be significantly behind (a few milliseconds), which will ensure absolute reordering.

Answer 2

Actually, in my opinion, the order attribute is not part of a class Pedido. The best modeling would be to create a class ItemFila. See a proposal below.

Suggested implementation

class Fila{
    IList<ItemFila> Itens {get;set}
}

class ItemFila {
    public Pedido Pedido{get;set;}
    public int Posicao {get;set}
}

class Pedido {
    public DateTime CriadoEm {get;set}
    public StatusPedido Status {get;set;}
    // Dados do pedido
}

In this case, these classes would represent the same structure in the database, so every time you want get the queue, just load the Queue object.

Ordination

Whenever you get the Itemfila to compose the queue, you can use the select next:

SELECT 
    *
FROM 
    ItemFila
ORDER BY 
    Posicao
ASC

Thus, you will always have the queue in the order established in the database (I am considering that you are using a relational database).

Position update

Suppose your queue has 3 orders, and you need to pick up the last one and put in first. What you should do is add the value of the current position to all items in that row, provided that these items have the position value lower than the current position. Using SQL would look like this:

DECLARE @current AS INT;
SELECT @current = Posicao FROM ItemFila WHERE Id = 123456;

UPDATE
    ItemFila
SET
    Posicao = Posicao + @current
WHERE
    Fila_ID = 123
    AND Posicao < @current;

Edit It is necessary to incremementar the following items as well. See the following code:

UPDATE
    ItemFila
SET
    Posicao = Posicao + @current
WHERE
    Fila_ID = 123
    AND Posicao > @current;