Associations

[mikermcneil]

Update:

Sails v0.10.0-rc3 is now available on npm.

Upgrading to Sails v0.10 beta:

Install: $ sudo npm install -g sails@beta -g

Docs: beta.sailsjs.org

Also see: https://github.com/balderdashy/waterline-docs

Migration Guide for v0.9.x apps: https://github.com/balderdashy/sails-docs/blob/master/Migration-Guide.md

We're working on improving docs in general, but we could really use your help- please feel free to submit pull requests to https://github.com/balderdashy/sails-docs. The changes go live to beta.sailsjs.org (and eventually sailsjs.org proper, when we set the latest tag on v0.10)

[mikermcneil]

Scott: when you do embedded models for waterline, are you going to just be adding in hidden fields like that, that are arrays or lists of object ids? 4:01 PM me: If I understand you correctly, yes 4:02 PM as in, how will it work w/ relational dbs? 4:03 PM Scott: even in nosql dbs...unless you are planning on duplicating the data, but then you have to make sure its always synced up

like if you have Department and User models

me: You mean the embed query helper thing in that issue? 4:04 PM Scott: yes

me: yeah, it's only useful for linked-relationships

where you have a foreign key

so in those cases, it grabs fresh data each time you do a query 4:06 PM Scott: I'm just looking at the issue again now, and it looks like you're planning to physically store the Group data in the User

model for NoSQL dbs

me: yeah

Scott: which is fine, but you have to update the Group in two places then 4:07 PM me: right

you can do it the other way though

what I had planned was, if you specify a "model" or "collection", it'll use a foreign key

otherwise it'll be stored phsyically

and it can only be stored physically in a nosql adapter

otherwise it'll bitch at you 4:08 PM but you can use the link/relational pattern even in a nosql db

Scott: And when you do an update on a Group, it will examine all the model defs, find which ones have embedded Groups, and search those models to find ones that need updating? 4:09 PM me: in the embedded pattern?

no

only in the relation/link pattern

in the NoSQL embedded pattern, Group doesn't actually exist 4:10 PM Scott: it's just an attribute of UseR?

me: right

you wouldn't have a Group model file

Scott: so you can query on it, but there's no Group model

got it

me: right 4:11 PM vs. associating the "Group" key with a "group" model

which would manage the relation for you

Scott: options

got it

me: Does that make sense? I think it might. Definitely could use more eyes on all of these things. 4:12 PM Scott: it does, and there are cases for both

ok--different topic, if you have a second

me: surr

[dcbartlett]

Should i documentate this?

[mikermcneil]

It would be good to explicitly mention that we're not yet supporting named associations in the docs, that we're waiting until we're sure we have it right, and link back here?

On Fri, Mar 15, 2013 at 6:36 AM, Dennis Bartlett notifications@github.comwrote:

Should i documentate this?

— Reply to this email directly or view it on GitHubhttps://github.com/balderdashy/sails/issues/124#issuecomment-14955991 .

Mike McNeil Founder http://www.linkedin.com/in/mikermcneil/ http://twitter.com/mikermcneil http://github.com/balderdashy

   C      O
  NFI    DEN
  TIA   L i
  nfo  rma
  tion in
   tended
only for t      he addressee(s).

If you are not the intended recipient, empl oyee or agent responsible for delivery to the intended recipient(s), please be aware that any review, dissemination, use,distribut ion or copying of this message and its contents is strictly prohibited. If you receive this email in error, ple ase notify the sender and destroy any paper or electronic copies immediately.

[mikermcneil]

https://gist.github.com/mikermcneil/5558560

[abuckton]

Hi Mike, I saw the proposal and that looks workable. What is the current status of the association/relational work ?

[anissen]

I would be very interested in this as well. The lack of mongoose-like populate functionality is the only thing that keeps me from migrating my project(s) to Sails.

[particlebanana]

So the first step was moving the ORM out of Sails which we are finishing now with Waterline. We also knocked out some of the low hanging fruit like validations. This will be in the 0.9 release.

The next step is definitely associations. Once the 0.9 release is out we can nail down an api then add it to Waterline pretty quickly I think. We will need to update the adapters to support the new options as well.

@anissen I love the populate method too and we will have something similar for sure. We already have the query builder interface so it works out perfect.

If anyone would like to take a look at the current state or follow the project it's all happening in balderdashy/waterline now.

[arianitu]

Please add foreign key constraints as a requirement to this task for associations defined using SQL.

[mikermcneil]

@abuckton To expand on what @particlebanana said, it's coming along. Waterline has validations now, as well as custom table/attribute names and lifecycle hooks. Associations is the last big feature for Sails, IMO, other than continued performance optimizations, stability and reliability updates, and more convenience blueprints (particularly around associations)

So v0.9 is looking like a release candidate for 1.0, and (fingers crossed) the last breaking change. I'm working to get it out first, with the migration guide. Then we can work associations into a subsequent 0.9.x release since it won't change the Sails API-- just extend it.

Thanks for checking in!

[tanertopal]

Is there any way of speeding up the work on associations? If you give me little overview I could work on it.

[kdocki]

I think it would be nice to see associations like this...

User.findOne().then(function(user) {
    user.roles = user.roles() // this isn't in 'with' so if we want to include it we need to do this
    res.json(user)
});

// might return { id: 1, roles: [{ id: 1, name: 'AdminRole'}, {id: 3, name: 'UserRole' }], primaryRole: { id: 3, name: 'UserRole' } }

var User = {
  with: [ 'primaryRole' ]
  attributes : {
    primaryRoleId : { 
        type: 'integer',
        required: true
    },
    primaryRole : function() {
       return this.hasOne('Role').where({id: this.primaryRoleId})
    },
    roles : function() {
       return this.hasManyAndBelongsTo('Role')
    }
  }
}

var Roles = {
   attributes: {
      users : function() {
          return this.hasAndBelongsToMany('User')
      }
   }
}

[mikermcneil]

@kdocki Thanks for the input-- that's similar to what we came up with!

@particlebanana, @Zolmeister, @sgress454, @ghernandez345, @irlnathan and I got together a few weeks ago and hashed out a spec for associations. Here's the latest, pasted from a note (so apologies for any typos, this is just to give you guys an early look and get your thoughts:)

It was an interesting challenge to provide a clean, uniform API for both noSQL and SQL databases, but as with the original design of Waterline, Hibernate and Mongoose provided an excellent starting place to work from. For the new parts, we tried to keep syntax consistent.

I include them below just for reference, but you'll notice that we completely did away with the belongs and has terminology in the actual usage. That's because they've always confused the hell out of me, mainly because they don't capture the true nature of the association. e.g. a Bear doesn't belongTo a Cave, he's just living there for a while!

Instead, in Waterline, you name the association yourself. You can use conventional semantics, or use your own. In the example above, I'd probably name the association cave so I could access bear.cave. But if I need to access the bear from the cave, I might name my association on the other end something like currentBear. By making your associations explicit, you're not only creating a more descriptive data model, but also making the usage of your models more intuitive. Not to mention you can effectively ignore an entire vocabulary (belongsTo, hasWhatever, yada yada) and make associations work just like any other attribute.

The downside? Since, in Node/JavaScript, we have callbacks to think of, calls to populate() have to be explicit (or risk encouraging extremely inefficient behavior). It's a trade-off, but a fact of life when working with Node.js, and if you're reading this, you're used to it. Populate is made available as a query modifier on find(), but not on instance methods. This is designed to discourage the sort of overzealous joining common in other MVC frameworks where blocking getters are used to do on-the-fly joins. I won't name any names :)

Associations In Sails

belongsTo

A belongs_to association sets up a one-to-one connection with another model, such that each instance of the declaring model "belongs to" one instance of the other model.

Definition

belongs_to :group
group : {
  model: 'Group'
}

Usage

// Finding by association
// automatically joins
User.findByGroup(groupId)

// Force a join ({})
User.find()
.populate('group')

hasOne

A has_one association also sets up a one-to-one connection with another model, but with somewhat different semantics (and consequences). This association indicates that each instance of a model contains or possesses one instance of another model.

Definition

has_one :group
group : {
  model: 'Group'
}

Usage

// Finding by association
// automatically joins
User.findByGroup(3)

// Force a join ({})
User.find()
.populate('group')

hasMany

A has_many association indicates a one-to-many connection with another model. You'll often find this association on the "other side" of a belongs_to association. This association indicates that each instance of the model has zero or more instances of another model.

Definition

has_many :role
roles: {
  collection: 'Role'
}

Usage

// Finding by association
// automatically joins
User.find()
.where({
  roles: [1,2,3]
})

// Force a join ([])
User.find()
.populate('roles')

## hasMany :through

A has_many :through association is often used to set up a many-to-many connection with another model. This association indicates that the declaring model can be matched with zero or more instances of another model by proceeding through a third model.

Definition

has_many :permission :directory
dirPermissions: {
  collection: 'Directory',
  type: 'string'
}

Usage

// Find accounts with access to this directory (2)
// (auto-populate dirPermissions)
Account.find()
.where({
  dirPermissions: [2]
})

// Force a join ([])
Account.find()
.populate('dirPermissions')

hasOne :through

A has_one :through association sets up a one-to-one connection with another model. This association indicates that the declaring model can be matched with one instance of another model by proceeding through a third model. Describe a 1-way relationship that has other data associated with it.

Definition

has_one :wedding :person
marriedTo: {
  model: 'Person',
  atBuilding: {
    model: 'Building'
  },
  atTime: 'datetime'
}

Usage

In this example, you'll notice we've recursively described this relationship. Weddings have a building, for instance.

// Find the person married to person #2
// (auto-populate wedding information)
Person.find()
.where({
  marriedTo: [2]
})

// Force a join ([])
Person.find()
.populate('marriedTo')

hasAndBelongsToMany

hasAndBelongsToMany is a 2-way n <-> n association. This means that if A hasAndBelongsToMany B, for any given model a of A, you can access a list of B models, x, and also for any given model b of B, you can access a list of A models, y.

Definition

has_and_belongs_to_many :label
// models/Message.js
labels: { 
  collection: 'Label'
}

// models/Label.js
attachedToMessages: {
  collection: 'Label'
}

Usage

// Finding by association
// automatically joins
// (both ways)
Message.where({
  labels: [2]
})

Label.where({
  attachedToMessages: [2,53,2]
})

// Force a join ([])
// (works both ways)
Message.find()
.populate('labels')

Label.find()
.populate('attachedToMessages')

[Foxandxss]

For a first shot on associations, it has more features than many of the competence.

About having to use .populate.... Is just syntax, what is important here are the features, how you have to use it doesn't matter that much but anyway, I like the syntax :+1:

[betacar]

Is this available in the master branch? I downloaded today SailsJS v0.9.3 and I would love to use this in my current development app. :)

[ktkaushik]

@betacar I think this is still in the making.

[tanertopal]

@mikermcneil Is there any time scale when this issue will be resolved? I'd love to help. Right now I desperately need this in one my client projects. Can I somehow get involved?

[particlebanana]

@tanertopal see #712 I'll try and get the base started this weekend so we have something to work with then once it's done I'll gladly appreciate some PR's to move the process along.

[sourcec0de]

I am interested in seeing this come to life as well. Just started working on a User model that could use some associations. I would be glad to lend some help as well.

[ElHacker]

Hey, I want to help with this too!

[particlebanana]

So just a quick update on this.

I have a proof of concept up on Waterline with belongs_to and has_many and a corresponding branch on sails-mysql. It's still very much proof of concept and has a lot of sequel code in Waterline I need to pull out. It was more me working out how it will work in the core ORM and ensuring the API will work out.

I sat down this past weekend to try and figure out many_to_many associations and ended up re-writing a large portion of Waterline. It's now a 2-stage model loading process that loads all the models into memory and builds up an internal schema representation that can be synced to any database. This allows us to create join tables where needed and easily map out foreign key constraints. This works good for sequel databases such as MySQL and PostgreSQL but should also allow Mongo to build up the collections and can determine when to use embedded records vs an array of Object Id's.

I'm not sure what the API will look like for embedded records in Mongo yet but we can work on that after the base is done. I'm not sure I like the idea of adding an embedded: true flag that only works on Mongo.

Hopefully I will get some time this week to flush it out and get it pushed up so I can get some feedback on it. I have the model loader and internal schema done but broke most of the core in the process.

Not sure how familiar any of you guys are with the raw Waterline API but the new loader will work something like this. If you are using Sails this will be in the ORM hook and not something you need to concern yourself with.

var Waterline = require('waterline');

module.exports = function(cb) {

  // Create a new instance of Waterline
  var waterline = new Waterline();

  // Load up each model into memory so that it can be introspected and
  // used to build up an internal schema mapping
  sails.models.forEach(function(model) {
    waterline.loadCollection(model);
  });

  // Init the models which will take the internal schema mapping and migrate the database.
  // Because we know about the relationships we can build join tables where needed and
  // map out foreign keys on belongs_to associations.
  waterline.initialize(sails.adapters, function(err, collections) {
    if(err) return cb(err);
    sails.models = collections;
    cb();
  });
}

[Tombert]

Has there been any progress on this? I was hoping that associations would come rather soon.

[xdissent]

https://github.com/balderdashy/waterline/tree/associations =)

[Tombert]

I should rephrase; are there any updates on when this is going to be merged into master on Sails?

[vizo]

:+1:

[colus001]

I'm also longing for the next update with swig template and association support. And sure others too.

[richardhector]

Is there an ETA for this one already? Thanks a lot

[artworkad]

+1 need this :+1:

[4ware]

bump!

[particlebanana]

Alright guys an update on this. We have the Waterline Associations Branch coming along. It has hasMany, belongsTo and manyToMany functionality. As far as adapters go sails-postgresql, sails-mysql, sails-disk and sails-memory have been updated to support what is currently in the waterline repo. I've started on Mongo but it's not done yet. Waterline-Adapter-Tests has been updated to allow adapters to specify what interfaces they support and run appropriate integration tests on those interfaces. It also has a small test suite for the current associations support but needs a lot more tests.

After mongo support we still need to add hasMany through support. This shouldn't be too complicated when we are already creating join tables through many to many. The goal was to have a beta for you guys to help test and debug by end of September or early October. I think we are on track and should have it ready for beta in the next 2 weeks.

I'm not sure how long that beta will last, it depends on how many issues come up and how confident we are that everything is working as planned. We will need your help testing associations and submitting issues and PR's as well as getting more test coverage for various cases into the Waterline-Adapters-Tests suite.

It won't be perfect in the first pass and have all the bells and whistles that everyone wants out of the gate but should be a foundation that we can build upon and improve on.

Once the beta gets closer I will write up a bunch of docs on how to use the associations and how to get the beta setup. For now if you want to see a basic project that uses associations, I have a little test thing I put together at WL-Associations-Playground.

[Tombert]

I have a slight question/concern.

Looking at the way associations work right now, particularly with the many-to-many and one-to-many relationships. Does "collection" refer to the actual database collections, or does it refer to the model name? If it's the latter, I have to say that it's slightly confusing syntax. How I might have done it is like this

reservations: {
  model: 'reservation',
  relationship: 'hasMany'
}

That said, great work thus far! I'm super excited about this update and will definitely be happy to help any way I can!

[particlebanana]

I'll write up docs once I'm sure we won't be making large changes to the api but the main goal was to remove the confusion around hasMany, belongsTo, etc. You always point to a model name as the api for working with data doesn't care what the actual table/collection name is called in the database.

You use collection to reference many items and model to reference a single item. If two models both point to each other using collection a Many to Many association is assumed and a join table will be created. We will expose the join syntax once it's fully baked to allow more complex joins.

[artworkad]

@particlebanana any chance to see the beta this week?

[ab192130]

I'm waiting for assoiciations too..

[phorst]

For non-relational data stores will associations across collections be supported? So in the mongo example above could I have a user collection and a group collection and group from a user:

// User Collection { id: 7, name: 'Mike', groupId: 381 }

//Group collection { id: 381, name: 'Developers' }

Thanks.

[timestep]

aww yiss, associations!

[sourcec0de]

yisssssss!

[ragulka]

@particlebanana I'm trying to play around with the Waterline-Associations-Playground, but I have no success. Even after cloning sails, sails-disk and waterline from git repos and checking out the association branches, I am getting a 500 server error saying: "message": "Unknown rule: collection".

I wanted to try out how validations work with associations. Ie, when I do something like this:

POST /places

{
  "title": "Hotel",
  "address": "Nice road 1",
  "reservations": [
    { "user": 1, "startDate": "2013-01-01 10:00:00"}
  ]
}

[particlebanana]

Hmm it looks like it's trying to parse the collection attribute as a validation which means it's not using the associations branch.

How did you get it setup? With the playground you can just clone it and npm install then run node app.js. If you clone the pieces individually make sure to wipe npm_modules and run npm install again to pull in all the new dependencies after changing branches.

[ragulka]

I actually tried to just clone and do npm install, but I stumbled upon an error while it was installing Sails.

└── socket.io@0.9.14 (base64id@0.1.0, policyfile@0.0.4, redis@0.7.3, socket.io-client@0.9.11)
npm ERR! Error: read ETIMEDOUT
npm ERR!     at errnoException (net.js:878:11)
npm ERR!     at TCP.onread (net.js:539:19)
npm ERR! If you need help, you may report this log at:
npm ERR!     <http://github.com/isaacs/npm/issues>
npm ERR! or email it to:
npm ERR!     <npm-@googlegroups.com>

npm ERR! System Darwin 12.5.0
npm ERR! command "node" "/usr/local/bin/npm" "install"
npm ERR! cwd /Library/WebServer/Documents/node_projects/wl-associations-playground
npm ERR! node -v v0.10.1
npm ERR! npm -v 1.2.15
npm ERR! syscall read
npm ERR! code ETIMEDOUT
npm ERR! errno ETIMEDOUT
npm ERR! Error: read ETIMEDOUT
npm ERR!     at errnoException (net.js:878:11)
npm ERR!     at TCP.onread (net.js:539:19)
npm ERR! If you need help, you may report this log at:
npm ERR!     <http://github.com/isaacs/npm/issues>
npm ERR! or email it to:
npm ERR!     <npm-@googlegroups.com>

So after that I wiped sails and tried installing it manually from the git repo, but for some reason, it installed waterline without the associations branch. So I wiped waterline and cloned it manually and checked out associations.

[ragulka]

After explicitly setting git repos like this npm install sails@git://github.com/particlebanana/sails.git#associations sails-disk@git://github.com/balderdashy/sails-disk.git#associations it managed to install packages with no errors, but I still have the exact same issue when trying to save a model with associations.

error: Error: Unknown rule: collection
    at Object.match [as rule] (/Library/WebServer/Documents/node_projects/wl-associations-playground/node_modules/sails/node_modules/waterline/node_modules/anchor/lib/match.js:49:9)
    at Anchor.to (/Library/WebServer/Documents/node_projects/wl-associations-playground/node_modules/sails/node_modules/waterline/node_modules/anchor/index.js:76:40)
    at validate (/Library/WebServer/Documents/node_projects/wl-associations-playground/node_modules/sails/node_modules/waterline/lib/waterline/core/validations.js:123:29)
    at /Library/WebServer/Documents/node_projects/wl-associations-playground/node_modules/sails/node_modules/async/lib/async.js:108:13
    at Array.forEach (native)
    at _each (/Library/WebServer/Documents/node_projects/wl-associations-playground/node_modules/sails/node_modules/async/lib/async.js:32:24)
    at Object.async.each (/Library/WebServer/Documents/node_projects/wl-associations-playground/node_modules/sails/node_modules/async/lib/async.js:107:9)
    at Validator.validate (/Library/WebServer/Documents/node_projects/wl-associations-playground/node_modules/sails/node_modules/waterline/lib/waterline/core/validations.js:140:9)
    at async.series.runner (/Library/WebServer/Documents/node_projects/wl-associations-playground/node_modules/sails/node_modules/waterline/lib/waterline/query/dql.js:214:25)

yada yada...

Btw, I can see that it (waterline) is on the associations branch, because I can see files like wl-associations-playground/node_modules/sails/node_modules/waterline/lib/waterline/model/lib/associationMethods/add.js etc.

But perhaps the issue is not with waterline, but with Sails instead?

[aarnaud]

I have same probleme "message": "Unknown rule: collection" when i try to request post. attributes contains :

accounts: {
     collection: 'Account',
}

I use a associations branch of sails. Extract of my package.json

"dependencies": {
    "sails": "git://github.com/balderdashy/sails.git#associations",
    "grunt": "0.4.1",
    "sails-disk": "git://github.com/balderdashy/sails-disk.git#associations",
    "sails-mongo" : "git://github.com/balderdashy/sails-mongo.git#associations",
    "ejs": "0.8.4",
    "optimist": "0.3.4",
},

[Samstiles]

Any update ona timeline for merge with master?

[mikermcneil]

@aarnaud I believe that issue was fixed yesterday. Am I right, @particlebanana? You might try rm -rf node_modules && npm install to get the latest. npm doesn't notice when git:// dependencies are updated (and fair enough, it probably shouldn't) so you have to manually delete the dependencies and reinstall them

@Latros Appreciate your patience :)

We'll likely release Waterline v0.10.0 to npm first, then follow-up with Sails-- not sure on a timeline yet. Currently the #associations branch of sails-postgresql and sails-disk are pretty stable if you want to take them for a spin.

If you have any cycles to help, @particlebanana is currently finishing the ability to have multiple associations to the same model (e.g. a Message might have three named "hasMany" associations to users-- to, cc, bcc) Then I don't see why we couldn't release the new Waterline, with the caveat that associations support is being added on an adapter-by-adapter basis.

[aarnaud]

I forget to mention that I use mongo-sails. I will test the change next weekend.

Thanks @mikermcneil

[npfitz]

@mikermcneil If I wanted to play with the associations branch of sails-postresql, what all would I need to do? Would I need to use the associations branch of sails? Or could I use the current stable version of sails, and have npm grab the associations branch of waterline?

Also, I see some documentation on how to use associations further up in this thread. Is there any other documentation anywhere?

Thanks!

[calderUXD]

What is the ETA on this with mongo-sails?

[lwansbrough]

Been playing around with the associations branch a bit, works good so far! A few things I've noticed: Waterline or Sails is still trying to parse "collection" as a type of validator when you set the property of the collection to required:

{
    collection: 'user',
    required: true
}

There's also no documentation on how to create a relationship. The only info that's available shows how to retrieve relationships, but I haven't seen any examples of actually adding models from one side or the other to a many-to-many relationship. @mikermcneil @particlebanana Any tips? Cheers.

[ragulka]

I am also trying to give this another try, but have stumbled on the same "message": "Unknown rule: collection" error.

[gr0uch]

Hi, I'm the author of Fortune.js and I've been keeping tabs on Waterline. When I wrote Fortune.js a few months ago, I basically wrote something very similar, a unified API for different databases. The major show-stopper for using Waterline in my project was the lack of associations, so I am very interested in how this feature will be implemented.

[lwansbrough]

So it looks like this is still not set in stone, but the response I got was something like the following (please remember that everything said here is subject to change): Associations cannot be made prior to the model being created. You must create the model first (meaning .save() it), at which point you can then add an association to it. If you would like to immediately create an association when creating a model, you can do so using the afterCreate method. Here's an example of that (this example is from a User model):

attributes: {
    roles: {
        collection: 'role'
    }
},

afterCreate: function(values, cb) {
    User.findOne(values.id).exec(function(err, user) {
        if(err) return cb(err);
        user.roles.add(role);
        user.save(cb);
    });
}