Relationship between 2 collections Arangodb

Question

I have 2 collections :

• User
• Tweet

I want store in User the list of tweet_id and in each tweet the user_id. In my json Tweets files I have the tweet author champ.I try with AQL but I did not succeed, can you help me ?

Thanks

ps: I'm French student sorry for this broken English

Answer 1

If you have User documents like:

{
  "_key": "1234",
  "name": "John"
}

And Tweet documents like:

{
  "_key": "56789",
  "message": "Lorem ipsum sit dolor amet",
  "author": "1234"
}

Then you could use the following AQL query:

FOR tweet IN Tweet
  UPDATE tweet.author WITH { tweet_key: tweet._key } IN User
  RETURN NEW

It iterates over all documents in Tweet collection and tries to update documents in User collection based on the attribute value author, which must be identical to the document key in User. It adds the document key of the Tweet document as a new attribute to the User document.

If you didn't make the User ID and Tweet ID the document keys of the respective collections, then you have to use a different query. Let's assume User documents like:

{
  "_key": "376472856",
  "user_id": 1234,
  "name": "John"
}

And Tweet documents like:

{
  "_key": "8975438957",
  "tweet_id": 56789,
  "message": "Lorem ipsum sit dolor amet",
  "author": 1234
}

Then you should create a hash index (possibly unique) in User collection on field user_id and run the following query:

FOR tweet IN Tweet
  FOR user IN User
    FILTER user.user_id == tweet.author
    LIMIT 1
    UPDATE user WITH { tweet_id: tweet.tweet_id } IN User
    RETURN NEW

It will augment User documents with an attribute tweet_id equal to the tweet_id attribute in the corresponding Tweet document.