How can I use AQL with multiple queries that using the result of one another?

Question

I have 2 vertices and an edge named user, device, ownership respectively.

My business logic is when I receive device information, I upsert it with dateCreated and dateUpdated fields added. If I inserted that device then I insert new user with default values and create edge connection to it. If I update I simple return already connected user as a result.

Without losing atomicity how can I achieve this?

I tried single AQL query but without condition it is not possible it seems and traversal also is not supported with insert/update operation.

I can do separate queries but that loses atomicity.

var finalQuery = aql`
UPSERT ${deviceQuery} 
INSERT MERGE(${deviceQuery},{dateCreated:DATE_NOW()}) 
UPDATE MERGE(${deviceQuery},{dateUpdated:DATE_NOW()}) 
IN ${this.DeviceModel}
RETURN { doc: NEW, type: OLD ? 'update' : 'insert' }`;

var cursor = await db.query(finalQuery);
var result = await cursor.next();
if (result.type == 'insert') {
  console.log('Inserted documents')

  finalQuery = aql`
  LET user=(INSERT {
    "_key":UUID(),
      "name": "User"
    } INTO user
    RETURN NEW)
    
    INSERT {
    _from:${result.doc._id},
    _to:user[0]._id,
      "type": "belongs"
    }INTO ownership
    
    return user[0]`;

    cursor = await db.query(finalQuery);
    result = await cursor.next();
    console.log('New user:',result);
    
}

I doubt this can be achieved. You will keep running into `access after data-modification` error — gurisko, Nov 23 '20 at 14:58

score 0 · Answer 1 · answered Nov 19 '20 at 05:54

0

You can try something like this

Upsert ....

FILTER !OLD
Let model = NEW

LET user= First(INSERT {
    "_key":UUID(),
      "name": "User"
    } INTO user
    RETURN NEW)
    
    INSERT {
    _from:model._id,
    _to:user._id,
      "type": "belongs"
    }INTO ownership
    
    return user

answered Nov 19 '20 at 05:54

John Benavides

71
3

But this doesn't return user if update occurs. For both scenarios(update and insert) I need a user. – Erhan SEN Nov 19 '20 at 13:01

score 0 · Accepted Answer · answered Nov 25 '20 at 13:00

I end up separating the modification and selection queries.

var finalQuery = aql`
LET device=(
UPSERT ${deviceQuery} 
INSERT MERGE(${deviceQuery},{dateCreated:DATE_NOW()}) 
UPDATE MERGE(${deviceQuery},{dateUpdated:DATE_NOW()}) 
IN ${this.DeviceModel}
RETURN { doc: NEW, type: OLD ? 'update' : 'insert' })
FILTER device[0].type=='insert'
LET user=(INSERT {
"_key":UUID(),
"name": "User"
} INTO user
RETURN NEW)

INSERT {
_from:device[0].doc._id,
_to:user[0]._id,
"type": "belongs"
}INTO ownership
return user[0]`;

var cursor = await db.query(finalQuery);
var result = await cursor.next();
if (result == null) {
  const deviceId=this.DeviceModel.name+"/"+queryParams._key;
  finalQuery = aql`
  FOR v,e,p IN 1..1 
  OUTBOUND ${deviceId} ownership 
  FILTER e.type=="belongs" 
  RETURN v `;

  cursor = await db.query(finalQuery);
  result = await cursor.next();
  isUpdate=true;
}

This way I ensure the atomicity. There are improvements for controling if cursor.extra.stats.writesExecuted true etc.

How can I use AQL with multiple queries that using the result of one another?

2 Answers2