CODEWITHSUNDEEP
grapharangodbaql
Mustanish Altamash
Mustanish Altamash

Reputation: 157

Graph query using AQL

I have 3 collections, 1st one is the list (vertices), 2nd one is the users (vertices) and the third one is the activity (edges). I plan to store any kind of activity done on a list in the activity collection (such as like, comment, flag and so on). suppose I need to fetch all lists of any user along with the count of each event on every list he created, like no of like like, comments, flag on each list in a single query. How do I do that? I have been exploring arangodb docs for like 2 days but can't figure out how to write such query. enter image description here

Upvotes: 0

Views: 287

Answers (1)

CodeManX
CodeManX

Reputation: 11855

If each individual activity is represented as an edge between both of your vertex collections, then find a user, retrieve its neighbors (the list vertices) and group the edges by type (an edge attribute?):

FOR u IN users
  FILTER u._key == "john"
  FOR v, e IN 1..1 OUTBOUND u activities
    // v is a list vertex document
    // e is an activity edge document
    COLLECT list = v._id, type = e.type, user = u._id
    WITH COUNT INTO count
    RETURN { list, user, type, count }

The result with my test data:

list        | user       | type    | count
------------|------------|---------|------
lists/john1 | users/john | comment | 1
lists/john1 | users/john | modify  | 3
lists/john1 | users/john | vote    | 1
lists/john2 | users/john | delete  | 1
lists/john2 | users/john | like    | 1
lists/john2 | users/john | modify  | 2
lists/john2 | users/john | vote    | 2

If you want to keep the list and user document, you could do:

FOR u IN users
  FILTER u._key == "john"
  FOR v, e IN 1..1 OUTBOUND u activities
    COLLECT list = v._id, type = e.type
    AGGREGATE count = LENGTH(1) INTO groups
    RETURN {
        list: groups[0].v,
        user: groups[0].u,
        type,
        count
    }

For large amounts of data the following might use less memory because it avoids keeping track of groups and looks up the necessary documents instead:

FOR u IN users
  FILTER u._key == "john"
  FOR v, e IN 1..1 OUTBOUND u activities
    COLLECT list_id = v._id, user_id = u._id, type = e.type
    WITH COUNT INTO count
    RETURN {
        list: DOCUMENT(list_id),
        user: DOCUMENT(user_id),
        type,
        count
    }

Upvotes: 1

Related Questions