CODEWITHSUNDEEP
javascriptnode.jspostgresqlsequelize.js
Yuri Sulyma
Yuri Sulyma

Reputation: 413

Rich self-association with foreign key constraints in Sequelize with PostgreSQL

I'm using SequelizeJS with PostgreSQL..

I have a table units. I want a table unit_relations with columns unit1_id, unit2_id, type. Both unit1_id and unit2_id should have foreign key constraints to units.id such that any time a unit is deleted, any rows of unit_relations referencing that unit (as either unit1 or unit2) are deleted as well.

In Sequelize, I want unit2 to be mapped to Relation and unit1 to be mapped to ReverseRelation; e.g. the row

          units                         unit_relations
------------+---------------   ----------+----------+-------
relation_id | reverce_rel_id    unit1_id | unit2_id |  type
------------+---------------   ----------+----------+--------
            |                         11 |       43 | parent

would be fetched by unit1.getRelations() and unit2.getReverseRelations().

I am having difficulty getting Sequelize to respect my column names (it wants to use relation_id and reverse_relation_id) or create the foreign keys.

Upvotes: 2

Views: 1042

Answers (1)

khex
khex

Reputation: 2828

I still do not fully understand all the relationships between tables. Try to describe it using simple examples rather than abstract concepts. Like 

//Mary Had A Little Lamb
Marry.hasOne(Lamp)

But from what I understand, you can do so:

var Sequelize = require('sequelize');
var sequelize = new Sequelize(.... {
    // your pg db settinds 
});

var Unit = sequelize.define('Unit', {});

var UnitRel = sequelize.define('UnitRel', {
    type: Sequelize.STRING
});

// Create in Unit table columns 'relation_id' and 'rev_rel_id'
UnitRel.hasOne(Unit, {foreignKey: 'relation_id', as: 'Relation'});
UnitRel.hasOne(Unit, {foreignKey: 'rev_rel_id', as: 'RevRelation'});

// Create in UnitRel table columns 'unit1_id' and 'unit2_id'
UnitRel.belongsTo(Unit, {foreignKey: 'unit1_id', as: 'Unid_1'});
UnitRel.belongsTo(Unit, {foreignKey: 'unit2_id', as: 'Unid_2'});



Unit.sync({force: true}).success(function() {
    UnitRel.sync({force: true}).success(function() {
        console.log('TABLES CREATED');
    });
});

and then find some UnitRel and stretch dependent fields

UnitRel.find({
  where: {id: 1}, 
    include:[
      {model: Unit, as: 'Uid_1'},
      {model: Unit, as: 'Uid_2'}
    ]
}).success(function(match) {
   console.log(JSON.stringify);
});

Upvotes: 2

Related Questions