Neel Patel Neel Patel - 1 year ago 79
SQL Question

How do you prioritize SQL column values based on what is being insert?

I am create a feature request web application where I am using sql to handle the feature request.
This is how my table looks.

enter image description here

I have "id, title, description, client, clientPriority, date, and etc..

So lets say "Client 1" requests for some feature and sets the clientPriority to "1". Then they come back next day and requests for another feature and want to set the priority "1". So the first feature, which was requested with priory "1" should be pushed down to "2". Is it possible to accomplish this with sql? If so, how can I accomplish this with sql table?

I am using Python sqlAlchemy.

Answer Source

Well you have to run 2 queries in one transaction.

First it to move priorities @priority is what you going to insert (could be 1 or anything). Following assumes you want to move full stack. Which will make sense. Modify to "=" if you want to move one.


UPDATE tableName 
SET ClientPriority = ClientPriority + 1 
WHERE ClientPriority >= @priority

INSERT INTO tableName (feature, priority) 
VALUES @feature, @priority


In Alchemy it's same only fluent Session = transaction so once you open it you good.

tablename.update().where(ClientPriority>=@priority).   values(ClientPriority=ClientPriority+1)
tablename.insert.values(feature=@feature, ClientPriority=@priority)

Adding the trigger as final solution from Neel Patel comment:

@event.listens_for(RequestedFeatures, 'before_insert') def receive_before_insert(mapper, connection, target): query = "UPDATE features SET clientPriority = (clientPriority + 1) WHERE client = '" query += target.client + "' AND clientPriority >= " + str(target.clientPriority) qr = session.execute(query) session.commit()