Possible Join interface

gistfile1.md

For the given sample database, the following example illustrates a possible interface for joining SQL tables within Minimongo.

Sample Data

Table classes

id	name	teacher
1	algebra	1234

Table students

id	name
34	john doe
42	larry loe

Table assignments

id	class_id	name	value
7	1	hw	10

Table scores

id	assignment_id	student_id	score
1	7	34	9
2	7	42	8

Use Case

Perform a query that returns all of the scores for every assignment in a class along with data from the student and assignment.

SELECT
  students.name,
  students.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;

Expected Query Results

name	id	name	value	score
john doe	34	hw	10	9
larry loe	42	hw	10	8

Example Code

The following shows a possible syntax for recreating this type of query using the SQL.Table object type.

var assignments = new SQL.Table('assignments');
var students = new SQL.Table('students');
var scores = new SQL.Table('scores');

function classScoreCursor = function(classId){
  return scores
    // INNER JOIN assignments ON (assignments.id = scores.assignment_id)
    // Uses a "column-reference": `scores.col.assignment_id` (described below)
    .join(assignments, { id: scores.col.assignment_id })
    // INNER JOIN students ON (students.id = scores.student_id)
    .join(students, { id: scores.col.student_id })
    // With joined cursors, the columns from each table are available in subdocuments
    .find({ 'scores.class_id': classId });
}

if(Meteor.isServer){
  Meteor.publish('classScores', function(classId){
    return classScoreCursor(classId);
  });
}else if(Meteor.isClient){
  Meteor.subscribe('classScores', 1);

  // Provide score data for example "myView" template
  Template.myView.helpers({
    scores: function(){
      return classScoreCursor(1);
    }
  });
}

// Expected return from classScoreCursor(1).fetch():
[
  {
    scores: {
      id: 1,
      assignment_id: 7,
      student_id: 34,
      score: 9
    },
    assignments: {
      id: 7
      class_id: 1
      name: 'hw'
      value: 10
    },
    students: {
      id: 34,
      name: 'john doe'
    }
  },
  {
    scores: {
      id: 2,
      assignment_id: 7,
      student_id: 42,
      score: 8
    },
    assignments: {
      id: 7
      class_id: 1
      name: 'hw'
      value: 10
    },
    students: {
      id: 42,
      name: 'larry loe'
    }
  }
]

SQL.Table cursor object

The preceding example displayed a usage of the following method and property.

SQL.Table.prototype.join(table, on)

Argument Name	Type	Description
table	Table cursor	Pass another Table to join rows
on	{ <column>: <column reference> }	Object describing which field(s) on the joining table to use for match. A <column reference> will be exposed on the Table instance.

SQL.Table.prototype.col

Object containing column references available on a table for use with the join() method.

On the server, each property of this object will provide the table and column names for use in extending the query.

> scores.col.student_id

"scores.student_id"

The join() method on the server will be able to use this string to write the correct SQL statement.

---

On the client, each property of this object will be a usable Mongo selector containing the values available for that column.

> scores.col.student_id

{ $in: [ 34, 42 ] }

The join() method on the client will use this selector for finding relevant Minimongo documents.