22

Can anyone guide me on how to setup relational schema & performs joins in sails.js?

Travis Webb
  • 14,688
  • 7
  • 55
  • 109
Brieuce Wayne
  • 282
  • 1
  • 2
  • 8
  • possible duplicate of [Sails JS Waterline join of multiple models](http://stackoverflow.com/questions/23932095/sails-js-waterline-join-of-multiple-models) – givanse May 31 '15 at 04:37

2 Answers2

42

Associations are officially Supported in Waterline

Overview

From the docs:

With Sails and Waterline, you can associate models across multiple data stores. This means that even if your users live in PostgreSQL and their photos live in MongoDB, you can interact with the data as if they lived together in the same database. You can also have associations that span different connections (i.e. datastores/databases) using the same adapter. This comes in handy if, for example, your app needs to access/update legacy recipe data stored in a MySQL database in your company's data center, but also store/retrieve ingredient data from a brand new MySQL database in the cloud.

Supported Association Types

Planned Association Types

Original Post

I'm the author of Waterline, the ORM used in Sails. Waterline is brand new and we are adding features all the time. Currently we don't have support for associations but it's next on the roadmap. We worked out an API for associations that I think most people will really like. You can view the work in progress and the proposed API at: [Proposed Sails Associations API][1].

We are going to tackle Associations and Transactions next and hope to have them ready in the next month or so.

In the mean time if you are using the MySQL or PostgreSQL adapters they both expose a raw .query() method that allows you to pass in a hand built sql query and have it executed. I totally realize this isn't ideal but should allow you to continue building your app while we get support for associations and joins.

The function signature for the query method is:

Model.query(<sql query>, <optional data>, callback);
Travis Webb
  • 14,688
  • 7
  • 55
  • 109
particlebanana
  • 2,416
  • 21
  • 22
  • 9
    where can one find the documentation for the `Model.query` method? – Johan Dettmar Nov 20 '13 at 14:01
  • It would be nice if custom instance methods were still available when using the model.query method. I assume because the Model.query method accepts arbitrary sql it makes keeping synch with the model more difficult? – Kory Nov 28 '13 at 04:29
  • 1
    Because it accepts anything we don't know which collection data we are getting back. You should be able to instantiate a new model instance by doing something like: `var instance = User._model(values)` – particlebanana Jan 15 '14 at 00:49
  • 1
    Is it possible to join multiple tables with sails version 0.10.0-rc7 ? – Sandro Adamia May 29 '14 at 09:34
  • 2
    How can you add parameters in the query, I suppose it is possible with it is possible? – dpineda May 29 '14 at 23:45
  • @particlebanana Please check out [my question](http://stackoverflow.com/questions/26596718/sailsjs-through-association) Here as it's directly related here. The docs haven't been updated to show how to use through associations. [We need your help](https://www.youtube.com/embed/aYhz5tArAnk?start=9&end=11&safe=active&autoplay=true) – OneHoopyFrood Oct 27 '14 at 21:49
  • @avian I updated the answer with the latest Waterline features. Associations and Joins are now officially supported out of the box. – Travis Webb Apr 20 '15 at 00:23
  • @particlebanana Please help me - how I can user query builder? I need to create query `SELECT p.id, p.name, count(c.id) FROM parent p JOIN child c ON c.parent_id=p.id GROUP BY p.id` – Crusader Sep 21 '16 at 12:41
6

The example from particle banana works but should actually use "new" like "var instance = new User._model(values)". I'm using the following code and it works.

Accounts.query(query, function(err, accounts) {
  if (err)
    return fn(err);

  accounts = _.map(accounts, function(account) {
    return new Accounts._model(account);
  });

  fn(null, accounts);
});
Bruce Kim
  • 61
  • 1
  • 3
  • Isn't this a blocking statement? You have to wait for that _.map to complete before it continues on. On Node, it would be better to use something like async.js: https://github.com/caolan/async to eliminate blocking code altogether. – Glen Selle Mar 12 '14 at 13:43
  • 6
    There is no I/O performed in `new Accounts._model()`, so it's not blocking and nothing would be gained from making it asynchronous. – Dave Mar 21 '14 at 19:54