I am new to Neo4j Cypher queries. I am getting HPALM data in Neo4j & then rendering it on grafana. HPALM creates a new row with every test run under the same "test-id". Due to this the summary test status shows wrong count due to duplicate entries with one row showing as status=Passed (latest run) & another as status=Failed (older run). But I need only the latest row data, the one with status=Passed in this example. below is the query which shows sample data having duplicate enrties/ rows but with different test 'status' & different 'last-modified' time. I need query to extract the unique / distinct latest entries & filter out older duplicate entries/ rows.
you can see first 2 rows with same id=44079, test-id=72534 but different status Failed & Passed & different 'last-modified' date time.
MATCH (a:HP) WHERE a.project='CHP' and a.type='test-instance' and a.`cycle-id` ='11743' RETURN distinct
a.id, a.`test-id`, a.`exec-date`, a.`last-modified`, a.status,
a.name, a.owner order by
a.id╒═════╤═══════════╤═════════════╤═══════════════════╤═════════════╤══════╤═══════╕
│a.id │a.`test-id`│a.`exec-date`│a.`last-modified` │a.status │a.name│a.owner│
╞═════╪═══════════╪═════════════╪═══════════════════╪═════════════╪══════╪═══════╡
│44079│72534 │2018-03-09 │2018-03-09 07:42:30│Failed │(null)│g942477│
├─────┼───────────┼─────────────┼───────────────────┼─────────────┼──────┼───────┤
│44079│72534 │2018-03-20 │2018-03-20 13:01:31│Passed │(null)│g942477│
├─────┼───────────┼─────────────┼───────────────────┼─────────────┼──────┼───────┤
│44080│72533 │2018-03-07 │2018-03-07 11:12:54│Failed │(null)│g942477│
├─────┼───────────┼─────────────┼───────────────────┼─────────────┼──────┼───────┤
│44080│72533 │2018-03-21 │2018-03-21 10:42:11│Passed │(null)│g942477│
├─────┼───────────┼─────────────┼───────────────────┼─────────────┼──────┼───────┤
│44081│72532 │2018-03-05 │2018-03-05 11:51:28│Passed │(null)│g942477│
├─────┼───────────┼─────────────┼───────────────────┼─────────────┼──────┼───────┤
│44082│72526 │2018-03-05 │2018-03-05 11:56:54│Failed │(null)│g942477│
├─────┼───────────┼─────────────┼───────────────────┼─────────────┼──────┼───────┤
│44082│72526 │2018-03-21 │2018-03-21 10:57:40│Not Completed│(null)│g942477│
Additionally if this is resolved I need to inject the same logic in below summary query to filter out the duplicates:
Currently the real count of Failed, No Run & Not Completed shows higher than expected due adding up of duplicates.
MATCH (a:HP)
WHERE a.project='CHP' and a.type='test-instance' and a.`cycle-id`='11743'
RETURN a.status , count(*) as cnt, a.`cycle-id` order by a.status
╒═════════════╤═══╤════════════╕
│a.status │cnt│a.`cycle-id`│
╞═════════════╪═══╪════════════╡
│Failed │4 │11743 │
├─────────────┼───┼────────────┤
│No Run │2 │11743 │
├─────────────┼───┼────────────┤
│Not Completed│5 │11743 │
├─────────────┼───┼────────────┤
│Passed │31 │11743 │
└─────────────┴───┴────────────┘
Any help to resolve this is most appreciated. I need help forming query with "Group by" & "having" clause here with example. I also need help creating query for sorting by date time.
Cannot find correct functions for date time addition / subtraction & sorting as the field type is "string". Let me know if you need more information.
I am using version 3.0.6 of Neo4j
Version:
3.0.6
Name:
graph.db