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 = $1;

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...

This example is available on the join_example branch of the Postgres Leaderboard example app.

Meteor.publish('myClassScores', function(classId) {
  check(classId, Number);

  // 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 = $1`,
    // Query Parameters
    [ classId ],
    // Invalidation functions
    {
      assignments: function(row){
        return row.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 = $1`,
    // Query Parameters
    [ classId ],
    // Invalidation functions
    {
      assignments: function(row) {
        return row.class_id === classId;
      },
      students: function(row) {
        // Check if the id of this row matches cache value set
        return studentIds.indexOf(row.id) !== -1;
      },
      scores: function(row) {
        // Check if the assignment_id of this row matches cache value set
        return assignmentIds.indexOf(row.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 serial NOT NULL,
  name character varying(50) NOT NULL,
  CONSTRAINT students_pkey PRIMARY KEY (id)
);

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

CREATE TABLE assignments (
  id serial NOT NULL,
  class_id integer NOT NULL,
  name character varying(50),
  value integer NOT NULL,
  CONSTRAINT assignments_pkey PRIMARY KEY (id)
);

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 serial NOT NULL,
  assignment_id integer NOT NULL,
  student_id integer NOT NULL,
  score integer NOT NULL,
  CONSTRAINT scores_pkey PRIMARY KEY (id)
);

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);