RethinkDB: Outer Join from Table to Array

Question

I have three tables, classes, courses and userSchedules that look like this:

classes { "classId": "24ab7b14-f935-44c1-b91b-8598123ea54a", "courseNumber": "PO101" , "days": "MWF" , "professor": "Abramius Darksbayn" , "seatsAvailable": 23 , "time": 11 }

courses { "courseNumber": "PO101" , "courseName": "Intro to Potions" , "creditHours": 3 , "description": "Potions..." }

userSchedules { "userId": "123", "classes": [ "24ab7b14-f935-44c1-b91b-8598123ea54a", "ab7b4414-a833-4ac2-c84a-98123ea54a97" ] , }

I want to write a query that joins classes and courses (on courseNumber) and then outer-joins to userSchedules. So I want to always return all classes and their corresponding course plus I want to return if the user is enrolled in that class. So I'd like the result to look like this:

{ "classId": "24ab7b14-f935-44c1-b91b-8598123ea54a", "days": "MWF" , "professor": "Abramius Darksbayn" , "seatsAvailable": 23 , "time": 11, "course": { "courseNumber": "PO101" , "courseName": "Intro to Potions" , "creditHours": 3 , "description": "Potions..." }, isEnrolled: true }

I'm really struggling to figure out how to make this work at all and then secondly, what the most performant way is to do this. Any help would be greatly appreciated! Thanks!

EDIT Ok, I found a way to do most of it, but it seems like really a lot of code and I also wonder about it's performance. And the !!result("right") always returns true even when there is no "right" side from the outer join. Is there a better way to do this:

r.table('classes') .eqJoin('courseNumber', r.table('courses')) .outerJoin( r.table('userSchedules').getAll(userId).concatMap(schedule => schedule('classes')), (joinedClass, scheduledClassId) => joinedClass('left')('classId').eq(scheduledClassId)) .map(result => { return { classId: result('left')('left')('classId'), days: result('left')('left')('days'), professor: result('left')('left')('professor'), seatsAvailable: result('left')('left')('seatsAvailable'), time: result('left')('left')('time'), course: result('left')('right'), enrolled: result.hasFields('right') } })

EDIT 2: I figured out why isEnrolled isn't working. And I've edited the above query to show my recent effort.

Answer 1

If you want to just make your query more readable you can change it to this one:

const allUserClasses = 
          r.table('userSchedules')
           .getAll(userId)
           .concatMap(schedule => schedule('classes'))

r.table('classes')
  .eqJoin('courseNumber', r.table('courses'))
  .map(joined => joined('left').without('courseNumber').merge({course: joined('right') })
  .outerJoin(allUserClasses,
    (joinedClass, scheduledClassId) => joinedClass('classId').eq(scheduledClassId))
  .map(result => result('left').merge({ enrolled: result.hasFields('right') })

Also if you have a fixed small array outer join is always an overkill and this is more appropriate:

r.table('userSchedules')
 .get(userId)('classes')
 .do(userClasses =>
    r.table('classes')
     .eqJoin('courseNumber', r.table('courses'))
     .map(joined => joined('left')
         .without('courseNumber')
         .merge({
            course: joined('right'),
            enrolled: userClasses.contains(joined('left')('classId')) 
         })
     )
 )