I'm working on a project where I need to try to evenly distribute children nodes across parent nodes of the same type along with another requirement I'll explain as I setup the scenario.
Let's say I my starting point is this graph(s)
The orange Server nodes represent servers and the goal is to evenly distribute the Config nodes across the servers so that the customer configs do not land on the same server IF multiple servers are available.
The goal would to have the graph look something like this:
If however, there is only one server available, then I need to assign both Config nodes to the single Server;
If a customer were to add additional config but there's not a server where they DON'T already have config applied, the new config should be applied to a server that has the least amount of Configs applied to it.
Additionally, when the config nodes are applied, there needs to be two attributes on the config node set. Let's say customer_id
and config_id
.
The customer ID's range from let's say 1-100 and config ID's from 1000-2000. These are unique to the server the configs get applied to.
I'm about 90% there, but I think my query can be optimized and I the way I'm handling the ID's isn't the best because I'm essentially just looking for what's already configured, grabbing the max and adding 1, but if a config is deleted, I potentially lose an ID because I'm always grabbing the max.
CALL {
MATCH(config:Config)-[:CONFIG_FOR]->(c:Customer) WHERE config.state = 'SCHEDULED'
RETURN COLLECT(config) AS configs
}
UNWIND configs AS config
CALL {
WITH config
WITH config
// LOOK FOR AVAILABLE SERVERS IN THE SAME LOCATION THAT DON'T HAVE
// CONFIG FROM THE SAME CUSTOMER
Match(server:Server) WHERE server.location = config.location
AND NOT EXISTS((:Config{uuid: c.uuid})-[:CONFIG_FOR]->(server))
WITH server, config
LIMIT 1
MERGE(config)-[:CONFIG_FOR]->(server)
// SEARCH TO SEE IF THERE ARE OTHER CONFIGS APPLIED TO DETERMINE THE ID ASSIGNMENTS
OPTIONAL MATCH(c:Config)-[:CONFIG_FOR]->(server) WHERE c.uuid <> config.uuid
CALL {
WITH c, config
WITH c, config
WHERE c IS NOT NULL
WITH MAX(c.customer_id) + 1 AS customerId, MAX(c.config_id) + 1 AS configId, c
SET config.customer_id = customerId
SET config.config_id = configId
UNION
WITH c, config
WITH c, config
WHERE c IS NULL
SET config.customer_id = 1
SET config.config_id = 1000
}
}
// THIS NEXT SUBQUERY WAS ADDED TO HANDLE THE CASE WHERE THERE NO SERVERS AVAILABLE THAT DON'T ALREADY HAVE A CUSTOMER CONFIG APPLIED.
CALL {
OPTIONAL MATCH(config:Config)-[:CONFIG_FOR]->(c:Customer) WHERE config.state = 'SCHEDULED'
WHERE NOT EXISTS((config)-[:CONFIG_FOR]->(:Server))
Match(server:Server) WHERE server.location = config.location
WITH server, config
LIMIT 1
MERGE(config)-[:CONFIG_FOR]->(server)
WITH config, server
OPTIONAL MATCH(existingConfig:Config)-[:CONFIG_FOR]->(server) WHERE existingConfig.customer_id = config.customer_id
SET config.customer_id = existingConfig.customer_id
WITH config, server
MATCH(allConfigs:Config)-[:CONFIGURED_FOR]->(server)
WITH config, allConfigs ORDER BY allConfigs.config_id
WITH MAX(allConfigs.config_id) + 1 AS new_config_id, config
SET config.config_id = new_config_id
}
As mentioned at the start, the above query does seem to be assigning the configs in the way I would like, but I'm wondering if there's an easier way to go about it (less query statements), however, the generating of the ID's works in that I get the behavior I want, but let's say a server has configs with IDs 1 and 2, but then 1 is deleted, the next config will get 3 and I'm not able to re-use 1.