I am developing a server that takes requests sent by POST and distributes them to active agents querying a MySQL database. I have two tables, the users table and the requests table. Every time I get a new request I run a query that queries the active agents and sorts them by number of requests in queue and the timestamp of the last action performed. So far it works fine.

The problem is when I have an agent that needs to disconnect, I have to redistribute the queued requests among the other agents. To do this I implemented a forEach that goes through each request, then I perform the query of active agents and take the first row and assign the request to that one. The flaw is that I don't know why the same agent is being assigned the same task, being that the active agent query is repeated for each request and therefore should increase the amount of queued requests of the immediately previous agent.

Here is the code I have implemented:

app.post('/panel/redist', function (peticion, respuesta) {
    pool.getConnection(function (error, connection) {
        let query = ''

        // Look pends requests ## 
        query = `select * from solicitudes where status = 1`
        connection.query(query, function (error, solicitudes, campos) {
            if (solicitudes.length > 0) {
                solicitudes.forEach(solicitud => {
                    // Look active agents and order by number of requests and timestamp
                    query = `select usuarios.id, nombre, count(agente) conteo, last_action from usuarios
                                left join solicitudes on usuarios.id = solicitudes.agente
                                    where usuarios.login = 'active' and tipo_usuario = 1
                                        group by nombre
                                            order by  conteo asc, last_action asc`

                    connection.query(query, function (error, agentes, campos) {
                        if (agentes.length > 0) {
                            query = `UPDATE solicitudes SET agente = ${connection.escape(agentes[0].id)}, status = '2' WHERE id=${solicitud.id}`
                            connection.query(query, function (error, campos, filas) {
                                console.log(`Success asign to:${solicitud.id}`)
                            })
                        }
                    })
                })
            }
        })
        connection.release()
        respuesta.json({
            type: 'success'
        })
    })
})

This is in Node.js using mysql2 connector.

Thank you.