Combining 3 Cypher Queries in 1

Question

I have a business model in a Spring Application where Drugs exist. The theory behind this is, that the user has a global search function(like Google) with just one input field.

• Drugs got a TradingName, expireDate.

• Drugs are in physical Slots.

• Drugs contain Substances.

• (d:Drug), (s:Slot), (sub:Substances)

• d.tradingName, d.expireDate, sub.substanceName are Strings.

• s.number is a Long

Examples for search from a user in the input field.

1. Depon (is a trading name for a Drug)

2. Paracetamol (Substance Name)

3. 355 (He wants to know which Drug is in Slot 355)

4. 2021-02 (He wants to see what Drugs are expiring at February 2021)

The point is, that whatever the user searches for, the output should be the same. A list of the Drugs with all its information, but I am handling this in my frontend.

Now I have 3 queries.

1)

MATCH (d:Drug)
WHERE toLower(d.tradingName) CONTAINS toLower({0}) OR toLower(d.expire) CONTAINS toLower({0})
WITH d, datetime(d.expire) as expireDate
RETURN (d)-[]-()
ORDER BY expireDate

this one is working perfectly and is also ordering outputs, but its not searching in Slot und Substance.

2)

MATCH (s:Slot), (d:Drug) WHERE s.number = ({0}) return (d)-[]-(s)

MATCH (sub:Substances), (d:Drug)
WHERE toLower(sub.substanceName) CONTAINS toLower({0})
RETURN (sub)-[]-(d)

How is it possible to combine all this queries to one and in the end output the ordered to expire date Drugs with its relations to Slot and Substance?

Output in the end should look something like that.

d.tradingName - d.expire - sub.substanceName - s.number

Answer 1

if you have Neo4j 4, you can make a union query and post-process the result with your order by.

CALL {
  MATCH (d:Drug) 
  WHERE toLower(d.tradingName) CONTAINS toLower({0}) OR toLower(d.expire) CONTAINS  toLower({0}) 
  WITH d, datetime(d.expire) as expireDate 
  RETURN (d)-[]-() AS path, , datetime(d.expire) as expireDate
  UNION
  MATCH (s:Slot), (d:Drug) WHERE s.number = ({0}) 
  RETURN (d)-[]-(s) AS path, , datetime(d.expire) as expireDate
  UNION
  MATCH (sub:Substances), (d:Drug) 
  WHERE toLower(sub.substanceName) CONTAINS toLower({0}) 
  RETURN (sub)-[]-(d) AS path, , datetime(d.expire) as expireDate
} WITH path, expireDate
RETURN path
ORDER BY expireDate

This is a link that explain it ; https://graphaware.com/neo4j/2020/01/17/post-union-processing-explained.html

Answer 2

You can combine all 3 queries and order by expiredate this way (with empty lines inserted for clarity):

WITH '(?i).*' + {0} + '.*' AS regex

MATCH p1=(d:Drug)--()
WHERE d.tradingName =~ regex OR d.expire =~ regex
WITH regex, COLLECT(p1) AS ps

MATCH p2=(:Drug)--(s:Slot)
WHERE s.number = {0}
WITH regex, ps+COLLECT(p2) AS ps

MATCH p3=(:Drug)--(sub:Substances)
WHERE sub.substanceName =~ regex
WITH ps+COLLECT(p3) AS ps

UNWIND ps AS p
RETURN DISTINCT p
ORDER BY NODES(p)[0].expireDate;

The above query refines the case-insensitive string tests a bit by using a shared regular expression and the =~ operator.

Also, the query is a lot more efficient than the original ones, since it does not use MATCH clauses that cause the creation of cartesian products (e.g., MATCH (s:Slot), (d:Drug)). Also helping to avoid cartesian products is the use of COLLECT between each "subquery" to reduce the number of result rows to 1 before executing the subsequent MATCH clause.

Finally, the DISTINCT option is used to eliminate duplicate paths, since it seems theoretically possible for multiple subqueries to return the same path.