58 lines
1.8 KiB
Plaintext
58 lines
1.8 KiB
Plaintext
// Path Analysis Query for Table View (Pure Cypher, Final & Performant)
|
|
// Calculates and returns path data in a clear, tabular format for a specific scenario.
|
|
|
|
// Parameter for the scenario ID
|
|
// Example: :scenario_id => 'parallel-process'
|
|
|
|
// 1. Find start nodes for the scenario using OPTIONAL MATCH
|
|
MATCH (start_node:Job)
|
|
OPTIONAL MATCH (start_node)<-[r_in:PRECEDES {scenario: $scenario_id}]-()
|
|
WITH start_node, r_in
|
|
WHERE r_in IS NULL
|
|
|
|
// 2. Find end nodes for the scenario using OPTIONAL MATCH
|
|
MATCH (end_node:Job)
|
|
OPTIONAL MATCH (end_node)-[r_out:PRECEDES {scenario: $scenario_id}]->()
|
|
WITH start_node, end_node, r_out
|
|
WHERE r_out IS NULL
|
|
|
|
// 3. Match paths and filter relationships efficiently using WHERE ALL
|
|
MATCH path = (start_node)-[rels:PRECEDES*]->(end_node)
|
|
WHERE ALL(r IN rels WHERE r.scenario = $scenario_id)
|
|
|
|
// 4. For each job in the path, calculate its effective duration and cost
|
|
WITH path, nodes(path) AS jobs_on_path
|
|
UNWIND jobs_on_path AS job
|
|
OPTIONAL MATCH (:Scenario {id: $scenario_id})-[m:MODIFIES]->(job)
|
|
WITH
|
|
path,
|
|
job,
|
|
COALESCE(m.new_duration, job.base_duration) AS effective_duration,
|
|
COALESCE(m.new_cost, job.base_cost) AS effective_cost
|
|
|
|
// 5. Group by path and aggregate the pre-calculated effective values
|
|
WITH
|
|
path,
|
|
COLLECT({
|
|
name: job.name,
|
|
bd: job.base_duration,
|
|
ed: effective_duration,
|
|
ec: effective_cost
|
|
}) AS job_data
|
|
WITH
|
|
path,
|
|
job_data,
|
|
REDUCE(s = 0, x IN job_data | s + x.ed) AS total_duration,
|
|
REDUCE(s = 0, x IN job_data | s + x.ec) AS total_cost
|
|
|
|
// 6. Return the calculated data for a clean table view
|
|
RETURN
|
|
[j IN job_data | j.name] AS path_jobs,
|
|
[j IN job_data | j.bd] AS base_durations,
|
|
[j IN job_data | j.ed] AS effective_durations,
|
|
total_duration,
|
|
total_cost
|
|
|
|
// 7. Order by duration
|
|
ORDER BY total_duration DESC;
|