CODEWITHSUNDEEP
node.jsobjection.js
conny
conny

Reputation: 10145

Self-referencing many-to-many relation with Objection.js

I have Currency and ExchangeRate database tables like below:

CREATE TABLE Currency (id INT, code VARCHAR(3), name TEXT);    
CREATE TABLE ExchangeRate (baseCurrencyId INT, counterCurrencyId INT, rate FLOAT);

INSERT INTO Currency (id, code, name) VALUES 
  (1, 'USD', 'US Dollars'),
  (2, 'AUD', 'Australian Dollars');
INSERT INTO ExchangeRate (baseCurrencyId, counterCurrencyId, rate) VALUES
  (1, 2, 1.342),
  (2, 1, 0.745);

Given a baseCurrency Currency.id and a counterCurrency Currency.code, I want to find the corresponding exchange rate and counterCurrency name.

What would be the most efficient way of modeling this relationship? (I'm using Objection.js v0.4.0)

Upvotes: 3

Views: 2231

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

I added quotes to table and column names to make them case sensitive:

CREATE TABLE "Currency" (
  id INT, code VARCHAR(3), name TEXT
);    
CREATE TABLE "ExchangeRate" (
  "baseCurrencyId" INT, "counterCurrencyId" INT, rate FLOAT
);

INSERT INTO "Currency" (id, code, name) VALUES 
  (1, 'USD', 'US Dollars'),
  (2, 'AUD', 'Australian Dollars');
INSERT INTO "ExchangeRate" ("baseCurrencyId", "counterCurrencyId", rate) VALUES
  (1, 2, 1.342),
  (2, 1, 0.745);

You can model that as many to many relation where are extra parameters stored in join table:

const knex = require('knex')({ 
  client: 'pg', 
  connection: 'postgres:///objection_test'
});
const { Model } = require('objection');
const Promise = require('bluebird');

class Currency extends Model {

  static get tableName() { return 'Currency'; }

  static get relationMappings() {
    return {
      currencyWithRate: {
        relation: Model.ManyToManyRelation,
        modelClass: Currency,
        join: {
          from: 'Currency.id',
          through: {
            from: 'ExchangeRate.baseCurrencyId',
            to: 'ExchangeRate.counterCurrencyId',
            extra: ['rate']
          },
          to: 'Currency.id'
        }
      }
    };
  }
}

const boundModel = Currency.bindKnex(knex);

boundModel.query().then(currencies => {
  // get related currencies for each 
  return Promise.all(currencies.map(cur => {
    return Promise.join(cur, cur.$relatedQuery('currencyWithRate'),
      (cur, exchangeRateCurrency) => {
        return {
          currency: cur,
          exchangeRateCurrency: exchangeRateCurrency
        };
      });
  }));
}).then(result => {
  console.dir(result, { depth: null});
}).finally(() => {
  return knex.destroy();
});

Lets assume that code above is test.js:

Mikaels-MacBook-Pro-2: mikaelle$ node test.js 
[ { currency: 
     AnonymousModelSubclass {
       id: 1,
       code: 'USD',
       name: 'US Dollars',
       currencyWithRate: [ AnonymousModelSubclass { id: 2, code: 'AUD', name: 'Australian Dollars', rate: 1.342 } ] },
    exchangeRateCurrency: [ AnonymousModelSubclass { id: 2, code: 'AUD', name: 'Australian Dollars', rate: 1.342 } ] },
  { currency: 
     AnonymousModelSubclass {
       id: 2,
       code: 'AUD',
       name: 'Australian Dollars',
       currencyWithRate: [ AnonymousModelSubclass { id: 1, code: 'USD', name: 'US Dollars', rate: 0.745 } ] },
    exchangeRateCurrency: [ AnonymousModelSubclass { id: 1, code: 'USD', name: 'US Dollars', rate: 0.745 } ] } ]
Mikaels-MacBook-Pro-2: mikaelle$

Looks like $relatedQuery() also patches queried objects to the Model whose relations were requested.

Upvotes: 6

Related Questions