Publishing Efficient Joined Queries

Suppose that you were trying to publish the following parameterized query: (Example SQL data is listed at the end of this page)

SELECT
  students.name AS student_name,
  students.id AS student_id,
  assignments.name,
  assignments.value,
  scores.score
FROM
  scores
INNER JOIN assignments ON
  (assignments.id = scores.assignment_id)
INNER JOIN students ON
  (students.id = scores.student_id)
WHERE
  assignments.class_id = ?;

The result set of this query will need to be refreshed when:

• An assignments row changes that matches the class_id given
• Any students row included in the results changes (in case the name changes)
• Or any scores row changes that matches an assignments row with the correct class_id

In order to write triggers for this joined query, an extra supporting query to load all of the assignments rows that match the selected class_id.

The following code listing provides a complete Meteor.publish function for this query:

ECMAScript 6 'template strings' are used in the following example to make writing SQL queries in Javascript code much easier. Read more about ES6 template strings or check out the grigio:babel package for using ES6 with Meteor...

Meteor.publish('myClassScores', function(classId) {
  if(typeof classId !== 'number')
    throw new Error('classId must be integer');

  // Triggers require caches of columns that are joined on
  var assignmentIds = [], studentIds = [];

  // Prepare supporting query to main query
  var classAssignments = liveDb.select(`
      SELECT
        id
      FROM
        assignments
      WHERE
        class_id = ${liveDb.db.escape(classId)}
    `, [ {
      table: 'assignments',
      condition: function(row, newRow){
        return row.class_id === classId ||
          (newRow && newRow.class_id === classId);
      }
    } ]
  );
  // Update cache on new data
  classAssignments.on('update', function(diff, results) {
    assignmentIds = results.map(function(row) { return row.id });
  });

  // Subscription has been stopped, also stop supporting query
  this.onStop(function() {
    classAssignments.stop();
  });

  var classScores = liveDb.select(`
    SELECT
      students.name AS student_name,
      students.id AS student_id,
      assignments.name,
      assignments.value,
      scores.score
    FROM
      scores
    INNER JOIN assignments ON
      (assignments.id = scores.assignment_id)
    INNER JOIN students ON
      (students.id = scores.student_id)
    WHERE
      assignments.class_id = ${liveDb.db.escape(classId)};
  `, [
    {
      table: 'assignments',
      condition: function(row, newRow) {
        // newRow only passed on UPDATE events
        return row.class_id === classId ||
          (newRow && newRow.class_id === classId)
      }
    },
    {
      table: 'students',
      condition: function(row, newRow) {
        // Check if the id of this row matches cache value set
        return studentIds.indexOf(row.id) !== -1 ||
          (newRow && studentIds.indexOf(newRow.id) !== -1)
      }
    },
    {
      table: 'scores',
      condition: function(row, newRow) {
        // Check if the assignment_id of this row matches cache value set
        return assignmentIds.indexOf(row.assignment_id) !== -1 ||
          (newRow && assignmentIds.indexOf(newRow.assignment_id) !== -1)
      }
    }
  ]);

  classScores.on('update', function(diff, results) {
    // Update student_id cache
    studentIds = results.map(function(row) { return row.student_id });
  });

  return classScores;
});

Appendix

Sample SQL

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `students` VALUES
  (1 , 'John Doe'),
  (2 , 'Larry Loe');

CREATE TABLE `assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;


INSERT INTO `assignments` VALUES
  (1 , 1 , 'Homework'    , 10),
  (2 , 1 , 'Test'        , 100),
  (3 , 2 , 'Art Project' , 30),
  (4 , 1 , 'HW 2'        , 10),
  (5 , 1 , 'HW 3'        , 10);


CREATE TABLE `scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assignment_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;


INSERT INTO `scores` VALUES
  (1 , 1 , 1 , 10),
  (2 , 1 , 2 , 8),
  (3 , 2 , 1 , 70),
  (4 , 2 , 2 , 82),
  (5 , 3 , 1 , 15),
  (8 , 5 , 1 , 10);