I am create a feature request web application where I am using sql to handle the feature request.
This is how my table looks.
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.
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.
BEGIN TRANSACTION UPDATE tableName SET ClientPriority = ClientPriority + 1 WHERE ClientPriority >= @priority INSERT INTO tableName (feature, priority) VALUES @feature, @priority COMMIT TRANSACTION
In Alchemy it's same only fluent Session = transaction so once you open it you good. http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html
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()