Creating Knex Migration Referencing the Same Table Twice

Question

Trying to create a table Transaction in a postgresql database that references Buyers and Sellers s.t. both are objects from the Users table.

I think I have the migration working to look something like the following:

exports.up = function(knex, Promise) {
  return knex.schema.createTable('likes', t => {
    t.increments('id').primary()
    t.integer('buyers_id').references('users.id').onDelete('CASCADE')
    t.integer('sellers_id').references('users.id').onDelete('CASCADE')

    ...

    t.datetime("created_at");
    t.datetime("updated_at");
  })
};

Next, I need to manage the association in the model, s.t the Transaction belongs to a Buyer and a Seller, which are both members of the User class.

To clarify the question, I am able to create the model with these attributes, but the association does not seem to be working.

here is my Transaction model:

const BaseModel = require("./BaseModel");

// const Password = require('objection-password')();

class Transaction extends BaseModel {
  static get tableName() {
    return "transactions";
  }

  static get relationMappings () {
    const User = require('./User');
    const Item = require('./Item')
    return {
      buyer: {
        relation: BaseModel.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: 'transactions.buyers_id',
          to: 'users.id'
        }
      },
      seller: {
        relation: BaseModel.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: 'transactions.sellers_id',
          to: 'users.id'
        }
      }, 
      books: {
        relation: BaseModel.BelongsToOneRelation,
        modelClass: Item,
        join: {
          from: 'transactions.items_id',
          to: 'items.id'
        }
      }
    }
  }
}

module.exports = Transaction;

Here is the relevant route where I try to eager load the buyer:

let router = express.Router();

router.get('/', async (req, res) => {
  const transactions = await Transaction
    .query()
    .eager(['buyer', 'items')
  res.json(transactions);
});

I have figured this out. The above code works, using the aliases buyers and sellers and associating those two types of Users with Transactions.

Clarifying question: do you mean, "how do I write queries that use this table"? — Rich Churcher, Nov 12 '19 at 02:22
Ah, thanks for adding the `objection.js` tag and additional code. Looks like you solved your own problem, nice one! You might like to add your code as the accepted answer in case others are also struggling with something similar :) — Rich Churcher, Nov 13 '19 at 17:54

score 0 · Accepted Answer · answered Nov 14 '19 at 19:01

For anyone who is interested... Consolidating the working solution above using Node/ExpressJS for the server, Postgresql for the db, KnexJS/ObjectiveJS to manage Models and queries.

Here is the migration that sets up columns for buyers and sellers both of which reference the same Users table:

exports.up = function(knex, Promise) {
  return knex.schema.createTable('likes', t => {
    t.increments('id').primary()
    t.integer('buyers_id').references('users.id').onDelete('CASCADE')
    t.integer('sellers_id').references('users.id').onDelete('CASCADE')

    ...

    t.datetime("created_at");
    t.datetime("updated_at");
  })
};

HEre is the Transactions Model including associations s.t. a Transaction belongs to Item Buyer(user) and Seller(user) and Item:

const BaseModel = require("./BaseModel");

// const Password = require('objection-password')();

class Transaction extends BaseModel {
  static get tableName() {
    return "transactions";
  }

  static get relationMappings () {
    const User = require('./User');
    const Item = require('./Item')
    return {
      buyer: {
        relation: BaseModel.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: 'transactions.buyers_id',
          to: 'users.id'
        }
      },
      seller: {
        relation: BaseModel.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: 'transactions.sellers_id',
          to: 'users.id'
        }
      }, 
      books: {
        relation: BaseModel.BelongsToOneRelation,
        modelClass: Item,
        join: {
          from: 'transactions.items_id',
          to: 'items.id'
        }
      }
    }
  }
}

module.exports = Transaction;

Lastly, here is the express route which returns all transactions including eager loading the associated models:

let router = express.Router();

router.get('/', async (req, res) => {
  const transactions = await Transaction
    .query()
    .eager(['buyer', 'items')
  res.json(transactions);
});

Creating Knex Migration Referencing the Same Table Twice

1 Answers1