Search code examples
orientdbgraph-databasesorientdb2.2

OrientDB: How to return results from first or second query (if first returned no rows)

I have a common scenario with employees employed in departments:

  • employee has its parent department set by MY_PARENT_IS relation
  • same relation is used by departments against their parent departments.

The manager of a particular employee can be either:

  • set directly using IS_SPECIFIED_LEADER_FOR
  • first person that is set as IN_CHARGE_PERSON on employee's parent department (we go up the structure from the given employee until we find a department with IN_CHARGE_PERSON relation set).

Example structure:

Example structure

To achieve this goal I have 2 queries:

1st query to get specified leader for person:

SELECT expand(manager) FROM (MATCH {class:HRUnitPersonCompany, 
          where: (Name = 'Sales Berseker 1' AND in('IS_SPECIFIED_LEADER_FOR').size() > 0)}.in('IS_SPECIFIED_LEADER_FOR')
          {as: manager} RETURN manager)

2nd query finds first manager up the structure (IN_CHARGE_PERSON)

SELECT expand(manager) FROM (MATCH {class:HRUnitPersonCompany, 
          where: (Name = 'Sales Berseker 1')}.out('MY_PARENT_IS')
          {while: (out('IN_CHARGE_PERSON').size() == 0), 
          where: (out('IN_CHARGE_PERSON').size() > 0)}.out('IN_CHARGE_PERSON')
          {as: manager} RETURN manager)

The question is how to combine these 2 queries into 1 statement that will either return results from the first one OR the second one but only in case the first one returned no results ?

I played around with LET but cannot get this working 100% (this casts query parsing error):

SELECT if($specMgr.size()>0,$specMgr,$depMgr)
LET $specMgr = (SELECT expand(manager) FROM (MATCH {class:HRUnitPersonCompany, 
          where: (Name = 'Sales Berseker 1' AND in('IS_SPECIFIED_LEADER_FOR').size() > 0)}.in('IS_SPECIFIED_LEADER_FOR')
          {as: manager} RETURN manager)),
$depMgr = (SELECT expand(manager) FROM (MATCH {class:HRUnitPersonCompany, 
          where: (Name = 'Sales Berseker 1')}.out('MY_PARENT_IS')
          {while: (out('IN_CHARGE_PERSON').size() == 0), 
          where: (out('IN_CHARGE_PERSON').size() > 0)}.out('IN_CHARGE_PERSON')
          {as: manager} RETURN manager))

The problem is with the condition, when I remove the comparison, then at least no error is reported but it does not work as expected. Why cannot I use the .size() > 0 comparison in the if statement ?

Solution

  • OK, solved, it turned out I missed eval() and did not read that content of eval must be put between quotes. The working query is as follows:

    SELECT EXPAND(if(eval('$specMgr.size() > 0'),$specMgr,$depMgr))
LET $specMgr = (SELECT expand(manager) FROM (MATCH {class:HRUnitPersonCompany, 
          where: (Name = 'Sales Berseker 1' AND in('IS_SPECIFIED_LEADER_FOR').size() > 0)}.in('IS_SPECIFIED_LEADER_FOR')
          {as: manager} RETURN manager)),
$depMgr = (SELECT expand(manager) FROM (MATCH {class:HRUnitPersonCompany, 
          where: (Name = 'Sales Berseker 1')}.out('MY_PARENT_IS')
          {while: (out('IN_CHARGE_PERSON').size() == 0), 
          where: (out('IN_CHARGE_PERSON').size() > 0)}.out('IN_CHARGE_PERSON')
          {as: manager} RETURN manager))