Many-to-Many relational support in JSData

Is there any way to define a many-to-many relationship in JSData?

For example, I have these 3 tables:

entity entityFile file

On the "entity", i want to have a relationship called "files" which does a join THROUGH entityFile.

Solution

Good question. A typical many-to-many relationship is just two one-to-many relationships:

One of the more important details in any implementation is: Where is the relationship information stored? The answer to this question determines how an entity's relations can be accessed. Let's explore a few options.

Premise:

A hasMany B

B hasMany A

Option 1

Relationship information is stored on instances of A.

In this scenario, once you have an instance of A you can find its associated instances of B, because the IDs of the associated B instances are stored on A. This also means that if you only have an instance of B, the only way to find all of the instances of A that the B instance relates to would be to search all of the instances of A for those whose b_ids field contains the id of the B instance.

An example

var Player = store.defineResource({
  name: 'player',
  relations: {
    hasMany: {
      team: {
        // JSData will setup a "teams" property accessor on
        // instances of player which searches the store for
        // that player's teams
        localField: 'teams',
        localKeys: 'team_ids'
      }
    }
  }
})

var Team = store.defineResource({
  name: 'team',
  relations: {
    hasMany: {
      player: {
        localField: 'players',
        // Since relationship information is stored
        // on the player, in order to retrieve a
        // team's players we have to do a O(n^2)
        // search through all the player instances
        foreignKeys: 'team_ids'
      }
    }
  }
})

Now let's see it in action:

var player = Player.inject({
  id: 1,
  team_ids: [3, 4]
})

// The player's teams aren't in the store yet
player.teams // [ ]

var player2 = Player.inject({
  id: 2,
  team_ids: [4, 5],
  teams: [
    {
      id: 4
    },
    {
      id: 5
    }
  ]
})

// See the property accessor in action
player2.teams // [{ id: 4 }, { id: 5 }]

// One of player one's teams is in the store now
player.teams // [{ id: 4 }]

// Access the relation from the reverse direction
var team4 = Team.get(4) // { id: 4 }

// The property accessor makes a O(n^2) search of the store because
// the relationship information isn't stored on the team
team4.players // [{ id: 1, team_ids: [3, 4] }, { id: 2, team_ids: [4, 5] }]

Let's load a relation from a persistence layer:

// To get an authoritative list of player one's 
// teams we ask our persistence layer.
// Using the HTTP adapter, this might make a request like this:
// GET /team?where={"id":{"in":[3,4]}} (this would be url encoded)
//
// This method call makes this call internally:
// Team.findAll({ where: { id: { 'in': player.team_ids } } })
player.DSLoadRelations(['team']).then(function (player) {

  // The adapter responded with an array of teams, which
  // got injected into the datastore.

  // The property accessor picks up the newly injected team3
  player.teams // [{ id: 3 }, { id: 4 }]

  var team3 = Team.get(3)

  // Retrieve all of team3's players.
  // Using the HTTP adapter, this might make a request like this:
  // // GET /player?where={"team_ids":{"contains":3}} (this would be url encoded)
  //
  // This method call makes this call internally:
  // Player.findAll({ where: { team_ids: { 'contains': team3.id } } })
  return team3.DSLoadRelations(['player'])
})

If you're using the HTTP adapter, then it's up to your server to parse the querystring and respond with the right data. If you're using any one of the other adapters then the adapter already knows how to return the right data. Using JSData on the frontend and backend just makes this too easy.

Option 2

Relationship information is stored on instances of B.

This is just the inverse of option 1.

Option 3

"A hasMany B" relationship information is stored on instances A, and "B hasMany A" relationship information is stored on instances of B.

This is just option 1 except that it now works in both directions.

An advantage of this approach is that you can access relation from both directions without the need to use the foreignKeys option. A disadvantage of this approach is that when relationships change to have to modify data in multiple places.

Option 4

Relationship information is stored in a pivot (junction) table.

A hasMany C and C belongsTo A, where the actual relationship information is stored in C.

B hasMany C and C belongsTo B, where the actual relationship information is stored in C.

An example:

var Player = store.defineResource({
  name: 'player',
  relations: {
    hasMany: {
      membership: {
        localField: 'memberships',
        // relationship information is stored on the membership
        foreignKey: 'player_id'
      }
    }
  }
})

var Team = store.defineResource({
  name: 'team',
  relations: {
    hasMany: {
      membership: {
        localField: 'memberships',
        // relationship information is stored on the membership
        foreignKey: 'team_id'
      }
    }
  }
})

And the pivot Resource:

var Membership = store.defineResource({
  name: 'membership',
  relations: {
    belongsTo: {
      player: {
        localField: 'player',
        // relationship information is stored on the membership
        localKey: 'player_id'
      },
      team: {
        localField: 'team',
        // relationship information is stored on the membership
        localKey: 'team_id'
      }
    }
  }
})

Now let's see it in action:

var player = Player.inject({ id: 1 })
var player2 = Player.inject({ id: 2 })
var team3 = Team.inject({ id: 3 })
var team4 = Team.inject({ id: 4 })
var team4 = Team.inject({ id: 5 })

player.memberships // [ ]
player2.memberships // [ ]
team3.memberships // [ ]
team4.memberships // [ ]
team5.memberships // [ ]

Notice at this point we can't yet access any relations

// The relationships stored in our pivot table
var memberships = Membership.inject([
  {
    id: 997,
    player_id: 1,
    // player one is on team three
    team_id: 3
  },
  {
    id: 998,
    player_id: 1,
    // player one is also on team four
    team_id: 4
  },
  {
    id: 999,
    player_id: 2,
    // team four also has player 2
    team_id: 4
  },
  {
    id: 1000,
    player_id: 2,
    // player 2 is also on team 5
    team_id: 5
  }
])

Now we have membership information

player.memberships // [{ id: 997, ... }, { id: 998, ... }]
player2.memberships // [{ id: 998, ... }, { id: 999, ... }]
team3.memberships // [{ id: 997, ... }]
team4.memberships // [{ id: 998, ... }, { id: 999, ... }]
team5.memberships // [{ id: 1000, ... }]

Now, it's a bit clunky to send your pivot table data to your frontend and require your JavaScript to sort through the relations. For that you would want some helper methods:

var Player = store.defineResource({
  name: 'player',
  relations: {...},
  computed: {
    teams: {
      get: function () {
        return store.filter('membership', {
          player_id: this.id
        }).map(function (membership) {
          return store.get('team', membership.team_id)
        })
      }
    }
  },
  // Instance methods
  methods: {
    getTeams: function () {
      return Player.getTeams(this.id)
    }
  }
  // Static Class Methods
  getTeams: function (id) {
    return this.loadRelations(id, ['membership']).then(function (memberships) {
      return store.findAll('team', {
        where: {
          id: {
            'in': memberships.map(function (membership) {
              return membership.team_id
            })
          }
        }
      })
    })
  }
})

I'll let you figure out the analogous methods for the Team resource.

If you don't want to go to the trouble of the helper methods, then you might just implement them on the backend to make your pivot table invisible to the frontend and make your many-to-many relationship look more like option 1, 2 or 3.

Useful links