0

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.

Jim Cooper
  • 5,113
  • 5
  • 30
  • 35

1 Answers1

2

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')) 
         })
     )
 )
RonZ
  • 367
  • 2
  • 8
  • I have another challenge that makes this query really complex. I now want to get the number of students enrolled in each class. For each class, this would be the number of users who have that classId in their userSchedules.classes array. The challenge is that this will be an aggregation but I still need to know if the current user is enrolled. Is there a way to do this without doing 2 queries? To make it more challenging, I want to listen to the changes so I can live-update the # of available seats for a class. – Jim Cooper Apr 08 '17 at 17:34
  • Perhaps at this point I should stop using an array to make the joins easier and/or more performant? – Jim Cooper Apr 08 '17 at 17:49
  • This will make the query much more complex since there are no changefeeds on joins. There are several approaches to it. One approach is to create a "materialized view" table that will hold all the information you'd like and you will fill it using listeners to changefeeds from other tables, it may be a bit costly. Another approach is to make a query inside the iteration through the infinite cursor of the previous query, this is a bit too complex to show in a comment though. – RonZ Apr 11 '17 at 07:08