Arangodb AQL nested subqueries relying on the data from another

user_19521721 0
Viewed 57

I currently have three collections that need to be routed into one endpoint. I want to get the Course collection sort it, then from that course, I have to use nested subqueries to fetch a random review(there could be multiple tied to the same course) and also get the related user.

User{
    name:
    _id:User/4638
    key: ...}
Review{
    _from: User/4638
    _to: Course/489
    date: ....}
Course{
   _id: Course/489
   title: ...}

The issue I'm having is fetching the user based on the review. I've tried MERGE, but that seems to limit the query to one use when there should be multiple. Below is the current output using LET.

 "course": {
        "_key": "789",
        "_id": "Courses/789",
        "_rev": "_ebjuy62---",
        "courseTitle": "Pandas Essential Training",
        "mostRecentCost": 15.99,
        "hours": 20,
        "averageRating": 5
      },
      "review": [
        {
          "_key": "543729",
          "_id": "Reviews/543729",
          "_from": "Users/PGBJ38",
          "_to": "Courses/789",
          "_rev": "_ebOrt9u---",
          "rating": 2
        }
      ],
      "user": []
    },

Here is the current LET subquery method I'm using. I was wondering if there was anyway to pass or maybe nest the subqueries so that user can read review. Currently I try to pass the LET var but that isn't read in the output since a blank array is shown.

    FOR c IN Courses
        SORT c.averageRating DESC
        LIMIT 3 
            LET rev = (FOR r IN Reviews
                FILTER c._id == r._to
                SORT RAND()
                LIMIT 1
                RETURN r)
            LET use = (FOR u IN Users
                FILTER rev._from == u._id
                    RETURN u)
        
            
        RETURN {course: c, review: rev, user: use}`
aql arangodb
1 Answers

The result of the first LET query, rev, is an array with one element. You can re-write the complete query two ways:

  1. Set rev to the first element of the LET query result:
FOR c IN Courses
        SORT c.averageRating DESC
        LIMIT 3 
            LET rev = (FOR r IN Reviews
                FILTER c._id == r._to
                SORT RAND()
                LIMIT 1
                RETURN r)[0]
            LET use = (FOR u IN Users
                FILTER rev._from == u._id
                    RETURN u)
        
            
        RETURN {course: c, review: rev, user: use}

I use this variant in my own projects.

  1. Access the first elememt og rev in the second LET query:
FOR c IN Courses
        SORT c.averageRating DESC
        LIMIT 3 
            LET rev = (FOR r IN Reviews
                FILTER c._id == r._to
                SORT RAND()
                LIMIT 1
                RETURN r)
            LET use = (FOR u IN Users
                FILTER rev[0]._from == u._id
                    RETURN u)
        
            
        RETURN {course: c, review: rev, user: use}

This is untested, the syntax might need slight changes. And you have to look at cases where there aren't any reviews - I can't say how this behaves in that case from the top of my head.

user_5941380
Related