Sails.js one to many association with postgreSQL: column does not exist

Question

I need some help with associations in sails 0.12.13 with postgresql. I have an "App" model and a "Membership" model. Relation should be one to many (one app can be associated with many relationships). This is the App model db table schema (table is called "apps"):

Table "public.apps"
   Column   |            Type             |                     Modifiers                     
------------+-----------------------------+---------------------------------------------------
 id         | integer                     | not null default nextval('apps_id_seq'::regclass)
 name       | character varying           | not null
Indexes:
    "apps_pkey" PRIMARY KEY, btree (id)
    "apps_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "memberships" CONSTRAINT "app_fk" FOREIGN KEY (app_id) REFERENCES apps(id) ON UPDATE RESTRICT ON DELETE CASCADE

And this is memberships:

Table "public.memberships"
   Column   |            Type             |                        Modifiers                         
------------+-----------------------------+----------------------------------------------------------
 id         | integer                     | not null default nextval('memberships_id_seq'::regclass)
 app_id     | integer                     | not null
Foreign-key constraints:
    "app_fk" FOREIGN KEY (app_id) REFERENCES apps(id) ON UPDATE RESTRICT ON DELETE CASCADE

in my user model, i have this:

module.exports = {
  tableName: 'apps',
  autoCreatedAt: false,
  autoUpdatedAt: false,

  attributes: {
    name: { type: 'string', unique: true, required: true, alphanumericdashed: true },
    memberships: { collection: 'memberships', model: 'Membership' },
 }
}

And this is the Membership model:

module.exports = {
  tableName: 'memberships',
  autoCreatedAt: false,
  autoUpdatedAt: false,
  attributes: {
    app: { model: 'app', columnName: 'app_id' },
  },
};

When I try to query an app and get its memberships:

App.find({ id: 1 }).populate('memberships').exec((err, app) => {
      if (err) throw err;

      console.log(app.memberships);
    });

I get this error:

Error (E_UNKNOWN) :: Encountered an unexpected error
error: column apps.memberships does not exist
    at Connection.parseE (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/connection.js:539:11)
    at Connection.parseMessage (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/connection.js:366:17)
    at Socket.<anonymous> (/usr/src/app/node_modules/sails-postgresql/node_modules/pg/lib/connection.js:105:22)
    at emitOne (events.js:115:13)
    at Socket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:252:12)
    at readableAddChunk (_stream_readable.js:239:11)
     at Socket.Readable.push (_stream_readable.js:197:10)
     at TCP.onread (net.js:589:20)

Looks like the association is not "enabled" and waterline is searching for an actual column "membership" in my model. Can anybody explain me what I am doing wrong? thx

score 2 · Accepted Answer · answered Aug 06 '17 at 12:16

2

According to the documentation, I would guess that you have a bad association.

// App.js
module.exports = {
  tableName: 'apps',
  autoCreatedAt: false,
  autoUpdatedAt: false,

  attributes: {
    name: {
      type: 'string',
      unique: true,
      required: true,
      alphanumericdashed: true
    },
    memberships: {
      collection: 'membership', // <-- changed to singular (as your model should be)
      via: 'app'                // <-- use "via" instead of "model"
    },
  }
}

// Membership.js
module.exports = {
  tableName: 'memberships',
  autoCreatedAt: false,
  autoUpdatedAt: false,
  attributes: {
    app: {
      model: 'app'  
      // <-- removed the "columnName" here
    },
  },
};

Also, convention generally says name your model as a singular instance. For example, its "User.js" and not "Users.js". It's valid to refer to the collection as a plural. I made some changes in your naming, but you'll have to see how that affects your files (since you didn't give those names).

answered Aug 06 '17 at 12:16

vol7ron

40,809
21
119
172

That was it. Thanks. By the way, my model files are named "App.js" and "Membership.js", singular. – whites11 Aug 06 '17 at 12:33
Actually, @vol7ron, the error is gone away, but still I am not getting the data I want. In fact, when I try to get one app's memberships the query that gets executed is this: SELECT * FROM "public"."memberships" AS "memberships" WHERE "app" = 1 ORDER BY "memberships"."id" ASC as you can see, the relation is referencing the wrong column name "app" (the correct column name is "app_id". Do you know how to change that? – whites11 Aug 06 '17 at 14:19
That query looks right if you're trying to find memberships for `app` 1 – vol7ron Aug 06 '17 at 14:22
Got it, I needed a "columnName" in the "app" attribute in Membership.js – whites11 Aug 06 '17 at 14:27
@whites11 go ahead and make an edit to this answer. I'm sure others (possibly me) may refer to it in the future. Am I correct to assume, you added the `columnName` back as you originally had it? I suppose what I thought was bad syntax was actually valid -- that would be great! – vol7ron Aug 06 '17 at 16:56
Yes I did indeed. The working situation for me is: in App.js i have `memberships: { collection: 'membership', via: 'app' }` in Membership.js `app: { model: 'app', via: 'memberships', columnName: 'app_id' }` – whites11 Aug 07 '17 at 15:51
@whites11 is the `via: 'memberships'` needed in the Membership.js? I would think you only needed the model and the columnName – vol7ron Aug 11 '17 at 22:14
Honestly, my relationship is become an habtm and I am unable to test what you are asking. – whites11 Aug 17 '17 at 18:13

Sails.js one to many association with postgreSQL: column does not exist

1 Answers1